1

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());
}
user11034054
  • 41
  • 8
  • How are you accessing the variables in your script task? I have accessed them a couple different ways (specify them in the script task editor box as read only or read/write) or you can access them directly from c# code without doing this (though at moment I can not find the code how I did this) but if you provide how you are doing it we may be able to help better. – Brad Jan 20 '21 at 14:00
  • I am using the the script component in the data flow task to assign the variable, but they are lost after the data flow is done transferring the data into SQL table. – user11034054 Jan 20 '21 at 20:04

1 Answers1

1

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.

Brad
  • 3,454
  • 3
  • 27
  • 50
  • I searched first how to solve it before posting here, basically, they say that post execute is the best place to change the read write variables and I am checking via message box if they are being changed and they are doing exactly what want them to do. however, I need to use or pass the changed variable value onto the for loop so that the for loop can use the value to determine how many times it need to run the data flow. I hope that makes sense. – user11034054 Jan 20 '21 at 20:34
  • when I run the whole thing as it is, it then giving me that error message "The collection of variables locked for read and write access is not available outside of PostExecute." and I dont know what else to do. so please help. – user11034054 Jan 20 '21 at 20:41
  • 1
    I update variables all the time in SSIS just fine and I do not use PostExecute. Try putting them in main code function (not post execute) to see if that fixes it. – Brad Jan 20 '21 at 20:44
  • I have tried taking out of the Post Execute and my regular code. But I am still getting the same error. – user11034054 Jan 21 '21 at 09:15