Use Iteration number in SSIS

Question

I'm loading data from Flat file into the SQL Server Database using SSIS.

While loading I have load to one column called "Iteration". The value of this column should increment for every 100 rows inserted. For eg.,

+----------------+-------+
| Columns        | Iter  |
+----------------+-------+
| First 100 rows | 0     |
+----------------+-------+
| Next 100 rows  | 1     |
+----------------+-------+
| Next 100 rows  | 2     |
+----------------+-------+

I have no idea how to do this as I'm new to SSIS. I tried using counter with a variable. But not working. Is this possible in SSIS?

Answer 1

Add a Variable to your package called IterationSize, integer and initialize it to your 100. If the batch size changes, let's change it in a simple, obvious place.

A script Task, acting a Transformation is what you're looking for. We're going to configure the task so it has read access to an SSIS variable called IterationSize

and in the Inputs Outputs tab, we need to add a new column called Iteration to the Output 0 buffer's Output Columns collection. Default type of DT_I4 is fine

The code itself is tiny. We will create a member variable called currentRowNumber and initialize it to zero. As each row comes into the component, we'll increment by one. Before doing that, we'll assign a value to our new column which is divides the currentRowNumber by our SSIS variable User::IterationSize.

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

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    int currentRowNumber;

    public override void PreExecute()
    {
        base.PreExecute();
        this.currentRowNumber = 0;
    }


    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        // integer division is free truncation. Not testing for iterationsize of zero
        Row.Iteration = this.currentRowNumber / Variables.IterationSize;
        this.currentRowNumber++;
    }

}

For testing, I created a file with a single column that was always the number zero and had 206 rows. I set the iteration size to 3 and put a data viewer into my data flow. You can see the first three rows are iteration 0, next three iteration 1, etc

Biml it

Get the free BimlExpress add-in for Visual Studio and the following Biml should recreate my package exactly. Well, almost. Something happened to the actual code but I'll have to come back and fix it. + file a bug report

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="Flat File Connection Manager" FilePath="C:\ssisdata\Input\so_61794511.txt" FileFormat="Flat File Connection Manager" />
    </Connections>
    <Packages>
        <Package Name="SO_61794511" Language="None" ConstraintMode="LinearOnCompletion">
            <Variables>
                <Variable Name="IterationSize" DataType="Int32" IncludeInDebugDump="Include">7</Variable>
            </Variables>
            <Tasks>
                <Dataflow Name="DFT Ingest file">
                    <Transformations>
                        <FlatFileSource Name="FFS Data" LocaleId="None" RetainNulls="true" FileNameColumnName="" ConnectionName="Flat File Connection Manager" />
                        <ScriptComponentTransformation Name="SCR Segment data" ProjectCoreName="SC_c69eec20ae0540169ee55fafada9521b">
                            <ScriptComponentProjectReference ScriptComponentProjectName="SC_c69eec20ae0540169ee55fafada9521b" />
                        </ScriptComponentTransformation>
                        <DerivedColumns Name="Place holder" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="Flat File Connection Manager" CodePage="1252" TextQualifier="_x003C_none_x003E_" RowDelimiter="">
            <Columns>
                <Column Name="Data" Length="50" DataType="AnsiString" Delimiter="CRLF" MaximumWidth="50" />
            </Columns>
        </FlatFileFormat>
    </FileFormats>
    <ScriptProjects>
        <ScriptComponentProject Name="SC_c69eec20ae0540169ee55fafada9521b" ProjectCoreName="SC_c69eec20ae0540169ee55fafada9521b">
            <ReadOnlyVariables>
                <Variable VariableName="IterationSize" DataType="Int32" Namespace="User" />
            </ReadOnlyVariables>
            <OutputBuffers>
                <OutputBuffer Name="Output 0">
                    <Columns>
                        <Column Name="Iteration" />
                    </Columns>
                </OutputBuffer>
            </OutputBuffers>
            <InputBuffer Name="Input 0" />
        </ScriptComponentProject>
    </ScriptProjects>
</Biml>