Data Conversion in SSIS(Unicode String to Non Unicode)

Data Conversion is used to change the data type of a column. Let’s say you have Unicode String format in Source database and the same column is defined as Non Unicode in destination. In this case we need to convert the data before it reaches the destination and Data Conversion is the best bet for such scenarios.If you have a question that “Why the discrepancies(differences)  in the SOURCE and DESTINATION structures “, the answer is “We are not moving data from single source”. We will have different types of sources like excel,notepad,teradata,sql … So, it is obvious that the data types used will not be the same in all the databases and also all the types will not be supported by all databases and hence this Data Conversion is required.

Lets discuss a simple Example. I created a table with the name Employe and inserted some records into the same. PFB the queries used.

create table employe
(
E_id int,
E_name varchar(100),
)

Queries used to insert data.

Insert into employe values(1201,’achyut’)
Insert into employe values(1202,’ambriesh’)
Insert into employe values(1203,’anji’)
Insert into employe values(1204,’anji reddy’)
Insert into employe values(1205,’anuradha’)
Insert into employe values(1206,’anusha’)
Insert into employe values(1207,’anusha’)
Insert into employe values(1208,’arshiya’)
Insert into employe values(1209,’ashok’)

Note – You can use already existing table if you have one.

Follow the below steps to implement Data Conversion transformation.

• Open a new project and drag a Data Flow task from toolbox in Control Flow.
• Edit the Data Flow task by double clicking the object or by selecting EDIT button on Right click on the object. Make sure the Data Flow Page is opened as shown below.

• Select OLE DB data source from data flow sources and drag and drop it in the data flow.
• Double click on the OLE DB data source to open a new window where we can set the properties of the connection.
• Select the connection manager and click on new button to set the connection string as shown below.

• Set the connection to the database by providing the Server name,database name and authentication details if required.
• After the connection is set, select Data Access mode as “Table or View” as shown below and then select the table which we created just now.

• Click on columns page and select the columns we have to use as a source.
• The data source is set and now drag and drop Data Conversion transformation into Data Flow page and set the OLE DB Source Output as Input to Data Conversion transformation.
• Double Click Data Conversion to change the data type of any column you wish to and you can do that by changing DATA TYPE Column as shown below.

• You c an see the data type of column E_Name is changed from Non unicode to unicode below.

• Now the data type will be changed when ever the data passes through this transformation during the time of execution. Let’s configure the output to end this.
• Select a Flat File Destination and drop it in Data Flow page.
• Set the output of data conversion transformation as input to the flat file destination as shown below.

• Double click on Flat file destination and set the flat file connection properties as shown below.

• Once the connection is set then click on Mappings page and check the mappings are proper or not.
• Everything is SET and now we are ready to execute the package. Execute the package and make sure every control turns to GREEN which means successful.

• The above task moved data from a  table in SQL database to TEXT file and it also taken care of changing the data type of columns while moving.

Data Conversion is very useful one in the cases where you need to change the data type of columns. This is it !!