Home > Misc, SQL 2005, SQL 2008 > SSIS – Daily Refresh Job

SSIS – Daily Refresh Job

In this post I am going to explain the steps we should follow to create an SSIS package that run’s every night. The package will ensure already imported records are updated and new records are created in the destination system.

For demonstration I am assuming a source system, say CRM exists and it has a view as shown below


The job we are going to create will import this data into destination system, say Employee table as shown below


Now open SQL Business Intelligence Studio. Create new project and select “Integration Services Project” as shown below


From toolbox, drop a “Data Flow Task”


Double click to go inside the Data Flow Task and add a new OLE DB source control


Double click OLE DB Source control.


First we need to setup a new connection. For that click on the New button, enter the source database information. Once completed, select the connection. And set other properties as shown below


Next step is to add a Lookup transformation. This will help us to identify whether the record exists in the destination or not.


Before that we should add a new connection to the destination.


Now we can setup the lookup transformation as shown below


Lookup transformation by default fail if no match is found. In our case for new records this scenario will come up. To ensure new records pass through the pipeline we should set the Error Output properly.


Next, add a conditional split control


Drop the data flow arrow into conditional split control, which brings up following dialog. Select “Lookup Match Output”


Double click Conditional Split control and set following values


Now we should add the ole db destination and a ole db command controls. To “OLE DB Destination” we should select the input “New_Records” from Conditional Split. To “OLE DB Command” we should select the input “Modified_Records” from conditional split.

Make following changes to OLE DB Destination



Make following changes to OLE DB Command control





Now the package is ready for testing, hit F5 to run the package


In my next post I will show how to schedule this package to run every night.


Categories: Misc, SQL 2005, SQL 2008 Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: