I have a small issue with my SSIS package. I am importing a flat file. One of the fields is incoming with a decimal value, i.e. 32.76. It is being mapped to an int data type column. What is strange is that if the input flat file is comma delimited, it will import fine, though the decimal part of the value is truncated. But if the flat file is vertical bar delimited (|), then the SSIS package throws an error, indicating the input type is string and cannot be converted to type int. That is understandable, though I'm not sure why comma delimited would truncate, where vertical bar delimited throws the fatal error. it's the only difference between the two.
And would changing the column from int to decimal fix the issue? I'm reluctant to do that.
Thanks
If some of the fields values contains the delimiter character it will cause be considered as two fields, to solve this problem you have to change the delimiter or adding text qualifier to the columns.
Text qualifier = Character that text fields are enclosed with
Read more in Flat File Connection Manager
I am not sure if rounding decimal value is done on the Source, so if the field contains decimal values, so you have to change the type to decimal or leave it of type string and add a Derived column transformation or a Data Conversion transformation to convert it to integer (I prefer to work with the second approach).