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

January 30, 2011

How to Load Unicode Data from Excel to SQL Server that supports non Unicode format ?

Filed under: SSIS — Tags: , , — Irfan @ 9:23 am

Requirement (2): Load the data from an excel file to a non Unicode SQL Server 2008 database table. 

In my earlier post  here , we dealt with Unicode to Unicode format. Today, I will show you how to load Unicode data from a excel file to non Unicode table.

We do need both excel(source) and OLE DB (destination) connection managers. When we load Unicode data to non Unicode format database table, it throws the following error in SSIS Business Intelligence Development Studio (BIDS).

Error Message : Cannot convert between Unicode and non Unicode Character string….



To resolve the above error and move forward, we first need to convert the Unicode characters that are coming from excel source file and send it to the target component.

Moreover, SSIS does not even allow you to run the task without solving the error.  How do we resolve this ..? 

There is a special task called "Data Conversion" available in BIDS in Data Flow transformations. We will use this task to convert the data from Unicode to non Unicode format.

First insert the "Data Conversion" task between the source and target, after the insertion our Data Flow looks like this.




Now double click the Data Conversion task and change the data type as shown below.  The following screen shot shows the data type’s Converted from DT_WSTR(Unicode) to DT_STR(non Unicode string) 🙂



Click OK to close the Data conversion transformation editor. Go back to the Data Flow OLE DB Destination and map the columns correctly as shown below in .

We have to use the new columns that are non Unicode coming out of “Data Conversion” task and map to the Destination columns of the target table.



Now, Close the Destination by Clicking OK and execute the task by right clicking and selecting Execute Task from the Context menu. It will execute successfully and loads all the rows in the destination table.
Following screen shot displays the no. of rows read from the source, converted to string and loaded them successfully in the target table.



You can check the data in the target table either from BIDS or SQL Server Management Studio.  Hope I’ve explained clearly.  Thanks for reading.

January 25, 2011

How to load data from an excel file using SSIS 2008 when target table supports Unicode ?

Filed under: SSIS — Tags: , , , , — Irfan @ 12:47 am

Requirement (1): Load the data from an excel file to a Unicode database SQL Server 2008 database table.  

 
    Let’s first see what we have. We have an excel file in Office 2007 format that has 57 rows to load into a destination table in the Target database using SQL Server 2008 Integration Services.

Source : Excel file in MS Office 2007

No. of rows : 57
Destination : SQL Server 2008 Database

No. of rows after the load : 57

 

This would be an easy task, we can load the data without any issues. The source data file looks like below which has about 7 columns:

Now to load the data using SSIS, you need to install SSIS (SQL Server Integration Services) on you machine. 

    Open BIDS
    Create a Package and give it name and save it
     Create one excel Connection Manager and one OLE DB Connection Manager
    Create a Control Flow in which you will create a Data Flow. (This data flow is shown below)
  

And, the SSIS package developed in BIDS (Business Intelligence Development Studio) to load the data from excel file looks like below, It has a data flow that contains a connection to an excel data source file plus it has an OLE DB connection that connects to our Target table in the Destination database.

The destination table has the following data type’s that stores Unicode characters without any issues. Note to the readers, SSIS 2008 when reads first 8 records from the excel file it decides the data type of that column, Here the excel file string columns are read by SSIS as Unicode strings and our Target table is having Unicode data type’s which supports Unicode characters that can be stored easily. So the data type’s in source and target matches.

When the job is executed in BIDS (Business Intelligence Development Studio) it loads all the data to the destination table successfully.

Unicode ——-> to ——> Unicode (57 rows are loaded and the Job works perfectly, No worries) 🙂

Wait, what happens when we try to load the same excel file data to a non Unicode target table ?

<!–[if get mso 9]> Normal 0 false false false EN-US X-NONE AR-SA <![endif]–><!–[if gte mso 9]> <![endif]–> <!–[endif]–>

Source : Excel file in MS Office 2007 format

 

No. of rows : 57

Destination : SQL Server 2008

 

No. of rows after the load : 57

January 24, 2011

SQL Server Dates – Part 2 (Quarter)

In this post, I will be showing you how to generate Quarterly dates using getdate() database function.

Quarter Dates

The following code will generate First Day of the Previous Quarter, Last Day of the Previous Quarter, First Day of the Current Quarter, Last Day of the Current Quarter.

select ‘First Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())-1,0)
select ‘Last Day of the Previous Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)-1
select ‘First Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate()),0)
select ‘Last Day of the Current Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)-1
select ‘First Day of the Next Quarter :’ , DATEADD(QQ,DATEDIFF(QQ,0,getdate())+1,0)

Wait for my next post for more Quarterly dates. Click for Date Part 3

SQL Server Dates – Part 1 (Year)

To display today’s date in SQL Server 2008 select GETDATE().

Following are some of the dates that can be used in SQL Queries to fulfill the end user needs while retrieving data from the database or creating custom objects for delivering reports in BI tools.

select ‘First Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())-1,0)
select ‘Last Day of the Previous Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)-1
select ‘First Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate()),0)
select ‘Last Day of the Current Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)-1
select ‘First Day of the Next Year :’ , DATEADD(YY,DATEDIFF(YY,0,getdate())+1,0)

I will be continuing on how to generate Quarterly dates in my next post Quarter Dates

Create a free website or blog at WordPress.com.