August 23, 2010 at 11:34 am
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
August 23, 2010 at 11:45 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 23, 2010 at 11:55 am
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 ?
August 23, 2010 at 12:04 pm
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).
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 24, 2010 at 3:26 am
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
August 24, 2010 at 5:53 am
Thanks it is working with result set....but I had to change my vb script a little though...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply