Slow Running Insert After Changing OLE DB Destination Data Access Mode

Hello,

In a Data Flow Task, I have an insert that occurs into a SQL Server 2000 table from a fixed width flat file. The SQL Server table that the data goes into is accessed through an OLE DB connection manager that uses the Native OLE DB\Microsoft OLE DB Provider for SQL Server.

In the OLE DB Destination, I changed the access mode from Table or View - fast load to Table or View because I needed to implement OLE DB Destination Error Output. The Error output goes to a SQL Server 2000 table that uses the same connection manager.

The OLE DB Destination Editor Error Output 'Error' option is configured to 'Redirect' the row. 'Set this value to selected cells' is set to 'Fail component'.

Was changing the access mode the simple reason why the insert from the flat file takes so much longer, or could there be other problems

Thank you for your help!

cdun2

Phil,

I am not quite sure you can have both at the same time; fast load option processes batches of rows, then when a row causes an error the OLE DB Destination fails; so there is not chance of sending rows to the error output *I think*.

To the original poster:

By using 'table or view' option the destination component will commit after each row is inserted; then you are able to use the Error output but as you saw it is very slow. You may want to catch any possible faulty row before it reaches the destination and send it to a different data pipeline; that way you can take advantage of the Fast load option

Rafael Salas wrote:

Phil, I am not quite sure you can have both at the same time; fast load option processes batches of rows, then when a row causes an error the OLE DB Destination fails; so there is not chance of sending rows to the error output *I think*.

Thanks, I will try this approach.

cdun2

I'll work with the Lookup option first, so please bear with me as I try to understand how this should work.

I have the Lookup Transformation set up between my flat file source, and the Lookup Error Output and Lookup Output. The Lookup Transformation Reference Table is called ProblemRecords_DailySettlementData. This is where I intend for the matching (duplicate) records to go, so I have this table set as my Lookup Output.

I have my Lookup error output configured so that a table called Staging_DailySettlementData is populated when no match is made in the lookup (no duplicate records). This table has a unique nonclustered index on it with IGNORE_DUP_KEY. I have mapped only the unique columns between the source and the destination (columns 3,5,6,7,60).

I have Fast Load set up for both the output and the error output.

I'm not sure what the Lookup Operation should be for the mapped columns. I have it set up to <add as new column> for all columns.

According to how I understand this, when I run the DataFlow, The lookup should not find a matching record in ProblemRecords_DailySettlementData (the Lookup Output), and send the unmatched records to the Error Output. Otherwise the matching record (duplicate) should go to ProblemRecords_DailySettlementData.

When I run the dataflow, everything goes to the Error Output table, even the matching records. The Insert into the Error Output table fails when a duplicate is encountered.

What have I not set up correctly

Thanks again!