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.. Column1: ------- John;Sam;Greg;David And at destination we have 4 columns let say D1,D2,D3,D4 I want to map John -> D1 Sam->D2 Greg->D3 David->D4 Please I need it ASAP Thanks in Advance, RH sql

Have you tried using the regular Flat File Connection Manager and Flat File Source? Is there anything else special about this source file?SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

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. Thanks, RHsql

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.

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.. Thanks, RH sql

In this case you would first have to check if the there are any semi colons only then use the above expression. eg: 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.

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

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 = _ Me.Connections.Connection exportedAddressFile = _ CType(connMgr.AcquireConnection(Nothing), String) End Sub Public Overrides Sub PreExecute() MyBase.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) .AddRow() .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 Loop End Sub Public Overrides Sub PostExecute() MyBase.PostExecute() textReader.Close() 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: ID Col1 Col2 Col3 Col4 1 john Greg David Sam 2 tom tony NULL NULL 3 harry NULL NULL NULL

Thanks again Raj! Your code worked for my scenario. Appreciate your help. Thanks, RHsql

Raj, 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)) Thanks, RH sql

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.

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. Column1: ------- 111-ABC 222.DEF 333.PQR 444-XYZ 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, STThanks, Swapnil

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. Tweet me..