Tuesday, July 5, 2011

SSIS: Update data from different table if data is Null

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

1st Table is Person.Address and 2nd table is Address2
Now I want to update data in column AddressLine2 in Person.Address by using data in Address2 with condition that AddressID is same and AddressLine2 in Person.Address is Null
So 1st we create Data Flow Task in Control Flow tab

Create new Ole DB Connection in Connection manager, and add the flow that looks like below figures
In the OLE DB Source, add the table Person.Address
In the Lookup, Add table Address2. In the column tab at the lookup editor set like this:
So this Lookup transformation is actually add a new column from other table based on addressed.
The Derived Column transformation creates new column or replacing current column values by applying expressions to transformation input columns. An expression can contain any combination of variables, functions, operators, and columns from the transformation input. So from we need this to replace the new column (AddressLine2) from table Address2 into column AddressLine2 in table Person.Address
First we choose Replace ‘OLE DB Source.AddressLine2’ in Derived Column, then we add expression:
(ISNULL([OLE DB Source].AddressLine2) ? Lookup.AddressLine2 : [OLE DB Source].AddressLine2)
The OLE DB Command transformation runs an SQL statement for each row in a data flow. For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table. So in this case, we need this transformation to update current data.
In Advanced Editor for Ole DB Command, in the component properties add SQL Command:
update Person.Address set AddressLine2 = ?
where AddressID = ?

Question mark indicate the parameter. Just remember the sequence of the parameter.
After we fill in the Sql Command, choose Column Mappings Tab and point the Available input columns to the Available Destination Columns based on the parameter that already created.
Then execute the package
Result:

1 comment:

James Zicrov said...

This actually is a crucial application and technicality to look for whenever one needs to refer something from here for solving SQL and database related problems.

SSIS Postgresql Read