January 23, 2009 at 8:32 am
I tried to convert several columns in from string to currency using SSIS Derived Column or Data Conversion transformations but no matter how I tried I got an error message they were incompatible.
After spending a couple days on this, I decided to use my program do the conversion. Not the best way but our deadline is coming up fast.
When working with the fields in the program (vb.net), I ran into the same problem. The program would not recognize a field without data as a null or nothing. Intellisense told me that for example one field looks like this " " and another like this " ". It was saying something was in the field. I went back to the COBOL FD's I received with the flat file and found that the first field had length of 12 and the second a length of 6. So in my program I wrote
If strFirst = " " Then
do this
End If
If strSecond = " " Then
do this
End If
The program recognized this and I am able to then use it to enter what I need to into the field.
All of that to say, I would rather be to do the conversion in SSIS. Has anyone worked with this type of a file before?
January 23, 2009 at 10:29 am
The way your describing your problem is confusing me.
It is failing because your trying to cast a String of spaces into a currency type. a string of spaces does not/cannot convert to currency.
Change your derived column expression to something like
Where Col5 is your Column
ISNULL(Col5) || TRIM( Col5) == "" ? (DT_CY)0 : (DT_CY)Col5
IN SSIS Expressions IF is inplied.
so your expression reads IF Col5 ISNULL, or Remove spaces from column5 = Blank ? True = Cast 0 to currency, = FALSE CasT Col5 to Currency.
January 23, 2009 at 11:40 am
That did it!!!!!!! 🙂 It works perfectly!!!!! Thank you so much for your help. Not only for showing the example but for explaining it so well. Adding an explanation not only helps me but others who are new to SSIS. Thanks again!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply