Irfan's World

July 31, 2011

How to truncate and load a table from SSIS ?

When you use SSIS for loading data into a database or a data warehouse or a staging database, some times you need to wipe out all the records first and load the new data from the source to target.  This can be accomplished in different ways, but I am going to show you a method that I use very often and may be useful for you and this technique do not have a data flow task and no mappings. 

   Truncate and Load a target table.

My source database is AdventureWorksDW and my target database is SampleDB.  SampleDB is a exact copy of AdventureWorksDW.  I copied the ADW database and renamed it because I need a source and target, as well as I need the same structure of the source tables in target database. WinkingIn this method,  I am going to use a table DimAcount from my source database and target database.

(Please note, I’m assuming some basic understanding of SSIS, so I’m skipping the "how to create a project", etc. stuff and just going to the pertinent parts).

Following are the steps that we need to follow: 

         Step 1: Define connections to Source and Target.
         Step 2: Get the table name and save it in a user variable.   
         Step 3: Truncate the table in Step 2.

         Step 4: Load
the Target table ?

Step 1:
Following is a screen shot that shows the two connections defined in the connection manager for Source and Target databases in my project.
  

image

Step 2:
In order to complete this step, I am going to use an SQL query to get the table name, and then the returned result will be stored in user variable that I declared as vSourceTable of type String.

image 
Add a ‘Execute SQL Task’,  a query is passed to get the “DimAccount” table from source database. This query will return the table name “table_name”, following are the screen shots for the General and Result Set tab.  (renamed this task as Source – Get the table ?)

image


image

Step 3: 
Add another ‘Execute SQL Task’, this task is used to send the Truncate command from SSIS to the target database.  Following are the screen shots for the General and Expressions tab.
(renamed this task as Target – Truncate table)

image image

Step 4:
Add another Execute SQL Task to send an INSERT command (DML statement) to the target database, this will directly load the data from source to target.  This technique will work when there are same no. of columns exists in both source and target tables.  (renamed this task as Target – Load table )

image image

At last the SSIS job will look like below.  Execute the package, At this point, you should be able to successfully execute your package. Upon successful execution, the package will get the table, truncates and loads the target table. 

image

That’s all for now. Hopefully this gives you an idea of how easy to dump the source data into the target database using SSIS.  Cool

Advertisements

Create a free website or blog at WordPress.com.