Add a Row Number Column with a Script Component Transformation
Use this pattern when the destination cannot generate an identity column on its own (Flat File, Excel, PDW/APS) but you still need a sequential row number per row. The 'SCR - Rownumber' 'ScriptComponentProject' adds a single 'Int32 rownumber' output column, reads a 'User::maxrownumber' read-only variable at 'PreExecute', and increments the counter for every row in 'Input0_ProcessInputRow'. The package wraps that transformation between an Execute SQL task that fetches 'max(rownumber)' from the destination (so reruns continue numbering) and the destination component.
At Biml compile time the script issues 'SELECT name FROM dbo.sysobjects WHERE xtype = ''U'' AND category = 0' against the source database via 'ExternalDataAccess.GetDataTable' and emits one 'Demo_<TableName>' package per user table. Each package selects '*' from the source table, runs it through the rownumber transformation, and writes it to the matching destination table with no column mapping (so source and destination column lists must already align).
Set the 'Source' and 'Destination' OLE DB connection strings, replace the schema-discovery query if you need a narrower table list, and make sure each destination table has a 'rownumber INT' column with a default of 0 (or NULL) so the Execute SQL high-water query returns a valid value on the first run.
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
<Annotation>
File: Script Component Transformation RowNumber.biml
Description: Example of using the Script Component as
a transformation to add a rownumber to the destination.
Note: Example has an OLE DB Destination that supports
an identity column. Use your own Flat File, Excel or
PDW destination that doesn't supports an identity.
</Annotation>
</Annotations>
<!--Package connection managers-->
<Connections>
<OleDbConnection
Name="Source"
ConnectionString="Data Source=.;Initial Catalog=DemoSource;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
</OleDbConnection>
<OleDbConnection
Name="Destination"
ConnectionString="Data Source=.;Initial Catalog=DemoDestination;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
</OleDbConnection>
</Connections>
<ScriptProjects>
<ScriptComponentProject ProjectCoreName="sc_c253bef215bf4d6b85dbe3919c35c167.csproj" Name="SCR - Rownumber">
<AssemblyReferences>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" />
<AssemblyReference AssemblyPath="System.dll" />
<AssemblyReference AssemblyPath="System.AddIn.dll" />
<AssemblyReference AssemblyPath="System.Data.dll" />
<AssemblyReference AssemblyPath="System.Xml.dll" />
</AssemblyReferences>
<ReadOnlyVariables>
<Variable VariableName="maxrownumber" Namespace="User" DataType="Int32"></Variable>
</ReadOnlyVariables>
<Files>
<!-- Left alignment of .Net script to get a neat layout in package-->
<File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
//
// General Information about an assembly is controlled through the following
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("SC_977e21e288ea4faaaa4e6b2ad2cd125d")]
[assembly: AssemblyCopyright("Copyright 2015")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
// Major Version
// Minor Version
// Build Number
// Revision
//
// You can specify all the values or you can default the Revision and Build Numbers
// by using the '*' as shown below:
[assembly: AssemblyVersion("1.0.*")]
</File>
<!-- Replaced greater/less than by > and < -->
<File Path="main.cs">#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
#endregion
/// <summary>
/// Rownumber transformation to create an identity column
/// </summary>
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
int rownumber = 0;
/// <summary>
/// Get max rownumber from variable
/// </summary>
public override void PreExecute()
{
rownumber = this.Variables.maxrownumber;
}
/// <summary>
/// Increase rownumber and fill rownumber column
/// </summary>
/// <param name="Row">The row that is currently passing through the component</param>
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
rownumber++;
Row.rownumber = rownumber;
}
}
</File>
</Files>
<InputBuffer Name="Input0">
<Columns>
</Columns>
</InputBuffer>
<OutputBuffers>
<OutputBuffer Name="Output0">
<Columns>
<Column Name="rownumber" DataType="Int32"></Column>
</Columns>
</OutputBuffer>
</OutputBuffers>
</ScriptComponentProject>
</ScriptProjects>
<Packages>
<!--A query to get all tables from a certain database and loop through that collection-->
<# string sConn = @"Provider=SQLNCLI11.1;Server=.;Initial Catalog=DemoSource;Integrated Security=SSPI;";#>
<# string sSQL = "SELECT name as TableName FROM dbo.sysobjects where xtype = 'U' and category = 0 ORDER BY name";#>
<# DataTable tblAllTables = ExternalDataAccess.GetDataTable(sConn,sSQL);#>
<# foreach (DataRow row in tblAllTables.Rows) { #>
<!--Create a package for each table and use the tablename in the packagename-->
<Package ProtectionLevel="DontSaveSensitive" ConstraintMode="Parallel" AutoCreateConfigurationsType="None" Name="Demo_<#=row["TableName"]#>">
<Variables>
<Variable Name="maxrownumber" DataType="Int32">0</Variable>
</Variables>
<!--The tasks of my control flow: get max rownumber and a data flow task-->
<Tasks>
<!--Execute SQL Task to get max rownumber from destination-->
<ExecuteSQL
Name="SQL - Get max rownumber <#=row["TableName"]#>"
ConnectionName="Destination"
ResultSet="SingleRow">
<DirectInput>SELECT ISNULL(max([rownumber]),0) as maxrownumber FROM <#=row["TableName"]#></DirectInput>
<Results>
<Result Name="0" VariableName="User.maxrownumber" />
</Results>
</ExecuteSQL>
<!--Data Flow Task to fill the destination table-->
<Dataflow Name="DFT - Process <#=row["TableName"]#>">
<!--Connect it to the preceding Execute SQL Task-->
<PrecedenceConstraints>
<Inputs>
<Input OutputPathName="SQL - Get max rownumber <#=row["TableName"]#>.Output"></Input>
</Inputs>
</PrecedenceConstraints>
<Transformations>
<!--My source with dynamic, but ugly * which could be replace by some .NET/SQL code retrieving the columnnames-->
<OleDbSource Name="OLE_SRC - <#=row["TableName"]#>" ConnectionName="Source">
<DirectInput>SELECT * FROM <#=row["TableName"]#></DirectInput>
</OleDbSource>
<ScriptComponentTransformation Name="SCR - Rownumber">
<ScriptComponentProjectReference ScriptComponentProjectName="SCR - Rownumber" />
</ScriptComponentTransformation>
<!--My destination with no column mapping because all source columns exist in destination table-->
<OleDbDestination Name="OLE_DST - <#=row["TableName"]#>" ConnectionName="Destination">
<ExternalTableOutput Table="<#=row["TableName"]#>"></ExternalTableOutput>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
<# } #>
</Packages>
</Biml>
<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.Data.SqlClient"#>
Submitted by Joost van Rossum.