asked
Why does SSIS convert my data after I import from a flat file?
I've got a fixed-width flat file I'm importing, and I've defined all the fields in the connection manager as DT_WSTR. On the connection in the data flow to the very next transform (which is a script transform in case that matters...), if I examine the metadata of the columns in the data flow path, SSIS shows some of the columns as DT_I8, DT_DATE, DT_STR, etc., even though they were specifically identified in the Flat File Import Connection Manager as DT_WSTR. In my last step, I'm trying to export various data flows to excel, and I get errors because of data type mismatches (various columns can't convert between Unicode and non-Unicode string types - the difference between the DT_WSTR columns and the DT_STR columns I presume). I don't have any data conversion transforms in the data flow, so I can only presume SSIS is converting data types for me along the way? Or do I have this wrong? If so, how do I prevent SSIS from converting the data?
Like @mrs-fatherjack said, SSIS can be a bit finicky about how it wants to interpret data, but there are ways around it. The Data Conversion and Derived Column transforms work (Data Conversion if you want to re-type in place and Derived Column if you want to create new columns), but there is another way in this case. If you right-click on the Flat File Source and go to Advanced Editor, you will see a treeview on the left-hand side. Open the Flat File Source Output option, and then Output Columns. For each column, you can change the DataType to DT_WSTR there, and the rest of the flow will have those columns interpreted as Unicode string types. This should work for your integer, decimal, and non-Unicode string types.
In my experience you can't stop SSIS from converting the data, what you can do however is convert it to what you want so that it exports in the correct format. You can do that by using the Data Conversion within the Data Flow pane.
Thanks Kevin - The advanced editor did the job! I would have thought if you went through the trouble of defining all the data types in the "Advanced" panel of the Flat File Source (which it should be noted, I thought you were referring to at first), SSIS would use those data types throughout the flow! I find it a little redundant that they make you do it again using the "Advanced Editor" on the Flat File Source object in the Data Flow, but hey - as long as it works... The feedback from both is much appreciated!
9 People are following this question.
