CODEWITHSUNDEEP
sqlsql-serverssisetlpowerquery
sugarakis
sugarakis

Reputation: 129

How to update a SQL table from an SSIS Power Query Source resultset

in an SSIS package I have a "Power Query" Source step that results to 3 columns, Region, Facility and Weekly_Amount. I am interested in updating a SQL table with the Power Query's Weekly_Amount column, where Region and Facility relate to SQL table record. Example:

Update {Some table}
set {SQL Table}.Weekly_Amount = {PowerQuery}.Weekly_Amount 
where {SQL Table}.Region = {PowerQuery}.Region
and {SQL Table}.Facility = {PowerQuery}.Facility

What SSIS step do I need to use in order to accomplish the above? I can always include a T-Sql Update statement.

Thank you.

Upvotes: 3

Views: 787

Answers (1)

Hadi
Hadi

Reputation: 37313

Oledb Command

You can use an OLEDB Command transformation within the Data Flow Task to do that:

Update {SQL Table}
set {SQL Table}.Weekly_Amount = ?
where {SQL Table}.Region = ?
and {SQL Table}.Facility = ?

And map the columns to the parameters in the OLEDB Command configuration form.

References and helpful articles

Staging Table

Using OLEDB command is not recommended from performance perspective since it will execute an update statement for each row in the data pipeline.

You can import data into a staging table and use an Execute SQL Task to update data using a similar query:

Update {SQL Table}
set {SQL Table}.Weekly_Amount = {Staging Table}.Weekly_Amount
FROM {SQL Table} INNER JOIN {Staging Table} 
    ON {SQL Table}.Region = {Staging Table}.Region 
    AND {SQL Table}.Facility = {Staging Table}.Facility

Upvotes: 1

Related Questions