The Data Conversion Transformation

b
Fig 1: The Data Conversion Transformation

In this post I will be covering the Data Conversion Transformation. The sample package can be foundĀ here for 2005 and guidelines on use are here.

What does the Data Conversion do?

The transformation takes input columns and creates a copy of that column with a new data type. This is a vital component in SSIS because SSIS is very fussy about Data Types – you cannot force an integer typed column into a string destination. It should go – in theory – but this strictness prevents sloppy errors and more importantly allows SSIS to be truly platform independent by ensuring its internal data types can be reliably matched to the target.

Configuring the component is pretty simple – you pick your input column, set the name of the output and pick the target data type. Depending on the target data type you may have other configuration options such as precision, scale, length or code page. The snapshot below is taken from the sample package.

b
Fig 2: Configuring the Data Conversion Transformation

An important thing to be aware of is how the Data Conversion transform handles missing data. A blank field will return an error when you try to convert it to pretty much anything but a string – if you have missing values that you want to sail through this component, you must convert them to NULLs first, as a NULL can be converted (to the appropriate NULL) but a blank string is technically a value, and one that cannot be validly converted. It would be good if there was an option to treat blanks as nulls, but for now there isn’t, so be careful. There is a demonstration of this in the sample package in Data Flow 2.

When would you convert Data to a new Type?

The need for this kind of change is manifold – two of my most common uses would be converting data from text files to the correct types – from strings into dates, numbers, currency etc. – and when moving data between relational systems. While an ADO .NET source may give you data as one general type – say an int, you may want to put it in a different type field for an SQL Server database, such as a tinyint.

MSDN Documentation for the Data Conversion Transformation can be found here for 2008 and here for 2005.

10 thoughts on “The Data Conversion Transformation

  1. I have been working on an SSIS project for two months and noticed several days ago, the data conversion component does not exist in the data flow. Therefore, when changing and/or saving to a different table name, I receive a red error mark on the destination. The project requires that I import flat files into database tables which works fine but changing the table name in the data flow area cause a destination failure. Are there ways of manually or automatically adding the data conversion component to 600 .DTSX XML file packages. I used the SSIS wizard to build these packages.

    Thank you in advance for your assistance.

    -Terry

  2. To be honest i’m not quite sure what you are getting at… I don’t think the Data Conversion component is what you are looking for.

    If you change the destination of a data flow it needs to validate all the column mappings, data types etc. If the source and targets are not identical then you will have to rework your packages.

    Can you do it automatically? Yes. But not quickly, writing such code is not a trivial challenge.

  3. i want to import data from a table in one db to a table in another db. The problem is..there is difference in datatype in these tables (in source fields are nvarchar and in destination its varchar). Can i use this dataconversion tool for achieving this. If any other alternative is there..please help me..Thanks in advance

  4. Hi, I’m importing XML into sql table of XML column type. output from script component is Text stream and i want to insert that into column of XML data type. Can you please tell me which conversion i need to choose in Data conversion..

    Thanks!
    Kumar

  5. Hi, I have data with data type decimal (6,2) in my database. I would like to have output in flat file to be like + space with the remaining length. For example, the data is 2.00 and I want the output looks like “2.00 ” (the length is 7 char). Anyone has suggestion on this? Thanks for your kind help.

  6. Thanks for your reply. I have converted the data using float. However, it will only display 0.2 (for value 0.20) and 50 (for value 50.00). The data type is decimal (6,2) in my database. This is what I did in Derive Column:

    Derive Column Name Derive Cloumn Expression Data Type
    Copy of F00125 Replace ‘Copy of F00125′ RIGHT((DT_STR,7,1252)F00125,7) float [DT_R4]

    *FYI F00125 is my filed with value 0.20 with length 7 char

    I would like to have an output like this in my text file:
    “0.20 ” —-> 0.20 + space for the remaining length (total length is 7)

    Thanks in advance.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>