CODEWITHSUNDEEP
c#ssisdataflowscript-taskscript-component
user11034054
user11034054

Reputation: 41

How do you change the value of the a SSIS variable within the package using Script Component in Data Flow

enter image description hereI am downloading a json file and successfully putting it in a table using data flow and script component object.

Within my json file there is a count of numbers row I should import and how many pages it would take to import the data.

e.g. "count": 1925, "current page": 1, "total pages": 2, "results":

I on my url to get the json file, I have to specify which page I'm trying to get. e.g. &page=" + PageNo

I would like to automate my SSIS package to do this. I have managed to assign the user variables to get the count rows imported, count of row to be imported by using the readwrite function in the script component by assign the in the Post Execute area.

I would like to use the For Loop container to Loop the data flow and keep importing the data until the count rows imported are equal to be count of row to be imported.

My problems is when I execute the Control Flow, I keep getting the error

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

How do I get the variables to change so I can use them the for Loop container?

Added code as per Brad's request.

public override void PostExecute()
{
    base.PostExecute();
   
    Variables.RowsImported = i;
    Variables.RowsTobeImported = totalcount;

    if (totalcount > i)
        Variables.PageNoExtra = Variables.PageNoExtra + 1;

    MessageBox.Show(Variables.RowsImported.ToString());
    MessageBox.Show(Variables.RowsTobeImported.ToString());
    //MessageBox.Show(Variables.PageNoExtra.ToString());
}

Upvotes: 1

Views: 3397

Answers (1)

Brad
Brad

Reputation: 3591

Your issue appears to be accessing the variables in the PostExecute, move it outside of PostExecute. I access variables from script tasks like the below code examples. I had an issue accessing it a different way before but I can not remember the issue but I know this solved it and this is how I do it all the time.

To do it this way you have to set the Read/Write variables in your script task property manager like in screen shot to make them accessible this way.

Also another issue could be that you are trying to do something to the variables on PostExecute, since it does not do this till execution is complete this could cause issues (not sure but I have never done any variable work in the PostExecute).

// Read variable from SSIS package in C# script task
string VariableUseInScript = Dts.Variables["User::VariableAccessedFromSSISPackage"].Value.ToString();


// SET variable value in SSIS package to use in other parts of package
Dts.Variables("[SSISVariableValueToSet]").Value = "ValueToSet";

enter image description here

NOTE: In the above screen shot I only have 1 variable in each. You can have multiple in each one, or they can all be in ReadOnly (if you are not updating them) or in ReadWrite if you are updating them.

Upvotes: 1

Related Questions