Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

SSIS scenarios...
Browse by Tags · View All
MSBI 30
SSIS 27
BRH 15
SQL Server 15
#BI 14
Script Component 9
#SQLSERVER 8
#SQL Server 7
Flat File Source 5
Script Component Source 5

Archive · View All
August 2010 4
March 2011 3
September 2010 3
February 2013 2
June 2011 2
November 2010 2
May 2010 2
November 2009 2
March 2010 2
May 2012 1

SSIS - Script Component, Split single row to multiple rows

Nov 24 2010 6:41AM by Sudeep Raj   

Coming back to Script Component.

Scenario:

We have a text file and we need to read each row and the output would have multiple rows per row in the input file.

Input File:

"201001","1;3"
"201002","1;2;3;4"
Expected output:
2010-01-01	01
2010-01-03	03
2010-02-01	01
2010-02-02	02
2010-02-03	03
2010-02-04	04

Solution:

Check this post to see how to setup the Script Component.

Code to be used-

C#

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

//Add this Namespace for IO Operations
using System.IO;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    //StramReader to read the input file stream
    private StreamReader textReader;

    //String to save the source file path
    private string srcFilePath;

    public override void PreExecute()
    {
        base.PreExecute();
        srcFilePath = @"I:\SSIS\BLOGS\One2Many\Input\one2many.txt";
        //Set the textReader at the PreExecute Phase so that we donot initialize it for each record.
        textReader = new StreamReader(srcFilePath);
    }

    public override void CreateNewOutputRows()
    {
        string nextLine;
        string[] columns;
        string[] col2Split;
        string outputCol1;
        char[] colDelimiters;
        char[] rowDelimiters;
        colDelimiters = ",".ToCharArray();
        rowDelimiters = ";".ToCharArray();

        //Read next line from the file to the string variable
        nextLine = textReader.ReadLine();

        //Read the file till nextLine variable is not NULL ie. EOF
        while (nextLine != null && nextLine.Length>0)
        {
            //Split the records by commas to later extract the data
            columns = nextLine.Split(colDelimiters);

            //split the 2nd part of the string based on semi colun after removing the double quotes
            col2Split = columns[1].Substring(1,columns[1].Length-2).Split(rowDelimiters);

            //Format the column one as per the requirement
            outputCol1 = columns[0].Substring(1, 4) + "-" + columns[0].Substring(5, 2) + "-";

            foreach (string str in col2Split)
            {                
                string outputCol2 = "0"+str;

                //Add output
                this.OutputBuffer.AddRow();

                //Add the column values for the row added above.
                this.OutputBuffer.Col1 = outputCol1+ outputCol2;

                //Left pad the 2nd coutput column with 0.
                this.OutputBuffer.Col2 = outputCol2.Substring(outputCol2.Length-2);
            }

            //Read the next line
            nextLine = textReader.ReadLine();
        }
    }

    public override void PostExecute()
    {
        base.PostExecute();

        //Close the Text reader once the file has been read in the PostExecute Phase.
        textReader.Close();
    }
}

VB.NET

using System;
Imports System.Data
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

'Add this Namespace for IO Operations
Imports System.IO

Public Class ScriptMain
	Inherits UserComponent
	'StramReader to read the input file stream
	Private textReader As StreamReader

	'String to save the source file path
	Private srcFilePath As String

	Public Overrides Sub PreExecute()
		MyBase.PreExecute()
		srcFilePath = "I:\SSIS\BLOGS\One2Many\Input\one2many.txt"
		'Set the textReader at the PreExecute Phase so that we donot initialize it for each record.
		textReader = New StreamReader(srcFilePath)
	End Sub

	Public Overrides Sub CreateNewOutputRows()
		Dim nextLine As String
		Dim columns As String()
		Dim col2Split As String()
		Dim outputCol1 As String
		Dim colDelimiters As Char()
		Dim rowDelimiters As Char()
		colDelimiters = ",".ToCharArray()
		rowDelimiters = ";".ToCharArray()

		'Read next line from the file to the string variable
		nextLine = textReader.ReadLine()

		'Read the file till nextLine variable is not NULL ie. EOF
		While nextLine IsNot Nothing AndAlso nextLine.Length > 0
			'Split the records by commas to later extract the data
			columns = nextLine.Split(colDelimiters)

			'split the 2nd part of the string based on semi colun after removing the double quotes
			col2Split = columns(1).Substring(1, columns(1).Length - 2).Split(rowDelimiters)

			'Format the column one as per the requirement
			outputCol1 = columns(0).Substring(1, 4) & "-" & columns(0).Substring(5, 2) & "-"

			For Each str As String In col2Split
				Dim outputCol2 As String = "0" & str

				'Add output
				Me.OutputBuffer.AddRow()

				'Add the column values for the row added above.
				Me.OutputBuffer.Col1 = outputCol1 & outputCol2

				'Left pad the 2nd coutput column with 0.
				Me.OutputBuffer.Col2 = outputCol2.Substring(outputCol2.Length - 2)
			Next

			'Read the next line
			nextLine = textReader.ReadLine()
		End While
	End Sub

	Public Overrides Sub PostExecute()
		MyBase.PostExecute()

		'Close the Text reader once the file has been read in the PostExecute Phase.
		textReader.Close()
	End Sub
End Class

Once you set up the package and execute it you get the following output.

I have uploaded the package here for reference.

Tags: SSIS, Script Component, SQL Server, Substring, MSBI, #BI, BRH, Script Component Source, Flat File Source, Pad string, text reader, split row, #SQL Server,


Sudeep Raj
12 · 13% · 4303
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

2  Comments  

  • I have done something similar before where there was a multiple valued column in a table, so in order to normalise it, and report on it I had to create a mapping table. I did it using a cursor though in SQL. I had a guid column followed by a ; seperated column which started with a record count such as 3;guid1;guid2;guid3 Something like this: create procedure dbo.SP_MakeMappingTable (
    @sSrcTable varchar(100), @sDstTable varchar(100), @sGuidField varchar(100), @sSplitField varchar(100) ) as begin

    declare @sTmp		varchar(4000),
    		@sNewGuid	varchar(40), 
    		@nCnt		int,
    		@sSplitString varchar(4000),
    		@sSql		nvarchar(4000)
    
    
    if (select OBJECTPROPERTY(OBJECT_ID(N'dbo.'+@sDstTable), N'IsUserTable')) = 1
    	exec('drop table dbo.'+@sDstTable)
    
    exec('create table dbo.'+@sDstTable+' ('+@sGuidField+' varchar(40), '+@sSplitField+' varchar(40))')
    
    
    create table #tmp (TMP_GUID varchar(40), TMP_BASKET varchar(4000))
    
    set @sSql = 'select '+@sGuidField+', '+@sSplitField+' from '+@sSrcTable
    insert #tmp exec sp_executesql @sSql
    
    declare @sGuid VARCHAR(40), @sBasket varchar(4000)
    
    declare csr cursor for 
    	select TMP_GUID, TMP_BASKET from #tmp
    
    open csr
    fetch next from csr into @sGuid, @sBasket
    
    while @@fetch_status = 0
    begin
    
    	set @sTmp = @sBasket
    	set @nCnt = 1
    	while 1=1
    	begin
    		if (@nCnt!=1)
    		begin
    			set @sNewGuid = substring(@sTmp, 0, charindex(';',@sTmp))
    			exec('insert into '+ @sDstTable +' ('+@sGuidField+', '+@sSplitField+') values ('''+@sGuid+''', '''+@sNewGuid+''')')
    		end
    
    		set @sTmp = substring(@sTmp,charindex(';',@sTmp)+1,4000)
    		set @nCnt = @nCnt +1
    		if charindex(';',@sTmp) < 1
    			break
    	end
    
    	set @sNewGuid = substring(@sTmp, 0, 4000)
    	exec('insert into '+ @sDstTable +' ('+@sGuidField+', '+@sSplitField+') values ('''+@sGuid+''', '''+@sNewGuid+''')')
    
    	fetch next from csr into @sGuid, @sBasket
    end
    
    close csr
    deallocate csr
    

    end

    Messy I know, but effective at the time :)

    commented on Mar 10 2011 9:58AM
    denzilwhite
    1773 · 0% · 11
  • very good

    commented on Jul 25 2011 8:00PM
    maqhost.com
    1312 · 0% · 18

Your Comment


Sign Up or Login to post a comment.

"SSIS - Script Component, Split single row to multiple rows" rated 5 out of 5 by 1 readers
SSIS - Script Component, Split single row to multiple rows , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]