| Author |
Topic |
|
nathan-z
Starting Member
10 Posts |
 Posted - 2009-01-19 : 09:40:17
|
| I have this issue with SSIS were 9 times out of 10 the package works just fine but on that one time, it moves blank data to the file instead of actual data. Basically what happens is that if there are 10 lines of data that should be placed in the file 10 lines of blank spaces appear instead. It is completely random when it does happen. Has anyone else had this issue?Thank you,Nathan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-19 : 09:51:08
|
| how are you transfering data? using data flow task or script task? |
 |
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-19 : 16:36:27
|
| I am transferring the data using a data flow task to a csv file. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-19 : 20:05:20
|
| seems to me whatever is retrieving the data for exSELECT a,b,c,d from xTable is returning values but you are asking it for column names c and only that happen to be blank whereas a and b are not...or it could be your source data actually does not have any data so it returns blank?guessing...can you post sample data and stuff. |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 09:23:38
|
| Unfortunately, I cannot post a data set but the query does return data. The odd thing is that after the file is created with just lets say 10 lines of blank data instead of actual data, I can run the same package again and it works just fine. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 09:38:01
|
quote:
Originally posted by nathan-z
I am transferring the data using a data flow task to a csv file.
are you apply any other transformation in between source and destination? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2009-01-20 : 09:50:41
|
| Can you post the query you are using for outputing the data?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 10:00:16
|
| The thing is it happens on several of our reports. They are simple select statements from one table joined to another table with a where statement to cut the overall results down. Normally there is nothing between the source and destination. |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 10:06:33
|
| This is a sample query but the table names and values have been changed.SELECT *, dbo.fn_DateyyyyMMdd(TermDate) as TermDateFormattedFROM Tablexwhere GroupNum not in ('x', 'y', 'z') |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-20 : 10:35:41
|
Nathan-z, is this a riddle or a real actual problem. you are asking people to solve your riddle without telling them the riddle itself."the table names and values have been changed" Are they under witness protection program? |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 11:01:13
|
| Real problem. It has happened a couple of times and has caused a few of our members to terminate in another system because half of the data was not there. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2009-01-20 : 11:14:32
|
| Did you make sure that your query does not output any blank or NULL values?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 11:23:15
|
| There are blank values in the file. Should that be a problem. When the package was a DTS package, this was not an issue. We have since reprogrammed it as an SSIS package. The odd thing is that in one case it put half the data in the file and left the other half as just blank lines. There were about 1000 blank lines in a fixed length file making the total size of the file the same as the day before. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 11:39:05
|
quote:
Originally posted by nathan-z
There are blank values in the file. Should that be a problem. When the package was a DTS package, this was not an issue. We have since reprogrammed it as an SSIS package. The odd thing is that in one case it put half the data in the file and left the other half as just blank lines. There were about 1000 blank lines in a fixed length file making the total size of the file the same as the day before.
is your problem transfering the existing blank lines from file to table or is it creation of blank lines in table for valid data in file? |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 11:47:33
|
| It is the creation of blank lines in a data file instead of actual data. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-20 : 11:56:00
|
| is there any trigger in destination table? |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-20 : 12:17:43
|
| what happens if you ran the contents of the SSIS package manually meaning you take out the SQL statement and run them in SSMS and push the result to the grid what do you get?NULL values are yes problematic, maybe your DTS package used to account for NULL values that is it used to have logic to say if NULL do not dump data ...but still you have to figure out why are there NULL values... |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-01-20 : 12:20:39
|
| what have you done with your DTS packages? can you still run these DTS packages and see what happens ...if this problem does not exist in your DTS packages then there is some logic in your DTS packages that checks for NULL values....also if you do not want NULL values why have you set up your table to accept null values.... |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-20 : 12:49:42
|
| No trigger. If i rerun the package after it creates the file with either missing data and blank lines in its place or a entire blank file, it creates the file without issue. I was mistaken, there are no NULL values just blank values that are coded to be either one value or a blank value. |
|
|
|
nathan-z
Starting Member
10 Posts |
Posted - 2009-01-21 : 15:44:07
|
| I am just going to log every event and put it to file to see if any errors come up. |
|
|
|
Mangal Pardeshi
Posting Yak Master
110 Posts |
Posted - 2010-08-27 : 09:57:59
|
| Me too faced the same issue, and while searching on net I stumbled across this. I know this is old thread, but I thought I should update the group about it. As in my case this may also happening because of the "low-memory condition may occur temporarily or intermittently" on your SSIS box.Please see the following KB: http://support.microsoft.com/?id=972498RegardsMangal PardeshiSQL MVP |
|
|
|
Next Page
|
SSIS Exports Blank Lines