CODEWITHSUNDEEP
ssis
Trey Carroll
Trey Carroll

Reputation: 2382

SSIS Update rows by using a flat-file containing the ID and update values

I'm new to SSIS and trying to create a dataflow task that will accomplish this type of thing:

UPDATE dbo.table1
SET lastname = t2.lastname
FROM table1 t1
JOIN table2 t2
ON t1.Id = t2.Id

Except I want to do it with the values for table2 being in a tab-delimited file like this:

ID lastname
1 Carroll
2 Patel
3 Smith

And I don't want to have to ETL table 2 into the database.

I have tried using a flat-file to pull in the values and then adding an OLE DB Data Destination, however this causes SSIS to INSERT the values rather than joining on the ID and UPDATING the field listed.

What is the correct way to approach an update of this kind with SSIS?

TIA,

Trey Carroll

Upvotes: 0

Views: 5723

Answers (1)

Santiago Cepas
Santiago Cepas

Reputation: 4094

This is how I'd do it:

  1. Set a dataflow task, with the flat file as source.
  2. Add a lookup transformation, and set it up so it looks up table 1 by id and returns lastname.
  3. Add an Execute OLE DB command transformation to your "on success" dataflow, and execute the appropriate SQL code to update Table 1.

The con of this approach is that it executes the SQL command for every row that matches, and it can be inefficient it that number is high. It would be much more efficient if you could load the flat file to a temporal table, and then perform the update.

Upvotes: 2

Related Questions