1

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:

enter image description here

Improve this question
8
  • 1
    Is there anyway to do this without using script Why?? Commented Jan 22, 2018 at 10:39
  • 1
    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. Commented Jan 22, 2018 at 13:04
  • As i suggested use a staging table Commented Jan 22, 2018 at 13:05
  • 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) Commented Jan 22, 2018 at 13:08
  • Check this stackoverflow.com/questions/48334344/… for more information you will find helpful information in the comments and answers Commented Jan 22, 2018 at 13:10

3 Answers 3

Reset to default
4

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

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

  3. 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

  4. 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:

  1. Create a staging table with an identity column
  2. Each time Truncate the Table (this will restart identity), and Insert data into staging table
  3. Insert data from Staging table using a DataFlow Task or an Execute SQL Task
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks @Hadi. I'm going with Scripts, business accepts using script component
@RanjithVaradan this is the better choice, it is more performant and less complex than staging table. You are always welcomed :)
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

Improve this answer

1 Comment

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
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

Improve this answer

5 Comments

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
So you mean it should reset for each Batch ?
Yes @Jayasurya.
@JayasuryaSatheesh or imagine a line or record number inside a file. You can't do that in the database or using a Sequence
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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.