Separating Semicolon Separated Fields

Question

Separating Semicolon Separated Fields

mike furnari

SSC Rookie

Points: 31
More actions
October 20, 2008 at 8:47 am

#124097

Hey,
I have a flat file with 2 columns in it. an Identity field and a semicolon separated field
I need to insert each code and its corresponding ID into a new table.
Source Table
ID Codes
"1" "AB;CD"
"2" "AB"
"3" "ED"
Destination Table
ID Code
"1" "AB"
"1" "CD"
"2" "AB"
"3" "ED"
In DTS I used a Cursor to do this but I thought there would be an easier way in SSIS.
thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

In SSIS you can use the flat file source specifying semi-colon as the delimiter and should be able to directly connect that to an OLE DB Destination.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mike furnari SSC Rookie Points: 31 More actions · Answer 2

Sorry for the confusion..

The flat file is actually Comma Separated.. but the Code Column is Semi-Colon Separated..

so the flat file would look like this

ID,Codes

"1","AB;CD"

"2","AB"

"3","ED"

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Ahhh. I have done similar using a script component. It seems to work fairly well. It is a bit of a performance bottleneck because it has to run asynchronously in order to add rows to the dataflow. Here is the script:

[font="Courier New"] Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim fieldcd As String = Row.fieldcd

Dim ministryList As String = Row.ministries

Dim delimiter As String = ","

If Not (String.IsNullOrEmpty(ministryList)) Then

Dim ministryListArray() As String = ministryList.Split(New String() {delimiter}, StringSplitOptions.RemoveEmptyEntries)

For Each item As String In ministryListArray

With Output0Buffer

.AddRow()

.fieldcd = fieldcd

.ministry = item.Replace(ControlChars.Cr, "").Replace(ControlChars.Lf, "").Replace(ControlChars.Tab, "").Trim()

End With

Next

End If

End Sub

[/font]

You would change fieldcd to ID and ministry to codes. This basically creates a new dataset that gets passed on to the next component. To do this you have to set the Output SynchonousOutputID to None like in this screenshot:

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mike furnari SSC Rookie Points: 31 More actions · Answer 4

Thanks It works great. This is exactly what I am looking for..

The only problem was that I could not set the SynchonousOutputID to None because of the following error..

"None is not a valid value for Int32."

I just changed it to 0, and all seems good.