1

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?

Improve this question

2 Answers 2

Reset to default
3

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.