Split flat file column data into multiple columns using ssis

Hi All, I need one help in SSIS.

I have a source file with column1, I want to split the column1 data into
multiple columns when there is a semicolon(';') and there is no specific
length between each semicolon,let say..


And at destination we have 4 columns let say D1,D2,D3,D4
I want to map
John -> D1

Please I need it ASAP

Thanks in Advance,




12 Answers Found


Answer 1

Hi Swapnil,

In that case what you would need to do is have a condition check before using the expression I provided earlier.

it would be like(I have not tried it):

(FINDSTRING([col1],"-",1)!0 ?SUBSTRING([Col1],1,(FINDSTRING([Col1],"-",1 )-1)) : SUBSTRING([Col1],1,(FINDSTRING([Col1],".",1 )-1)) )

Like wise for the 2nd column you would have to modify the expression.


Answer 2

Have you tried using the regular Flat file  Connection Manager and Flat File Source? Is there anything else special about this source  file?

Answer 3

This file  is a normal flat  file and I'm using flat file connection manger.

I know we can do this with SSIS derived column  by writing expression, But I need expression. I stucked at this point.

I need Expression in derived column.




Answer 4

Are there other columns  as well in this file?

for the 1st name use:

SUBSTRING([Col1],1,(FINDSTRING([Col1],";",1 )-1))

For 2nd column:

SUBSTRING(@[User::str],(FINDSTRING( @[User::str],";",1)+1),(FINDSTRING( @[User::str],";",2 ))- (FINDSTRING( @[User::str],";",1 )+1))

similarly for the 3 rd column  and fourth.


Answer 5

Thanks Raj for quick reply.

Sorry about the Confusion, Actually file  looks like..

Col1:    Col2:

1          john;Greg;David;Sam

2         tom;tony

3         harry

So how we could handle this type of scenario..





Answer 6

In this case you would first have to check if the there are any semi colons only then use the above expression.


FINDSTRING([Col1],";",1)>0?SUBSTRING([Col1],1,(FINDSTRING([Col1],";",1 )-1)): [Col1]

This is for the 1st column, for the 2nd column  the expression would be more complex. i would suggest you use script component here as it will make the coding simple and easy to understand. Just use the split  function and then assign the values to the output columns.


Answer 7

Thanks again!

But I'm very weak in Vb.net scripting, Please can you send me the code to handle this.




Answer 8

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.IO

Public Class ScriptMain
 Inherits UserComponent

 Private textReader As StreamReader
 Private exportedAddressFile As String

 Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

  Dim connMgr As IDTSConnectionManager90 = _
  exportedAddressFile = _
   CType(connMgr.AcquireConnection(Nothing), String)

 End Sub

 Public Overrides Sub PreExecute()
  textReader = New StreamReader(exportedAddressFile)
 End Sub

 Public Overrides Sub CreateNewOutputRows()

  Dim nextLine As String
  Dim columns  As String()
  Dim cols As String()

  Dim delimiters As Char()
  delimiters = ",".ToCharArray

  nextLine = textReader.ReadLine
  Do While nextLine IsNot Nothing
   columns = nextLine.Split(delimiters)
   With Output0Buffer
    cols = columns(1).Split(";".ToCharArray)
    .ID = Convert.ToInt32(columns(0))
    If cols.GetUpperBound(0) >= 0 Then
     .Col1 = cols(0)
    End If
    If cols.GetUpperBound(0) >= 1 Then
     .Col2 = cols(1)
    End If
    If cols.GetUpperBound(0) >= 2 Then
     .Col3 = cols(2)
    End If
    If cols.GetUpperBound(0) >= 3 Then
     .Col4 = cols(3)
    End If
   End With
   nextLine = textReader.ReadLine

 End Sub

 Public Overrides Sub PostExecute()

 End Sub

End Class

Put this code in ur script component. Before that add 5 columns to the script component output and name them as ID, col1, co2..,col4. ID is of data  type int. Create a flat  file destination  and name it as connection and point it to the flat file  as the source.

Im not sure whats the delimiter in ur flat file between the 2 columns. I have use a comma change it accordingly.


This is the output I get:

IDCol1 Col2Col3 Col4

1john GregDavid Sam

2tom tonyNULL NULL


Sudeep's Domain

Answer 9

Thanks again Raj!

Your code worked for my scenario.

 Appreciate your help.





Answer 10


Just for curious I'm asking

what this +1 and -1 indicates in the below substring expression ,please can you explain

SUBSTRING([Col1],1,(FINDSTRING([Col1],";",1 )-1))

For 2nd column:

SUBSTRING(@[User::str],(FINDSTRING( @[User::str],";",1)+1),(FINDSTRING( @[User::str],";",2 ))- (FINDSTRING( @[User::str],";",1 )+1))




Answer 11

When you get use the find string operation it returns the position of the semicolon. 

But we need to fetch the data  between the 2 semicolons hence the + & - ones.


Answer 12

Hi Raj, I need one help  in SSIS.

I have a Excel file  with column1, I want to split  the column1 data  into
two columns  when there is a dash('-') or dot('.'). Substring before '-' or '.' should be new column  as output1 and Substring after '-' or '.' should be another column as output2.





And we need to split this data into 2 columns let  say D1,D2
I want to map
D1            D2

111        ABC

222        DEF

333        PQR

444       XYZ

Please I need it ASAP

Thanks in Advance,



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter