Today I came across a very good article by the SQL CAT team about the performance differences between the different ways to convert data types in SSIS.
I will not repeat the whole article (the link is at the bottom), but I do want to point out the test and the result they presented.
They built a baseline package on SQL Server 2008 with 30 million rows and 3 columns in varchar(50) format. They applied a data type conversion in a number of places in the package to test which one is the most efficient. These are the places where they did the transformation: in the query, in the advanced options of the OLEDB adapter, in a Data Conversion component, in a Derived Column component.
The results were as follows:
Time(sec) | CPU Utilization % | Buffers in Use | |
Baseline | 35 | 75 | 1 |
Transact-SQL conversion | 46 | 75 | 1 |
Conversion on the OLE DB Source component | 45 | 70 | 1 |
Data Conversion transformation | 49 | 90 | 1-3 |
Derived Column transformation | 71 | 85 | 7-8 |
What stands out most is that the solutions with SSIS components that change the data flow are slower and heavier than the solutions that change the incoming dataset directly. Not a completely unexpected conclusion, but good to see it confirmed in some numbers.
The final conclusion of the article is that if you want to do a conversion fast and efficiently, you should not put it in an SSIS component. The best choice is the T-SQL or the OLEDB solution.
One reason to deviate from this and still do the conversion in SSIS is that it gives you good debugging options. In that case, always pick a Data Conversion component and not a Derived Column… :)
You can find the full article here
Frequently asked questions
What is the fastest way to convert data types in SSIS?
The SQL CAT article concludes that conversion in the T-SQL query or in the OLE DB adapter is the fastest. SSIS components that change the data flow are slower and heavier.
What did the SQL CAT article on data type conversion in SSIS test?
A baseline package on SQL Server 2008 with 30 million rows and 3 columns of varchar(50). They tested the conversion in the query, in the OLE DB adapter, in a Data Conversion component and in a Derived Column.
Is a Data Conversion component or a Derived Column better in SSIS?
If you still have to do the conversion in SSIS, for example for debugging, pick a Data Conversion component. It is better than a Derived Column.
Why would I still do data type conversion in SSIS?
It gives you better debugging options. For speed, the conversion in T-SQL or in the OLE DB adapter is better.
Where can I do the data type conversion in an SSIS data flow?
In four places: in the query, in the advanced options of the OLE DB adapter, in a Data Conversion component or in a Derived Column component.
1 comment
The resources of a non-self-governing territory are the of its legitimate citizens. Israel is obliged as the “Occupying Power” “The occupying State shall be regarded only as administrator and usufructuary of public buildings, real estate, forests, and agricultural estates belonging to the hostile State, and situated in the occupied country. It must safeguard the capital of these properties, and administer them in accordance with the rules of usufruct.â€It is a war crime to use Palestinian resources to benefit illegal settlers.
Related blogs
Controlling ETL jobs in a Data Warehouse
Metadata – Table growth in data warehouses
One Single Version of the Truth does not exist