THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Lock variables in Script Task using C# [SSIS]

If you have ever written code to lock variables inside a SSIS Script Task or Script Component then you will probably have written some VB.Net code that looks something like this:



Public Sub Main()
Dim vars As Variables
Dim fireAgain As Boolean = True
Dts.VariableDispenser.LockOneForRead("VarName", vars)
'Do something with the value....
vars.Unlock()
Dts.TaskResult = ScriptResults.Success
End Sub

If you have then moved to SSIS2008 then you may want to do similar in C# so you would convert this code to be something like:


public void Main() {
    Variables
vars;
    bool
fireAgain = true;
    Dts
.VariableDispenser.LockOneForRead("varName", ref vars);
    //Do something with the value...

    vars
.Unlock();
    Dts.TaskResult = (int)ScriptResults.Success;
}

However, doing so will result in an error:

Use of unassigned local variable 'vars'

What gives? Are you telling me the same logic doesn't work in C#? Actually the solution is pretty simple. Due to the way that C# and VB initialise their variables differently you simply have to set your variable to NULL as follows:

 


public void Main() {
    Variables
vars = null ;
    bool
fireAgain = true;
    Dts
.VariableDispenser.LockOneForRead("varName", ref vars);
    //Do something with the value...

    vars
.Unlock();
    Dts.TaskResult = (int)ScriptResults.Success;
}

Easy when you know how!!!

@Jamiet

Published Thursday, July 29, 2010 2:09 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Todd McDermid said:

Using the "manual" Lock methods can have its place in specific situations - but I'd caution people against using it.  Not you, Jamie - of course - but I regularly see people working with those methods when they have no reason to do so... mostly because guys like us have figured out how to do it to get around locking issues with the "automatic" way.

Instead of doing all that, for almost all situations, I'd recommend just sticking with the ReadOnly and ReadWrite variable lists in the Script editor, and let SSIS do all the heavy lifting.  I think you agree...

July 29, 2010 11:06 AM
 

jamiet said:

Hiya Todd,

I generally avoid using ReadOnlyVariables/ReadWriteVariables. I've had a lot of issues in the past with variables getting locked and hence blocking other tasks from getting a required locking on them - worst case all hell breaks loose and the package errors out.

We solved that by eliminating the use of ReadOnlyVariables/ReadWriteVariables and instead doing all the locking in code because a lock using code keeps that lock for much much less time than ReadOnlyVariables/ReadWriteVariables do - hence since that day I have religiously avoided ReadOnlyVariables/ReadWriteVariables.

Others' mileage may vary.

-Jamie

July 29, 2010 11:48 AM
 

Josef said:

So the boolean variable fireAgain is used for what purpose ?

July 3, 2013 8:48 PM
 

Matt Simpson said:

Any code snippet or suggested changes for SSIS 2012?

April 9, 2014 6:41 AM
 

Fix C# Use Of Unassigned Local Variable If Statement Windows XP, Vista, 7, 8 [Solved] said:

November 23, 2014 8:34 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement