3

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

Hadi
  • 36,233
  • 13
  • 65
  • 124
Scott
  • 165
  • 1
  • 3
  • 15
  • 1
    You should change the column type to decimal as that is the data you are getting. If you want to round it in your data flow, that is your prerogative. – KeithL Feb 14 '18 at 18:18

1 Answers1

2

Comma vs Vertical bar

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

How to convert into Integer

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).

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Worked thanks. Still not sure why it behaves differently comma delimited, vs vertical bar delimited, but whatever works. The comma delimited format does that cast by itself, where the vertical bar delimited requires that specific cast you mentioned. – Scott Feb 14 '18 at 19:00
  • @Scott Happy to help :) Also you have to accept or upvote this answer if it solved the issue – Hadi Feb 14 '18 at 19:02