Confusion regarding using variables in Script Component
-
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/
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply