Posted by: Red Souldier | November 30, 2009

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.

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:

Advertisements

Responses

  1. Hi redsouljaz thx for sharing.
    This tutorial is very helpful


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: