Irfan's World

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.

Advertisements

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

Blog at WordPress.com.