Add incremental number while inserting records using SSIS

Question

I have a SSIS package in which, two records are coming. I need to insert the records in the table with an extra column (let's say Sequence). If there are two records, Sequence column should have the value 1(for the first record) and 2(for the second record). Again, next time, I'm getting three records, then again sequence starts from 1,2 and 3.

Is there anyway to do this without using script or stored procedure?

Screenshot:

That’s the requirement. If I can use script then it’s easy. But without script or stored procedures, I need to know is there any other approach. — Ranjith Varatharajan, Jan 22 '18 at 13:04
There is no other solution in SSIS (`Script component`, Staging table and `OLEDB Command` which is not recommended at all because it processes row by row) — Yahfoufi, Jan 22 '18 at 13:08
Check this https://stackoverflow.com/questions/48334344/how-can-i-use-the-auto-generated-id-of-a-record-i-added-in-a-subsequent-task for more information you will find helpful information in the comments and answers — Yahfoufi, Jan 22 '18 at 13:10
One last thing is that the most efficient way is to use a script component (from performance and storage perspectives) — Yahfoufi, Jan 22 '18 at 13:13
I don't think that you can use `OLEDB command` in this case, even if it not recommended at all. Only 2 methods (Staging Table and Script Component) or if the source is QSL Table you can use SQL Command with `ROW_NUMBER()` as source to add auto increment field. — Hadi, Jan 22 '18 at 16:25

Hadi · Accepted Answer · 2018-01-22T16:23:23.647

There are 2 methods to achieve this:

(1) why not using a script component?

I think that using script component is more efficient and more controlled, you can write your own logic

In the DataFlow Task Add a Script Component
Add an Output Column of type DT_I4 (ex: NewID)

In the Script Editor use the following Code (i used Visual Basic language)

Imports System  
Imports System.Data  
Imports System.Math  
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper  
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper  

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _  
<CLSCompliant(False)> _  
Public Class ScriptMain  
    Inherits UserComponent 

    Private CurrentID as Integer  = 0


    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)  

        CurrentID += 1

        Row.NewID = CurrentID


    End Sub 

End Class

In the OLEDB Destination,Map the NewID column to the destination identity column

(2) Using Staging Table

As similar to what i mentioned in this answer:

Create a staging table with an identity column
Each time Truncate the Table (this will restart identity), and Insert data into staging table
Insert data from Staging table using a DataFlow Task or an Execute SQL Task

Thanks @Hadi. I'm going with Scripts, business accepts using script component — Ranjith Varatharajan, Jan 27 '18 at 08:13
@RanjithVaradan this is the better choice, it is more performant and less complex than staging table. You are always welcomed :) — Hadi, Jan 27 '18 at 09:46

score 2 · Answer 2 · answered Jan 22 '18 at 09:48

2

You can use a staging table with an IDENTITY(1,1) column, each time you execute the package you have to TRUNCATE the table to reset IDENTITY. So each time it will start from 1

Or you can write your own logic using a Script Component

answered Jan 22 '18 at 09:48

Yahfoufi

2,220
1
22
41

I can't find any documentation, but I don't think that Bulk Insert (Fast Load in SSIS) guarantees row order. To insure row order, be sure to turn off fast load in the destination. You'll have to weigh out the performance impact of this vs. doing something simple like using a script component as shown in another answer – Mark Wojciechowicz Jan 22 '18 at 14:59

score 0 · Answer 3 · answered Jan 22 '18 at 09:17

0

You can achieve this in the Database itself without the need of adding a Logic in the SSIS package. Just add a Column to your Destination table with IDENTITY and it will be automatically incremented. There is No Need to add Some Additional Logic in SSIS

You Can Add the IDENTITY Column (If you don't have one already on the Table) by Just altering your Table

ALTER TABLE YourTable
ADD SeqNo INT IDENTITY(1,1)

IDENTITY(1,1) Mens the Value of SeqNo for the First Record will be 1 and then it will be Incremented by 1 for each record inserted

answered Jan 22 '18 at 09:17

Jayasurya Satheesh

7,826
3
22
39

Thanks @jayasurya, but the thing is, the sequence should be there for particular records only, not all records. and also, if again, I'm insert three records, it should start from 1, not from the previous number – Ranjith Varatharajan Jan 22 '18 at 09:20
So you mean it should reset for each Batch ? – Jayasurya Satheesh Jan 22 '18 at 09:21
Yes @Jayasurya. – Ranjith Varatharajan Jan 22 '18 at 09:22
@JayasuryaSatheesh or imagine a line or record number inside a file. You can't do that in the database or using a Sequence – Panagiotis Kanavos Jan 22 '18 at 09:51
3

You could insert the new records into a separate table with an identity column and then insert that table (after casting the Identity column to int or varchar) into the final table. It ain't pretty but it should work? – ppijnenburg Jan 22 '18 at 10:13

Add incremental number while inserting records using SSIS

3 Answers3

(1) why not using a script component?

(2) Using Staging Table

Linked

Related