SSIS Fast Load fails to copy correct number of rows
Step 1 - truncate destination table on success Using obdc source table source to a odbc destination use a fast table load to take three colums out of source, and copy to destination. In the source, column 1 is the primary key (int) Other two columns are time stamps Destination table, column 1 is int (no keys) - does not allow nulls, column 2 & 3 allow nulls Noramlly the rowcount in the source and destination tablesmatch after a run. However, on occasions, the destination table count is less than the source table. On the destination odbc source, we enable identify insert and check constraints. I can't see how we'd drop rows since by definition the row needs to existing in the source (we're copying the primary key). The first time this occurred, anecdotal information is that the source sql server was under memory stress. Has anyone seen this behavior before? Any ideas on how to resolve it? Ken
December 3rd, 2008 4:15pm

are you sure that the source component isn't generating error rows? if not, then configure the error output on the source component to redirect error rows to another destination component for further inspection. hth
Free Windows Admin Tool Kit Click here and download it now
December 4th, 2008 1:49am

When the row count for the destination is less than the source table, try identifying the rows that are missing from the destination table.
December 7th, 2008 6:09am

I just ran into this same issue. After a solid half day of troubleshooting I found this little 'fast load' setting to be the culprit. We have a very simple copy operation taking rows out of an ODBC source, adding a column and then stuffing them into a destination ODBC source. All the operations were run on a development machine with a local SQL Server install and plenty of RAM/CPU headroom.I'd enabled logging of all kinds everywhere trying to detect the problem but nothing was tripped. When run under debug mode (in dev studio) I see the correct number of rows (684 in this case) reported being sent to the ODBC destination; however, when I look in the table itself I only ever see the first one.As soon as I turned off the fast load option then I started getting the full data set moving over properly.At this point, I'm of a mind to go through and remove 'fast load' from every one of my packages. I'll take reliability over speed any day of the week.
Free Windows Admin Tool Kit Click here and download it now
December 8th, 2008 6:42pm

dThat sounds weird. Fast load should not drop rows by itself. I would double check the error configuration in source and target components. I am thinking that somehow a batch of rows is being rejected and going undetected; or perhaps something in the source is happening and the right number of rows are not being retrieve.Have you tried placing a row count transformation right before the destination, and the write the value of that variable (rowcount) somewhere to be checked after the execution of the package?
December 8th, 2008 9:30pm

Hello.....is there any specific reason or resolution to this issue? We just faced the issue yesterday and removing the Fast Load option imported the correct number of records in OLEDB destination. Its a simple SSIS DFT with a oledb source and destination, with Fast Load on, roughly 20-25% of the rows are getting rejected at random. No memory issues, no error rows after creating an alternate flow redirecting the error records.
Free Windows Admin Tool Kit Click here and download it now
August 10th, 2012 12:02pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics