none
The collection of variables locked for read and write access is not available outside of PostExecute

    Question

  • Can someone plz provide some assistance to me on this one?
    I know its an infamous error and if you GOOGLE the issue there are tons of information on the topic and for the most part I understand the proposed resolutions.

    But I haven't been able to find any code that provides an example of how to resolve the issue in C#. Everyone seems to be coding in VB in their responses. :-(

    I did manage to find an article from MS for what I am trying to accomplish but I believe I am missing something in the translation.
    http://msdn.microsoft.com/en-us/library/ms136033.aspx

    The code provided by MS doesn't appear to be applicable for the SSIS 2008 version of the Scripting Component and I've tried to translate it to my environment for several hours now without resolve.

    Here is my code but if you look at the code MS has provided in their article, the structure is nothing to be compared to what I have in SSIS 2008.

    /*Microsoft SQL Server Integration Services Script Task Write scripts using Microsoft Visual C# 2008. The ScriptMain is the entry point class of the script.*/
    
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Dts.Runtime;
    using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
    using Microsoft.SqlServer.Dts.Runtime.Wrapper;
    using Microsoft.SqlServer.Dts;
    
    namespace ST_fffe29a909ee43f3a0ab538bcd17afcb.csproj
    
    {
    
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    
    public class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    
    {
    
    #region VSTA generated code
    enum ScriptResults
    
    {
    
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
    
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    
    };
    
    #endregion
    
    /*
    
    The execution engine calls this method when the task executes.
    
    To access the object model, use the Dts property. Connections, variables, events,
    
    and logging features are available as members of the Dts property as shown in the following examples.
    
    To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    
    To post a log entry, call Dts.Log("This is my log text", 999, null);
    
    To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);
    
    To use the connections collection use something like the following:
    
    ConnectionManager cm = Dts.Connections.Add("OLEDB");
    
    cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";
    
    Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    
    
    To open Help, press F1.
    
    */
    
    public void Main()
    
    {
    
    // TODO: Add your code here
    
    string dtsLogServer = "";
    string dtsLogTable = "";
    string dtsDatabase = "";
    string key = "";
    string strCmd = "";
    string strConnection = "";
    string startDate = "";
    string startTime = "";
    string eventAction = "";
    string perio = "";
    string eventDOW = "";
    
    eventAction = "BULK Extraction";
    
    key = Authentication.getKey();
    this.Dts.VariableDispenser.LockForRead("User::PERIO");
    this.Dts.VariableDispenser.LockForRead("User::EVENTDOW");
    this.Dts.VariableDispenser.LockForRead("User::CURRENTDATE");
    this.Dts.VariableDispenser.LockForRead("User::CURRENTTIME");
    this.Dts.VariableDispenser.LockForRead("User::LOGSERVER");
    this.Dts.VariableDispenser.LockForRead("User::EVENTTABLE");
    this.Dts.VariableDispenser.LockForRead("User::EVENTDATABASE");
    
    perio = Dts.Variables["User::PERIO"].Value.ToString();
    eventDOW = Dts.Variables["User::EVENTDOW"].Value.ToString();
    startDate = Dts.Variables["User::CURRENTDATE"].Value.ToString();
    startTime = Dts.Variables["User::CURRENTTIME"].Value.ToString();
    dtsLogServer = Dts.Variables["User::LOGSERVER"].Value.ToString();
    dtsLogTable = Dts.Variables["User::EVENTTABLE"].Value.ToString();
    dtsDatabase = Dts.Variables["User::EVENTDATABASE"].Value.ToString();
    
    strCmd = "INSERT INTO " + dtsLogTable + "(event_start_date, event_action, " + "event_start_time, extraction_perio, event_dow) VALUES ('" + startDate + "','" + eventAction + "','" +
    startTime + "','" + perio + "','" + eventDOW + "')";
    
    strConnection = @"Data Source=" + dtsLogServer + ";Initial Catalog=" + dtsDatabase + ";User Id=asadmin;Password=" + key + ";";
    
    SqlConnection sqlConn = new SqlConnection(strConnection);
    SqlCommand sqlCmd = new SqlCommand(strCmd, sqlConn);
    sqlConn.Open();
    sqlCmd.ExecuteNonQuery();
    sqlConn.Close();
    
    Dts.TaskResult = (int)ScriptResults.Success;
    
    }
    
    }
    
    }
    

    All I really want to do is to read the variable content and update my database, I don't want to update the variable or anything of the sort. I guess my real question is "How would I go about creating the required "PostExecute" method within my code so I can make this work???

    Thanks in advance!!!

    Monday, January 11, 2010 6:43 PM

Answers

  • There's a disconnect here - you're referring to the "script component", but the code you've supplied is from a Script Task.  They are different - and the methods of accessing the variables within each are very different as well.  AFAIK, you only get this error in a Script component - in the Data Flow - not in the Control Flow.

    The error means that you're trying to read OR write information to a variable marked as ReadWrite in a script component in a method other than the PostExecute.  Read that carefully.  I caught myself with this error just last week, doing something that I thought was perfectly reasonable.  I had a variable configured as ReadWrite, I was reading the value in the PreExecute, and writing an (updated) value to it in the PostExecute.  That's not legal in SSIS.

    So - is this a Script Task or a Script transform?


    Todd McDermid's Blog
    • Marked as answer by mindscape777 Monday, January 11, 2010 7:58 PM
    Monday, January 11, 2010 7:13 PM

All replies

  • Use this to read the variable:
    Perio=Me.Variables.Perio

    Hope this helps !!
    Sudeep   |    My Blog
    Monday, January 11, 2010 6:49 PM
  • Thanks for replying Sudeep but I am using C# which is part of the reason for my post. There are a dozen examples for proposing resolution in VB.

    Anyway, I believe your code translated to C# would be:

    perio = this.Dts.Variables[

    "User::PERIO"].Value.ToString();

    But my root of my problem is that I can't just read the variable to make the assignment because it throws the error I've posted:
    The collection of variables locked for read and write access is not available outside of PostExecute

    Appreciate the effort though!

    Monday, January 11, 2010 6:56 PM
  • Unfortunately, Sudeep is off the mark on this one (doesn't happen often).

    Unfortunately for you, you're mixing up ways of accessing variables in a Script Task.  There are two ways - one way that the Script Task UI helps you with, and another way that's completely manual, but still works fine.

    The "preferred" way is to use the Script Task UI.  That's where you type (or pick) your seven variables into the ReadOnly line of the Script's properties on the first page of the editor.  I'm thinking you've already done that, which is causing your problem.  Inside the script itself, your commands to LockForRead are completely unnecessary (if you've got the variables in your ReadOnly list).  Putting the variables in the ReadOnly list does that LockForRead (and other stuff) for you automatically.  Asking to lock the variables again could be causing your error.
    Todd McDermid's Blog
    Monday, January 11, 2010 6:58 PM
  • Thanks for the response Todd,

    I think (could be wrong though) that the issue may just be as the error has stated as I've read that this is a "by design" restriction built into the script component API.

    I totally get what you're saying and that being said, I've attempted to execute the script both ways. Within the script Task UI and commented out the LockForRead lines in my code and vica versa, but either way gives the same error that I can't lock the variables outside of the PostExecute method.

    I'm stumped if I don't have to create this method within the script.

    Any other suggestions??? :-)
    Monday, January 11, 2010 7:07 PM
  • There's a disconnect here - you're referring to the "script component", but the code you've supplied is from a Script Task.  They are different - and the methods of accessing the variables within each are very different as well.  AFAIK, you only get this error in a Script component - in the Data Flow - not in the Control Flow.

    The error means that you're trying to read OR write information to a variable marked as ReadWrite in a script component in a method other than the PostExecute.  Read that carefully.  I caught myself with this error just last week, doing something that I thought was perfectly reasonable.  I had a variable configured as ReadWrite, I was reading the value in the PreExecute, and writing an (updated) value to it in the PostExecute.  That's not legal in SSIS.

    So - is this a Script Task or a Script transform?


    Todd McDermid's Blog
    • Marked as answer by mindscape777 Monday, January 11, 2010 7:58 PM
    Monday, January 11, 2010 7:13 PM
  • Ditto!

    Just as I was reading your last response I also noted that I am indeed using a Script Component as well as a Script Task. It didn't occur to me until a few seconds ago going through my package that there is a distinct difference between the 2.

    So the code I provided is as you have already pointed out is the Script Task and after further isolation of the issue, it appears to be the script component that is causing my problem. When I disable the Script Component the Script Task executes without issue.


    So it appears that my Script Component was the culprit throwing the errors within the CreateNewOutputRows method.

    After giving serious thought of your last response and moving the appropriate portion of the code to the PostExecute in the "Script Component" the issue has apparently resolved.

    I've learned something new today. :-)

    Now I need to read up on the significant differences between the Script Component and Script Task. I feel a bit silly that I didn't recognize these as being 2 distinctly different objects. I guess I just took it for granted since I don't use them that often.

    Thanks much for your help on getting this issue resolved. I really appreciate it Todd!

    Monday, January 11, 2010 7:56 PM
  • Unfortunately, Sudeep is off the mark on this one (doesn't happen often).

    Hi Todd,
    Just a clarification, whats wrong here?
    In a script component Post Execute Method I just tried this code and it worked for me:

    Dim perio As String
            perio = Me.Variables.PackageNm
    msgbox(perio)

    Where PackageNm is a user variable.
    and I get the desired value

    Hope this helps !!
    Sudeep   |    My Blog
    Monday, January 11, 2010 8:06 PM
  • Just that he'd posted code for a Script Task, and your code wouldn't work in there.
    Todd McDermid's Blog
    Monday, January 11, 2010 8:45 PM
  • But the description that he gave was for Script Component and not Script task hence the confusion :D
    Hope this helps !!
    Sudeep   |    My Blog
    Monday, January 11, 2010 8:47 PM