SSIS: Update data from different table if data is Null
SSIS: SSIS stands for SQL Server Integration Services. It is the new data transformation standard for SQL Server 2005 and has replaced the old SQL Server Data Transformation Services.
In this post, I want to show how to update data from different table with condition that the data is Null.
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 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