Hi, I am trying to develop a SSIS package which will read the records from the flat file and insert them into a destination table. I have some validations written in script component. I have declared two Read Write variables with package level scope. when i try to assign a value to the variable in the script component and run the package, the package throws me an error "The collection of variables locked for read and write access is not available outside of PostExecute". What should be done to over come the problem please help me on this regard Thanks Madhavan.M

This is a known limitation. Do the assignment in PostExecute as it suggests.

Ok, how do you do the assignment in PostExecute?

Same as you would in ProcessInput, just set the variable value property.

Using Variables in the Script Component(http://msdn2.microsoft.com/en-us/library/92d1881a-1ef1-43ae-b1ca-48d0536bdbc2.aspx)

I have a solution for this. First of all, don't use a script component to assign a value(s) to a SSIS variable(s), use instead a [Execute SQL Task] in [Control Flow] level.FIRST STEP:-------------------In the [Execute SQL Task] -> [General] tab configure the follow: - 1. In [SQL Statement] option write the SQL Command that will set the value(s) for the SSIS variable(s). Make sure that the command will throw only one row or an error will appear.- 2. Set the [Result Set] option as [Single Row].SECOND STEP:------------------------In the [Execute SQL Task] -> [Result Set] tab map the name of the column name of the SQL Command with a SSIS variable. You can set multiple variables, just don't forget to set an ALIAS for the columns in the SQL Command.Well, I hope this helps you.Regards.Alejandro Mostajo

piruLee wrote: I have a solution for this. First of all, don't use a script component to assign a value(s) to a SSIS variable(s), use instead a [Execute SQL Task] in [Control Flow] level.FIRST STEP:-------------------In the [Execute SQL Task] -> [General] tab configure the follow: - 1. In [SQL Statement] option write the SQL Command that will set the value(s) for the SSIS variable(s). Make sure that the command will throw only one row or an error will appear.- 2. Set the [Result Set] option as [Single Row]. SECOND STEP:------------------------In the [Execute SQL Task] -> [Result Set] tab map the name of the column name of the SQL Command with a SSIS variable. You can set multiple variables, just don't forget to set an ALIAS for the columns in the SQL Command.Well, I hope this helps you.Regards.Alejandro Mostajo I think you may be missing the whole point of this thread, because the solution you're presenting here is unrelated to the original poster's question and the on-topic responses that the various volunteer community members posted. The original problem related to assigning a value to a package variable (which was added to the ReadWriteVariables list) from within the Script Component VB.NET code. This is a pretty common scenario - think of something like the Row Count transformation, which writes the row count for a data flow path back to a package variable for later use. Since variables are the only mechanism by which SSIS tasks can communicate, if you want your script component to share any sort of output with other tasks, this is the approach you need to use. The original poster was running into a "by design" restriction built into the script component API - for performance reasons the API only allows the script code to write to package variables once, in the PostExecute event. This is not a "limitation" because there's no loss of functionality - the developer simply does his "temp work" in VB.NET variables local to the script component. Although your Execute SQL task solution may well solve the problem that you are experiencing, but is not applicable to the original poster's question, which was specific to his attempt to "try to assign a value to the variable in the script component." Am I missing something here, or are we comparing apples to oranges?

MatthewRoche wrote: piruLee wrote: I have a solution for this. First of all, don't use a script component to assign a value(s) to a SSIS variable(s), use instead a [Execute SQL Task] in [Control Flow] level.FIRST STEP:-------------------In the [Execute SQL Task] -> [General] tab configure the follow: - 1. In [SQL Statement] option write the SQL Command that will set the value(s) for the SSIS variable(s). Make sure that the command will throw only one row or an error will appear.- 2. Set the [Result Set] option as [Single Row]. SECOND STEP:------------------------In the [Execute SQL Task] -> [Result Set] tab map the name of the column name of the SQL Command with a SSIS variable. You can set multiple variables, just don't forget to set an ALIAS for the columns in the SQL Command.Well, I hope this helps you.Regards.Alejandro Mostajo I think you may be missing the whole point of this thread, because the solution you're presenting here is unrelated to the original poster's question and the on-topic responses that the various volunteer community members posted. The original problem related to assigning a value to a package variable (which was added to the ReadWriteVariables list) from within the Script Component VB.NET code. This is a pretty common scenario - think of something like the Row Count transformation, which writes the row count for a data flow path back to a package variable for later use. Since variables are the only mechanism by which SSIS tasks can communicate, if you want your script component to share any sort of output with other tasks, this is the approach you need to use. The original poster was running into a "by design" restriction built into the script component API - for performance reasons the API only allows the script code to write to package variables once, in the PostExecute event. This is not a "limitation" because there's no loss of functionality - the developer simply does his "temp work" in VB.NET variables local to the script component. Although your Execute SQL task solution may well solve the problem that you are experiencing, but is not applicable to the original poster's question, which was specific to his attempt to "try to assign a value to the variable in the script component." Am I missing something here, or are we comparing apples to oranges? Well, you are totally right!, thank you for the greatest technical description of the problem. Now, can you give me the solution.

Perhaps I'm missing something obvious, but the solution was presented multiple times, in multiple ways, earlier in this thread - starting with the first response to the original post. If you're developing using the Script Component, you put your code into the various event procedures that are provided in the Visual Studio for Applications editor. One of these event procedures is for the PostExecute event. If you want to write a value from your Script Component code to a package variable so that it can be available in other tasks within the package, you need to perform that write in code that is within the PostExecute event handler. What am I missing here? If this isn't what you need, it would be valuable if you could post a description of what you're trying to accomplish, how you've tried to accomplish it to date, and how it's failed - ideally with complete error messages. This information (and the information posted back in September of 2006) solves the issue presented by the original poster, so if it is not solving your problem then we need more information in order to help.

Thanks a lot Mat,I don't know about others, but the funny thing is that I had BOTH problems I foolishly thought PostExecute meant the OnPostExecute of the event handler! I have once written one handler and used a script task there to write to a read-write variable, and that was in the body of the task. Now, thanks to this misunderstanding, I am one up.I also couldn't find a simple way to change the state of a variable in control flow, now I can do that too.This shows that it never hurts to completely define a problem and a solution. you may end up helping someone who is no as clever.Thanks guys, you are awesome!Reza

Thanks, Reza. Your feedback is very much appreciated. I have found over the years that whenver I'm stuck on a problem thatI cannot seem to solve myself, the best thing for me to do is to explain the problem in as much detail as possible to someone else. 9 times out of 10 that exercise forces me to look at the problem in a different way, and I end up seeing the solution myself. The other one time the other person has the information he needs to help me find a solution. One of thecommonly frustrating things about these forums is that many posters do not provide enough information to allow the volunteers to help them.Remotely troubleshooting other people's problems is difficult enough when you have a good problem description, but often gettingthat description can be difficult in and of itself. For your other ("I also couldn't find a simple way to change the state of a variable in control flow")problem, the simplest solution is to use the Script task. Brian Knight has a blog post that shows two ways to do it: http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx. Using a Script Component in the data flow is overkill, and a lot of work, if you just need to assign a value to a variable. Good luck!

This is an example in a ScriptComponent: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer) Dim vars As IDTSVariables90 Me.VariableDispenser.LockOneForWrite("Find", vars) If (Row.NewCost_IsNull Or Row.NewCost <= 0) Then vars("Find").Value = False Else vars("Find").Value = True End If End SubAnother resource about this:http://www.safnet.com/writing/tech/archives/2008/01/ssis_updating_v.html

the issue is: the obvious way you would think about using variables within a script taskis what causes this failure (this is how i stumbled on this post). you cannot repeatedly write to a variable because that variable gets locked. the right way to do it is to declare a class level variable and assign values into that. then overwritePostExecute method and set package read-write variable inside that method. here is the example: Public Class ScriptMain Inherits UserComponent Dim someCounter As decimal =0 Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)someCounter=someCounter +Row.SomeNumericValue End Sub Public Overrides Sub PostExecute() Variables.TotalPOAmount =someCounter MyBase.PostExecute() End Sub End Class

Thanks for posting this Mr. Andersen :-). You helped me today with exactly this issue. And supid me - I wanted as well make use of the OnPostExecute in some way and was not thinking to override the PostExecute of ScriptComponent - silly me. Cheers, Marc