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. 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
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.
Print this post
No comments:
Post a Comment