LOOKUP Transformation task in SSIS 2008 to copy no match row from source to destination in SQL Server 2008

Yesterday I wrote one of the very simple articles to copy data from source SQL Server to destination at “Transfer data from one instance/database to another instance/database in SQL Server with SSIS”. You can use it, if task needs to be run only one time. If you want to keep running this job daily, weekly or after every certain period of time, you don’t need all data to be copied over again and again. You want to copy only those data which are not exists in destination table.

In order to achieve this task in SSIS 2008, we are going to use “LOOKUP Transformation” task between our source and destination. LOOKUP will do the job of checking source and destination. It can return match/not match data as per your need.

In order to do this job, we will need two dummy database and table in which we perform this exercise. Let us create two data ADV1 and ADV2, or you can use your two databases. Run following TSQL script.

--database 1

USE adv1

go

create table ChemicalConsume

(

      ChemID int,

      QuantityInLtr int,

      ConsumeDate datetime

)

GO

insert into ChemicalConsume

SELECT 1,10,'06/06/2010' union all

SELECT 1,14,'06/07/2010' union all

SELECT 2,8,'06/08/2010' union all

SELECT 2,10,'06/09/2010'

GO

--database two

USE adv2

go

create table ChemicalConsume

(

      ChemID int,

      QuantityInLtr int,

      ConsumeDate datetime

)

GO

insert into ChemicalConsume

SELECT 1,10,'06/06/2010'

GO

Now, open BIDS and create new project for Integration Services and drop “Data Flow Task” in your work area from toolbox. To configure it, double click on that so that you will be redirected to the “Data Flow” tab.

Now, on dataflow tab, please drag “ADO NET Source” task from toolbox and drop it to your work area.

Double click on it to configure and set your database and table of ADV1 database there, for more information look at below screen shot.

Once your ADO NET Source is ready with configuration, drag “LOOKUP”  transformation task to your work area and join LOOKUP task with ADO NET SOURCE. Now, double click on “LOOKUP” to configure it.

Since, we want to copy only those rows which doesn’t exists in destination table, we have to select “Redirect rows to no match output” option from LOOKUP Transformation Editor and click on  “Connection” tab at left hand side.

From the connection tab, select your destination database and table. For more information, please look at the below given screen shot.

Now, please understand this concept, “ADO NET Source” returns row from ADV1.ChemicalConsume table which will be “Input” for our “LOOKUP” task. In the connection tab of LOOKUP task, we have made connection to “ADV2.ChemicalConsume” table as a destination so that LOOKUP will compare source and destination. 

Now from “LOOKUP Transformation Editor”  click on “Column” tab to set on which columns we are going to make comparison. Our comparison will be on “ChemID” and “ConsumeDate” fields of both the tables. So let us do it.

Now, take “ASP NET Destination” from tool box and join it with green arrow of “LOOKUP” task.

As soon as you will try to join green arrow of “LOOKUP” with “ASP NET Destination” you will get on small dialog box named “INPUT OUTPUT SELECTION”. Set the drop down value as shown in below screen

Now finally double click on “ADO NET Destination”  to configure it and set your ADV2 database along with appropriate table.

Now, run your package by hitting F5 and see output of the package.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com