October 12, 2007 at 8:06 am
I have a package that those the extraction for an ETL process. It has been working well but this week I got a complain about some data not being loaded. I checked the missing data and it was not in the file loaded but the source text file does have some 1656 rows more than the table into which it is extracted. The transform that does this is just a plain copy and has never reported an error but this might have happened in the past and it never got reported. I have left the transform set to fail at the first error. The total number of rows loaded is 20264 but the text file has 21920. What could be causing the package to miss that many rows from the source text file?
Ed
October 12, 2007 at 8:18 am
Was the text file delimited or fixed length?
Did you use a format file for the text file to load into the table?
Did you use bulk copy to copy the data from text file to the table?
October 12, 2007 at 8:21 am
The text file is delimited and it doesn't user a format file or bulk insert.
Thanks,
Ed
October 12, 2007 at 8:35 am
Ed
Have you worked out which rows are missing to see whether they have anything in common? Is it the last 1656 rows from the text file? Is there a trigger on your destination table?
John
October 12, 2007 at 8:46 am
It's the last 1656. It seems to get to that point and just give up.
Ed
October 12, 2007 at 8:56 am
Ed
Did you check the destination file for triggers? Is there an error message? Have you checked the errorlog and the Windows Event Log? Try opening the text file and counting down to the last row that was successfully imported: is there anything suspicious after it, such as an unexpected delimiter character? Could it be that the final 1656 rows are duplicates of rows already in the table, and they weren't imported because they'd violate a primary key or unique constraint? Perhaps you can create a test database and point the DTS package at that, and try importing some old text files to see whether they go through OK, or maybe make changes to the text file you're having trouble with and try importing that.
Good luck
John
October 12, 2007 at 9:41 am
John,
I checked the file and the row next to the last one loaded has the delimeters out of whack. I deleted that row and I am reruning the process. 😉 I'm also keeping my fingers crossed.
Thanks,
Ed
October 12, 2007 at 12:30 pm
I was suspected that. I did not know what delimiter you used, but the common one is ',' and one time I tried to load a file and it happened the data had a ',' and it caused a big problem. If you have to use delimiter, try to use some uncommon one, last time I use '|'.
October 12, 2007 at 12:33 pm
After I removed that row from the text file, it worked and went all the way to the end. The delimiter they were using was |.
Thank you all,
ED
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply