CODEWITHSUNDEEP
ssis
Vicky
Vicky

Reputation: 57

update a particular column with SSIS Package

I'm trying to update a particular column using SSIS package, however not sure what is wrong, below is the details.

I have 2 tables DETAIL & USER, I am trying to build a package which will find the rows with below condition

DETAIL.ID <> 'EXCLUDE',
DETAIL.USERNAME <>'N/A',
and DETAIL.USERNAME <> USER.USERNAME

Once I have the rows I want to update the USER.USERNAME column in the USER table with the DETAIL.USERNAME field.

I have build a package as follows:

Source - DETAIL table
CONDTIONS -   DETAIL.ID <> 'EXCLUDE' & 
              DETAIL.USERNAME <>'N/A'
LOOKUP - USER table
CONDITION - DETAIL.USERNAME <> USER.USERNAME
DERIVED COLUMN - REPLACE USER.USERNAME with DETAIL.USERNAME 
DESTINATION - USER

This package is failing and giving an error where it looks like it is adding an extra rows instead of updating it.enter code here

Screenshot of the package

Upvotes: 2

Views: 4986

Answers (2)

Kristen Hammack
Kristen Hammack

Reputation: 399

So, I had slightly different requirements, as I was loading from Excel and needing to update one table and insert into another one. But for the question of "how do you update a column in SSIS", I decided to go with a Conditional Split (which creates an in-memory table basically, so use with caution) and then an OLE DB Command, which allowed me to skip the temp table creation and use the output of previous steps in my UPDATE statement.

The Command Text is simply UPDATE Table1 SET Col1=? WHERE ID=?.

SSIS Package Execution Path

Ta-da! Fully-validated UPDATE in SSIS!

Upvotes: 0

Prabhat G
Prabhat G

Reputation: 3029

Partially, this is expected behavior as there is no way to tell OLEDB Destination that you're updating and not inserting. SSIS Data flow task flow is meant for read from source -> transform -> insert in destination.

To achieve what you want, I have a simpler way:

Create a Data flow task.

  • Source - DETAIL table. Use where clause to filter below condition:
    WHERE DETAIL.ID <> 'EXCLUDE' & DETAIL.USERNAME <>'N/A'

  • LOOKUP (no match output) - USER table on user.username column (This will bring all the records of Details which have different username than user.username)

  • DESTINATION - staging table (stg_detail)

img1

Next, use Execute SQL Task to run batch update.

syntax: 

UPDATE x
SET x.username = stg_detail.username
FROM user x
INNER JOIN stg_detail
ON x.ID = stg_detail.ID

Upvotes: 1

Related Questions