How to Increment a Global Variable

Question

Post reply

How to Increment a Global Variable

dndaughtery

SSChampion

Points: 11290
More actions
February 3, 2010 at 2:49 pm

#142146

I have declared a global variable called RecordID. I have added it to a script Component as a read/Write variable but when I try to increment it and assign the value with the following code:
Variables.RecordID = Variables.RecordID + 1;
Row.tmpOldPrimeKey = Variables.RecordID;
I get the following error:
The collection of variables locked for read and write access is not available outside of PostExecute.
I've also tried to do this in the pre execute and posexecute procedures but I can't access my "Row." variables declared in the output. How can I do this? I need to create a distinct ID for each record.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Where are you trying to do this?

It feels like a data-flow script component. Why don't you tell us more about what you are doing before I try to answer.

CEWII

dndaughtery SSChampion Points: 11290 More actions · Answer 2

Yeah its in a script transform in a data flow component. I basically need a way to add a unique record id for each record. I'm open to any way of doing it.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

Ok, let me try something and I'll get back to you..

CEWII

Phil Parkin SSC Guru Points: 246985 More actions · Answer 4

dndaughtery (2/4/2010)
Yeah its in a script transform in a data flow component. I basically need a way to add a unique record id for each record. I'm open to any way of doing it.

Add an IDENTITY column in your destination db?

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

dndaughtery SSChampion Points: 11290 More actions · Answer 5

I need the record ID inside the ssis pkg so that I can send it along with the error records in an error table. I've pretty much decided to send the data into a table with an identity(1,1) column and then create a new flat file with the RecordID in it.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

Sounds like a good solution to me.

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Do you need access to the last value when you are done? If not here is the basic solution:

' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

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

Private _iRecId As Integer = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

_iRecId = _iRecId + 1

Row.RecId = _iRecId

End Sub

End Class

CEWII