Convert the data type of an incoming data – Data conversion task – SSIS
Data is stored in different location and in different formats and types. It depends of the nature of the application wherein data is stored in different data types. We have two standard types of data Numeric and Alpha-Numeric. For instance, Zero to Nine is treated as numeric and A to Z and numeric combination as Alpha Numeric.
Application development tools has plenty of data types that helps developer to use appropriate data type for the application needs and it includes size of a data.
learn more about SQL Server data type here.
Data Conversion Transformation
Data Conversion transformation task can be used to convert a type for data from one to another.
Learn more about Data Conversion data types here.
It helps SSIS developer to change the data type of a column in the input dataset. We should be aware of the datatype and its length before performing datatype conversion.
For instance, If we try to change an int datatype value to small int then we may lose some data. Because, small int datatype cannot accommodate the length of int datatype values. So, check the datatype capacity to avoid data loss during data conversion.
I have given an excel file to upload the excel data to SQL Server. Excel file contains name of the employee and their working hours. While connecting to the excel file I have noticed that the string column that is name of the employee is stored as Unicode (nvarchar). But, SQL Server table is designed to store non unicode(varchar) data.
In this scenario, I will be using Data Conversion transformation task to convert nvarchar to varchar.
Step 1: Create a package and add a data flow task.
Step 2: Go to data flow design interface and Add Excel data source task and connect with the excel file.
Step 3: Add Data Conversion task and connect excel source with it.
Step 4: Open Data Conversion Transformation editor. Now, Select employee name. The data type of the excel source for the employee name is unicode. But, we need string data type. So, change the datatype or the column to string.
The default length of the string data type is 255. But, We need only 50 character. So, edit the length from 255 to 50.
Step 5: Add ADO.Net destination and connect data conversion task with it. Finally, map the source column with the destination. Though we have added a new column in the data flow for the employee name in the data conversion task, we need to map copy of employee name column with the destination Empname column otherwise we will get a warning symbol.
Step 6: Execute the package.
Thanks for reading.