Script Component–Source Part2 Retain last record

So keeping my focus on SSIS Script Component as Source I show another example. Mostly Script component is needed when source is Flat file and we need to retain the previous record value(s) to be used in the next record(s). Keeping this in mind I present the scenario:

The source file layout

1,1
2,1
3,0
4,1
5,0
6,0
7,1

Requirement: Add a third column to data and the values need to be set base on the existing 2 columns. If Column 2 value 1 set column 3 as Column 1, else set Column 3 value to the Column 1 value of the previous record.

Expected Output:

Col1 Col2 Col3
1 1 1
2 1 2
3 0 2
4 1 4
5 0 4
6 0 4
7 1 7

Output:

READ MORE HERE...

To use Script Component or Derived Column Transformation?? Is the question..

This is an old debate and yet not resolved. To use Script Component or Derived Column Transformation??
Yes Derived Column Transformation can NOT do all that can be done in Script Component.
Advantages of Script Component:


Read more..

Script Component–Source Part1

One of the most advance feature of SSIS is Script design capability provided be it Script Task in Control Flow or Script Component in Data Flow Task. I have seen people use the Script task pretty often but avoid Script Component because of various reasons.  Earlier I had shown how to set up user variable in Script component. Today I will demonstrate how to use Script Component as a Source for the below flat file:

Name,Rohit
Age,28
Sex,Male
Name,Mohit
Age,20
Sex,Male
Name,Ram
Age,28
Sex,Male
Name,Shyam
Age,20
Sex,Male

Notice that the Column values are coming in new rows and the column header is present in all the all the rows.

While the output should be in the following format:

Name     Age        Sex
Rohit      28          Male
Mohit      20          Male
Ram       28          Male
Shyam   20          Male

READ ON…..

Assigning variables in Script Component

One of the sore points of using Script component is assigning value to variables.
Read Write variables can only be altered in Post Execute phase. Script Component runs the code for each record and this would affect performance adversely. For details check "Coding and Debugging the Script Component".
In case I need to assign the value of system variable "PackageName" to a readWrite user variable "PkgNm"

Here is a sample code to assign value to Read write variable on the post execute phase:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

ImportsMicrosoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

    Inherits UserComponent

    Public Overrides Sub CreateNewOutputRows()

       ' I have not added any code here

    End Sub

    Public Overrides Sub PostExecute()

        Me.Variables.PkgNm = Me.Variables.PackageName

        Msgbox(Me.Variables.PkgNm)

        MyBase.PostExecute()

    End Sub

End Class