Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

More ways to change data types using SSIS

  • 16 September 2013
  • Author: Kathi Kellenberger
  • Number of views: 17157






A couple of weeks ago I posted a blog about values not matching the Lookup transform in SSIS because the data types were different in the columns to compare. I showed a method of changing a data type in the Advanced Properties of the source. Then, later I thought about a couple of ways that are better that accomplish the same thing. The reason I think they would be better is that they are more obvious when troubleshooting an issue later.

The most obvious way to change the data type of a column is to use the Data Conversion transform. It is really easy to use and you just have to select the column you need to change and select the new data type.

Another way to change the data type is by using the Derived Column transform. This is a bit more difficult to use since you will need to create an expression using one of the CAST functions instead of just selecting a new data type.

A third way is to use the CAST or CONVERT function in the query itself like this:

SELECT CAST(Qty AS Numeric(8,2) ) FROM myTable;

Probably the best method to change the data type is by using the Derived Columns, but there are always more ways than one to accomplish something in SSIS or, really, just about any technology.


Categories: SQL Server
Rate this article:

Please login or register to post comments.