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 8 (of 8 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: 247180 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?

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: 247180 More actions · Answer 6

Phil Parkin

SSC Guru

Points: 247180

February 4, 2010 at 9:43 am

#1113829

Sounds like a good solution to me.

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