Yesterday I received a message from one of my readers, asking how to generate an auto incrementing number in SSIS. We have already discussed how to generate auto incrementing numbers using ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(). Click Here if you haven't seen that yet.

SCENARIO
:
Load list of employees and generate an auto incrementing number as EmployeeID for a non identity column.

SOLUTION
:
Before we start, you can ask why do we need to create an auto incrementing column when we can use IDENTITY. The answer for that question, what if, you have a situation where you already have a column with IDENTITY and you need an another column with incrementing numbers. This solution is useful at that time. So, lets not waste anytime and get started.

Step 1
:
To work through this example we need a sample table where we will load our data. So lets create an Employee table as shown below.
CREATE TABLE Employee
(
EmployeeID INT,
EmployeeName VARCHAR(50),
)
GO
Step 2: Let us consider some sample data to be ETL'ed using SSIS to our newly built Employee Table. I have created a CSV file with some random names as EmployeeNames as shown below :

 data

Click Here to download the sample file.

Step 3: We have everything to create an SSIS solution now. Open BIDS to get started. Create a new solution and name it as per your requirement. In our case we will name it "Employee". Click Here, to learn how to create a solution in SSIS. By default, a blank package is created. We shall use the same.

 Step 4: In your empty package, drag and drop the Data Flow Task into the blank package as shown :

 auto1

For better understanding we renamed the data flow task as "DF Employees".

 Step 5: Inside the Data Flow task, add a Flat file source since our data is in the txt format as shown below.

 auto2

Remember that you can use any source with this example. Configure the Flat File source so that it understands where to pull the data from. To understand how to configure see below image.

 auto3

Click the "New" button to create a file connection which opens a new window as shown below.

 auto4

Browse for the location of the file and set it to your file location. By default, the 3rd option in the picture is unchecked. You have to manually check it, to tell SSIS that you have column names in the first row of your source. Click the columns tab(4), to see the list of columns. Step 4 is mandatory. In order to preview the data coming from your source file, you can click the preview tab(5).

Step 6
:
Drag and drop a script component, as transformation (it will give you 3 options when you drag and drop it) as shown below.

 auto5

Create a On success precedence constraint from the flat file source to script component which is nothing but drag the green arrow of Flat file source to script component. Double click and open the script component which will allow you to create an output column / columns from it. In our case we named it EmployeeID. For reference, see below images. First we need to select the input column to pass through the script component without any changes.

 auto6

We need to set up a output column to create our incrementing number as shown below.

 auto7

Using this window, click add column and rename it to EmployeeID. Next, add a connection manager as employee source using the connection manager tab as shown below.

 auto8

After finishing all these steps, lets get back to our "Script" tab (marked as 3) and click "Edit Script". This is where we write a piece of C# code to generate an incrementing number. For reference look at below image.

 auto9

Replace the total script with the script below.
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/
 
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 i;
    public override void PreExecute()
    {
        base.PreExecute();
        i = 0;
    }
 
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        i++;
        Row.EmployeeID = i;
    }
 
}
In this we declare a variable i. Pre Execution we set it to a value of 0. Once it starts processing the input rows, we first incremented the value of i with 1 and after that pass that value for EmployeeID column for that row processed.

Step 7: Now, let us add a OLE DB Destination to load the data as shown below. It will have a red X mark because we haven't configured it yet.

auto10

Connect the Script Task and OLE DB Destination using a precedence constraint. Double click and open the OLE DB Destination task to configure it as shown below.

auto11

Select the database the table is in and also select the table. Map the columns using Mappings tab as shown below.

 auto12

Click OK once done.

Step 8: We are done creating our package and its time to execute the package we have created so far.

 auto13

Step 9
:
Now let us verify our results in SSMS by running the following simple query on our Employee table.
SELECT * FROM Employee

The result will show up with auto generated numbers for each employee name without an identity field as below.

 auto14

See Also :







*** Like Us on Facebook to get instant notifications about new posts ***
*** Please leave your feedback below in the comments section if you have found this post useful ***
*** If you have any questions, please leave a comment *** *** Click on the images to make them bigger in size to view good ***