SSIS Data Type Conversion Testing

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:

• One way is using T-SQL code. Using the CAST function in your code can convert one data type into another. The scope of this tip is not intended on going into detail about the CAST function, but I will show a quick example. MSSQLTips.com offers numerous tips regarding how to use CAST.
  • Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions
  • SQL Server function to convert integer date to datetime format
  • Date and Time Conversions Using SQL Server
• The second way is using the Data Conversion Transformation Task. This task will convert the data in an input column to a different data type and then copy it to the output column. The task itself isn’t too complicating to setup as I’ll show you in this tip.

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:

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:

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 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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

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?

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

SQL Server CAST Function with Excel Destination

The following test is performed using a OLEDB Destination:

SQL Server Integration Services Data Conversion Task with OLEDB Destination

SQL Server CAST Function with OLEDB Destination

Next Steps

• As you can see the performance differences in my testing were very minimal.  Performance testing may vary based on each system due to available resources and the data type conversion.  Be sure to test thoroughly.  Here is another tip to consider when changing data types – Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions.
• View more SSIS Data Flow Transformation tips courtesy of MSSQLTips.com here.