Confusion regarding using variables in Script Component
-
I'm migrating data (about blood samples) from an old system and part of the migration involves adding a new identifier to each record.
The identifier will look something like this:- 10/0150
where 10 is the 2 digit year and 0150 identifies the sample as the 150th received that year.
The 0150 element would be left padded with zeroes to form a 4 digit string
I have an input field called dateSampleReceived from which I can identify the year
My idea was to utilise the following logic inside a script component to 'calculate' the new identifier
Identify the 2 digit year
Is Year same as that on last record processed?
if Yes add 1 to the counter
if No reset the year variable to the new identified year and reset the counter to 1
set the output column to year.tostring + "/" + counter.tostring()
I've immediately run into issues regarding the accessibility of variables.
For simplicity sake I just tried to get the code working to increment the counter without worring about a reset of the year
This is the code I have at present:
Public Class ScriptMain
Inherits UserComponent
Dim counter As Int32
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim vars As IDTSVariables90
Me.VariableDispenser.LockOneForRead("SampleCounter", vars)
counter = CType(Me.ReadWriteVariables("SampleCounter").Value, Int32) + 1
vars.Unlock()
Row.SampleCounter = counter
End Sub
Public Overrides Sub PostExecute()
Me.ReadWriteVariables("SampleCounter").Value = counter
End Sub
End Class
Running the code provides the error message:-
"The collection of variables locked for read and write access is not available outside of PostExecute."
I'm now totally confused as I thought doing something like this would be simple.
Can anyone point me in the right direction for the approach I'm taking or alternatievely suggest a better way?
Thanks
Neil
-
Neil,
You have made these two errors:
1. If you specify a variable in ReadOnlyVariables or ReadWriteVariables, you should not use VariableDispenser to manage the variables. The variables are already available in the special Variables class.
2. You should not manipulate the variable during the data flow execution. Just like what the error says, you have to do it in the PostExecute method.
With this in mind , I would implement the script like this:
Public Class ScriptMain
Inherits UserComponent
Dim counter As Int32
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
counter += 1
Row.SampleCounter = counter
End Sub
Public Overrides Sub PreExecute()
counter = MyBase.Variables.SampleCounter
End Sub
Public Overrides Sub PostExecute()
MyBase.Variables.SampleCounter = counter
End Sub
End Class
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi
Your code doesnt work and I still get the same error. You are using:
MyBase.Variables.SampleCounterin the PreExecution and PostExecution sections.
If SampleCounter is a ReadOnly variable, then its fine to be used in the PreExecution bit but then you cant write to it in the PostExecution section.
If SampleCounter is a ReadWrite variable, then you cannot use it in the PreExecution bit and get the following error "The collection of variables locked for read and write access is not available outside of PostExecute".
So the issue im having is that I want to read from a variable value and then increment the value of the variable by 1, but I cant. Your code looks like the perfect solution - but SSIS doesnt allow this to work.
any suggestion?
Shaheen
Public Class ScriptMain
Inherits UserComponent
Dim counter As Int32
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
counter += 1
Row.SampleCounter = counter
End Sub
Public Overrides Sub PreExecute()
counter = MyBase.Variables.SampleCounter
End Sub
Public Overrides Sub PostExecute()
MyBase.Variables.SampleCounter = counter
End Sub
End Class
-
Shaeen,
If the variable cannot be accessed for read in the PreExecute phase, then I would suggest you take the control of the variable value management in your hands. By this mean you have to lock the variable for read/write in PreExecute and release in PostExecute. Try this code:
Public Class ScriptMain
Inherits UserComponent
Dim counter As Int32
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
counter += 1
Row.SampleCounter = counter
End Sub
Public Overrides Sub PreExecute()
Call MyBase.VariableDispenser.LockOneForWrite("SampleCounter", m_vars)
counter = CInt(m_vars("SampleCounter").Value)
End Sub
Public Overrides Sub PostExecute()
m_vars("SampleCounter").Value = counter
Call m_vars.Unlock()
End Sub
Private m_vars As IDTSVariables90
End Class
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Hello,
thanks for your speedy reply! - I forgot to mention, I am programming this in c#, I'm wondering if you know how to do this in C#?
here is my code so far.
Thank you so much for your help already because it looks promising!
Kind regards
Shaheen
/* Microsoft SQL Server Integration Services Script Component* Write scripts using Microsoft Visual C# 2008.
* ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
int Counter;
public override void PreExecute()
{
base.PreExecute();
Counter = Variables.varMaxUIDW;
}
public override void PostExecute()
{
base.PostExecute();
Variables.varMaxUIDW = Counter;
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
Counter += 1;
Row.UIDCount = string.Format("{0:00}", Counter);
}
}
-
Shaheen,
I will leave the translation of the code from VB.NET to C# to you. The code is very similar.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
CozyRoc (2/6/2012)
Shaheen,I will leave the translation of the code from VB.NET to C# to you. The code is very similar.
You may find this [/url]code-translation site useful.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply