Separating Strings from a CSV File in SSIS 2008

Question

I have data which resembles the following:

"D.STEIN","DS","01","ALTRES","TTTTTTFFTT"
"D.STEIN","DS","01","APCASH","TTTTTTFFTT"
"D.STEIN","DS","01","APINH","TTTTTTFFTT"
"D.STEIN","DS","01","APINV","TTTTTTFFTT"
"D.STEIN","DS","01","APMISC","TTTTTTFFTT"
"D.STEIN","DS","01","APPCHK","TTTTTTFFTT"
"D.STEIN","DS","01","APWLNK","TTTTTTFFTT"
"D.STEIN","DS","01","ARCOM","TTTTTTFFTT"
"D.STEIN","DS","01","ARINV","TTTTTTFFTT"

I need to break out the final string into separate columns for import into a SQL Table, one letter into each field. Therefore, "TTTTTTFFTT" will be broken out into 10 separate fields each with a single bit value.

I've used a Flat File Source Editor to load the data. How do I accomplish the split?

Answer 1

I don't know SSIS. But perhaps you can do transformations like these, one for each bit column:

case substring(Field5, 1, 1) when 'T' the 1 else 0 end
case substring(Field5, 2, 1) when 'T' the 1 else 0 end
case substring(Field5, 3, 1) when 'T' the 1 else 0 end
...

If the bit column is non-nullable, you may need to do this:

isnull(case substring(Field5, 1, 1) when 'T' the 1 else 0 end, 0)

Answer 2

Use a Derived Column Task. In there, create a new column for each of the fields you want, using an expression something along the lines of

substring([ColumnName],1,1) == "T" ? 1 : 0

So that's basically a condition saying for the first character, if it is a T then make it a 1, else 0...do that for each letter in your field. Make the Data Type a Boolean (DT_BOOL).