Separating Strings from a CSV File in SSIS 2008

I have data which resembles the following:


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?


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 that for each letter in your field. Make the Data Type a Boolean (DT_BOOL).

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)

Need Your Help

Suggest a solution for event notification using nServiceBus

events nservicebus

Currently we are looking for a solution to have unique profile for our user. We are having a different set of applications and the different profiles like is SAP , in DB and in AD too. We want to m...

Need a simple and easy pagination in html table

javascript jquery html css

Need easy and light paging in HTML table using jquery.