SSIS SCRIPT TASK Question

Question

SSIS SCRIPT TASK Question

SSIS.COM

SSCommitted

Points: 1735
More actions
August 23, 2010 at 11:34 am

#144776

Hello,
I am using the following code to generate autoincrementing sequence numbers in my ssis task...But I have a problem, Is there a way I can store the last incremented value into a package variable so that for the next iteration the initial value starts from that number.
Eg: I have inserted 10 rows today. So, my values are from 1-10 today. But, if I have to insert 10 rows tomorrow, how do I store that value so that the sequence starts from 11..
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain
Inherits UserComponent
Dim intCounter As Integer = 0 'Set intial value here
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Row.Column = intCounter
intCounter = intCounter + 1 ' Set the incremental value here
End Sub
End Class

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

The way I would do this is to physically store something within a database which the package will use when it runs. Your first step when the package runs is to issue a select statement against your database which retrieves the value and assigns it to a package-scoped variable which can then be used for the remainder of your package, before being written back to the database at the end.

Alternatively, using your specific example, you may just be able to do something like

SELECT MAX(fieldname) + 1 from t1

and use the result of that instead.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

SSIS.COM SSCommitted Points: 1735 More actions · Answer 2

Thanks Phil for ur response....

Here is my problem...I don'y hav emuch knowledge with vb...So I am trying to add the ssis variable value during the initialization of my counter vb varaible...

so intcounter=dts.variables("new").value

But How do I store the last value(i.e. 10 in our example) to the package variable ?

Phil Parkin SSC Guru Points: 246987 More actions · Answer 3

Use an Execute SQL task. The results of the task (eg a single value) can be mapped directly to a variable (I don't have SSIS running at the moment, so can't give you all the nitty gritty).

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Luckily I have SSIS open 🙂

Use the ResultSet property --> Single row

With this you can set the result of a query to a variable. Configure this in the Result Set pane of the Execute SQL Task.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

SSIS.COM SSCommitted Points: 1735 More actions · Answer 5

Thanks it is working with result set....but I had to change my vb script a little though...