February 3, 2010 at 2:49 pm
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.
February 3, 2010 at 3:32 pm
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
February 4, 2010 at 6:39 am
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.
February 4, 2010 at 9:26 am
Ok, let me try something and I'll get back to you..
CEWII
February 4, 2010 at 9:34 am
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?
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.
February 4, 2010 at 9:38 am
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.
February 4, 2010 at 9:43 am
Sounds like a good solution to me.
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.
February 4, 2010 at 9:58 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply