CODEWITHSUNDEEP
sql-serverdata-warehouseetlssis
Cammo
Cammo

Reputation: 33

Use SSIS Lookup Transformation to update ADO .NET Destination table

I have an SSIS package that I want to use to update a column in a datawarehouse staging table based on the values of a surrogate key mapping table that contains the surrogate key paired with the natural key. Specifically I want to use the cache Lookup to update the fact staging table to contain the surrogate key for the inventory dimention in the same way that the following SQL would. 

 UPDATE A
      SET A.DWHSurrogateKey = B.DWHSurrogateKey
  FROM SaleStagingTable A INNER JOIN inventoryStagingTable on B.OLTPInventoryKey = A.OLTPInventoryKey

Unfortunately the nature of the data flow from Lookup transformation to destination means that it creates a whole new row, rather than updating the existing matched row. Is it possible to manipulate SSIS to do this?

Couple of constraints:

I'd like to be able to do what these guys have suggested but with ADO connectors rather than OLE DB:

http://redsouljaz.wordpress.com/2009/11/30/ssis-update-data-from-different-table-if-data-is-null/

http://www.rad.pasfu.com/index.php?/archives/46-SSIS-Upsert-With-Lookup-Transform.html

Upvotes: 3

Views: 6976

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

For such a simple update I would use an Execute SQL Task and save the hassle of having to mess around with a data flows. If you have lots of similar updates but with different fields and tables, I would store the column and table names in a Foreach Loop Container using a Foreach Item Enumerator, I would then add a Script Task that would take the item names and generate some dynamic SQL which could be stored in a variable, Next add the Execute SQL Task and get it to use the SQL variable.

Upvotes: 3

Related Questions