mssqltips logo

SQL Server Integration Services Data Type Conversion Testing

By:   |   Updated: 2013-10-01   |   Comments (2)   |   Related: More > Integration Services Data Flow Transformations

Problem

I've been tasked with a project where I need to extract data from one source and load into a destination. I plan on using SQL Server Integration Services (SSIS) and that seems simple.  The only caveat is that I need to modify some of data types during this conversion. What's the best way to do this using SSIS?  Are there any performance considerations?  Check out this tip to learn more.

Solution

There are two ways of converting data types within SQL Server Integration Services (SSIS).  Here is an overview of those options:

SQL Server Integration Services Data Conversion Task to Convert Data Types

Let's look at the Data Conversion task first...

First, open Visual Studio (or Business Intelligence Dev Studio if you're using pre SQL Server 2012) and create an SSIS project. Next, we can go ahead and configure a Connection Manager to our database. Right click Connection Managers in Solution Explorer and choose New Connection Manager:

Let's look at the Data Conversion task first...

Choose your Connection Manager type. In this example, we'll use OLEDB. Next, configure the Connection Manager to point to your dataset. In this example, I'll use localhost and the AdventureWorks2008R2 database:

Choose your Connection Manager type

Test the connection and click OK. Next, drag a Data Flow task from the SSIS toolbox onto the design screen:

Test the connection and click OK. Next, drag a Data Flow task from the SSIS toolbox onto the design screen

Right click the Data Flow task and choose Edit. You are now inside the data flow task. This is where all the action happens. Drag an OLEDB source task from the SSIS toolbox to the design screen:

Right click the Data Flow task and choose Edit

Right click the OLEDB task and choose Edit. This screen is where we will define the Connection Manager we created earlier. Under OLEDB connection manager choose the connection you created. Leave data access mode as Table or view. Change the name of the table or the view to the table that contains the data types to change. In this example, I'll use a table named Sales.CurrencyRate:

Right click the OLEDB task and choose Edit

To preview the data click Preview. In my example, I want to change the CurrenyRateDate and the ModifiedDate columns from a datetime data type to a date data type:

I want to change the CurrenyRateDate and the ModifiedDate columns from a datetime data type to a date data type

Click OK to close the OLEDB Source task. Drag the Data Conversion Transformation task onto the design screen. Connect the OLEDB Source task to the Data Conversion task:

Drag the Data Conversion Transformation task onto the design screen

Right click the Data Conversion task and choose Edit. Here is where we will convert our data types. Since I am converting CurrencyRateDate and ModifiedDate I will click on each of them in the Available Input Columns list:

Right click the Data Conversion task and choose Edit

The Input Column selection is the source column while the Output Alias is the name of the new column after the data type conversion. The Data Type column is where you will change the data type. If you select a string value in the Data Type column specify the length in the Length column and if you are changing numeric data specify the Precision and Scale in the appropriate columns.

In this example, I'm changing CurrencyRateDate and ModifiedDate from "database timestamp" to "database date" since I don't want to show the time in the destination:

I'm changing CurrencyRateDate and ModifiedDate from "database timestamp" to "database date"

Click OK. Drag the Derived Column task from the SSIS toolbox onto the design screen. Connect the Data Conversion task to the Derived Column task:

Drag the Derived Column task from the SSIS toolbox onto the design screen

Right click on the precedence constraint between Data Conversion and Derived column and click Enable Date Viewer. This will allow us to view the data as it passes through the constraint:

Right click on the precedence constraint between Data Conversion and Derived column and click Enable Date Viewer

Let's view our data. Click the Start Debug button on the toolbar to debug:

Let's view our data. Click the Start Debug button on the toolbar to debug

Tada! You can see the data has changed from a datetime data type to a date data type:

You can see the data has changed from a datetime data type to a date data type

Now that I have the data converted the way I want it I can remove the Derived column task and put an Excel destination task in its place:

Now that I have the data converted the way I want it I can remove the Derived column task and put an Excel destination task in its place

I already have an Excel spreadsheet setup on my local hard drive with the column names as headers. Point the Excel destination task to this spreadsheet and execute the task to move the converted data into Excel:

Point the Excel destination task to this spreadsheet and execute the task to move the converted data into Excel

 

Using the T-SQL CAST Function in SQL Server Integration Services to Convert Data Types

Now that we've went over how to use the Data Conversion Transformation task I'll show you a quick example of using the CAST function in your T-SQL code. Remove the Data Conversion task, right click the OLEDB Source Task and choose Edit:

I'll show you a quick example of using the CAST function in your T-SQL code

Change the Data access mode to SQL Command and use the following SQL. You will notice that I'm using CAST to change the data type to date:

SELECT CurrencyRateID
 ,CurrencyRateDate
 ,FromCurrencyCode
 ,ToCurrencyCode
 ,AverageRate
 ,EndOfDayRate
 ,ModifiedDate
 ,CAST(CurrencyRateDate AS DATE) CopyofCurrencyRateDate
 ,CAST(ModifiedDate AS DATE) CopyofModifiedDate
FROM AdventureWorks2008R2.Sales.CurrencyRate

Change the Data access mode to SQL Command and use the following SQL

Connect the OLEDB Source back to the Derived Column, Enable the Data Viewer and click the Start Debugging button:

Connect the OLEDB Source back to the Derived Column, Enable the Data Viewer and click the Start Debugging button

You can see that using the CAST function provides the same results as using the Data Conversion task, but which one is better for performance?

You can see that using the CAST function provides the same results as using the Data Conversion task, but which one is better for performance?

 

SQL Server Performance Analysis for the T-SQL CAST Function vs. the SSIS Data Conversion Task

Check out the tables below for performance measures.  The following test is performed using an Excel Destination:

SQL Server Integration Services Data Conversion Task with Excel Destination

Record Count Elapsed Time
1000 1.67 seconds
10000 2.10 seconds
100000 11.4 seconds
250000 23.57 seconds

SQL Server CAST Function with Excel Destination

Record Count Elapsed Time
1000 1.64 seconds
10000 2.10 seconds
100000 10.34 seconds
250000 24.15 seconds

The following test is performed using a OLEDB Destination:

SQL Server Integration Services Data Conversion Task with OLEDB Destination

Record Count Elapsed Time
1000 0.15 seconds
10000 0.2 seconds
100000 0.59 seconds
250000 1.20 seconds

SQL Server CAST Function with OLEDB Destination

Record Count Elapsed Time
1000 0.2 seconds
10000 0.23 seconds
100000 0.79 seconds
250000 1.54 seconds
Next Steps


Last Updated: 2013-10-01


get scripts

next tip button



About the author
MSSQLTips author Brady Upton Brady Upton is a Database Administrator and SharePoint superstar in Nashville, TN.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Wednesday, December 20, 2017 - 6:23:21 PM - Gerhard Kandler Back To Top

Hi Experts,

this article sounds very interesting to me. If MS Visual Studio Integration Services environment knows the types of columns, that are returned by an sql select statement, type conversion seems not to be a problem. On the other side, if you are using stored procedures, where the column-types returned are not really known to the environment, i didn't just find a useful solution for data transformation. Example: If i try to convert column values, that are known to me as string type, i always get the error message "use object type variables". I didn't just get a satisfying result, except to use script task, but this cannot be the solution, because script task creates methods depending on the names of variables and this is unsatisfying to me. Any idea, how to solve such trouble ?

Regards,
Gerhard Kandler


Monday, November 17, 2014 - 9:12:47 AM - mymemoboxs Back To Top

SSIS Data Convert from MySQL to SQL server

http://www.mymemoboxs.com/



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools