Newbie needs help

Feb 12, 2010 at 12:50 PM


do I have to use the "New" output to insert the stream into my destination or does the component do that automatically?

Same question for Updates....





Feb 12, 2010 at 8:46 PM
Edited Feb 12, 2010 at 8:48 PM

It only prepares the data and sends it to the component's output. You must add a combination of data flow destination and transformation components to actually perform the commitment of the data to the target Often you will use OLE DB Destination components for New and Audit outputs and use OLE DB Command Transformation componets for Update, SCD2 New and SCD2 Expired outputs.

Feb 15, 2010 at 8:22 AM

Thanks. Do you have an example how you work with the outputs?

Feb 22, 2010 at 9:16 PM

You would just send the "insert" outputs directly to any one of the Destination components that can connect to your DW dimension table - you can choose to Union All them before you do that, if you wish.

The "update" type outputs (like the SCD1 updated output) need to update existing records.  Typically, the first suggestion is to use the OLE DB Command component to issue UPDATE T-SQL commands.  This will probably work just fine - but isn't very quick because it does "row by row" UPDATE calls to the DB.  What's much faster (and more complex) is to send those "update" rows to a temporary table (doesn't have to be "#") then use an Execute SQL Task in the Control Flow to issue one batch UPDATE command.  Thankfully, there are two custom components here on CodePlex to help with that process - the Batch Destination, and the MERGE Destination.