CODEWITHSUNDEEP
sqlsql-serverazuressis
Calvin Ellington
Calvin Ellington

Reputation: 723

Updating Data Warehouse via ADO.NET Connection

I have built an SSIS package that looks at a source table and pushes this data to a data warehouse in Azure. So far it adds entries that are not in the warehouse but are in the source table. I'm confused as to how to do the update section of this package, where the data warehouse contains the same entry as the source table but needs to be updated to reflect changes made to the source table.

With OLE DB Connections there is a 'SQL' parameter in the 'custom options' section of the destinations advanced editor. There is no such ability with the ado.net destination advanced editor. What is the proper way to go about updating these entries?

Here is a screenshot of my package so far: enter image description here

I've searched extensively and cannot find a clear answer to this. Please forgive the error on my part if this is a simple solution.

Upvotes: 0

Views: 617

Answers (1)

Ferdipux
Ferdipux

Reputation: 5246

You have two possible approaches - Update with OLEDB Command and Update wwith intermediate table.

OLEDB Command Transformation runs specified command for each line of data block in data transformation. You simply add OLEDB Command to your data transformation, pick up OLEDB Connection Manager and enter parametric SQL command like this enter image description here Hitting ellipsis opens SQL Command Editor window enter image description here
Having set SQL command with parameters, you can bind fields from data stream to parameters on the next tab enter image description here
And this is it! More info - MS Docs.
Advantage of this method - it is simple. Disadvantage - for every update row a separate SQL command is fired, which is not good for performance.

Then you have an alternative with Update through intermediate table. Main idea - you store all table rows to be updated in a special table. Then - run SQL Command with SQL Update like 

Update [tgt_tbl] Set [tgt_tbl].[field1] = [interm_tbl].[field1] 
    From [tgt_tbl] tgt  
    Inner Join [interm_tbl] itbl On tgt.key = itbl.key

This approach is more complex, but performance is definitely better.

Upvotes: 1

Related Questions