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

2 Comments »

  1. Hi, thanks for the post, when I attempt this it constructs a sql statement of
    Execute SQL Task: Executing the query “TRUNCATE TABLE truncate table PEOPLE”. Any possible solutions? Have I missed something.

    I have the execute sql task within a Foreach loop container and the table name is in a variable. In the above line ‘PEOPLE’ is the value of the variable.

    Comment by Bill — August 6, 2013 @ 3:44 am

    • Good Post!

      For solving the issue “Execute SQL Task: Executing the query “TRUNCATE TABLE truncate table PEOPLE”” below steps should be helpful:

      1) In the General tab of Execute SQL Task Editor under the section SQL statement simply write TRUNCATE TABLE

      2) In the expressions tab of Execute SQL Task Editor SqlStatementSource should be set to “TRUNCATE TABLE ” + @[User::TableName]
      Here the variable name should be replaced by appropriate variable name.

      Comment by Anchita Dubey — December 15, 2013 @ 11:00 pm


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: