Read/Write for Variables

Hi,

I am using a Script Component and I have a Read/Write Variable varStatusCase (as assigned in the Custom Properties of my Script Component). I used this inside my script to get a specific value. However, when I ran it I get this error:

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

How do I repair this?

Thanks,

cherriesh

If you want to write on a variable you have to use the post execute method something like this

Public Overrides Sub PostExecute()

Me.Variables.varName = Something

End Sub

I dont know if there's another way to do it but this is the only one i've found. And it works.

|||

But just know that this doesn't execute for every row.

|||

Check if this post help you!

http://pedrocgd.blogspot.com/2007/05/ssis-populating-dimension_28.html

Regards!

|||

hi,

I tried doing what is in your link:

Private NextKey As Int32 = 0

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim MaxKey As Integer = Me.Variables.varStatus

NextKey = MaxKey + 1

Row.oStatusCode = NextKey

MyBase.PreExecute()

End Sub

Public Overrides Sub PreExecute()

Me.VariableDispenser.LockForRead("User::varStatus")

MyBase.PreExecute()

End Sub

Public Overrides Sub PostExecute()

Me.ReadWriteVariables("varStatus").Value = NextKey

MyBase.PostExecute()

End Sub

My variable "varStatus" is assigned in Script Component ReadWrite Variable.

Am I missing anything? I'm still having the same error.

cherriesh

|||

dear friend,

copy my statment in my post... check the link... the solution is there.

check the pre-execute method

helped?

Read All Variables in a Script Task w/o Enumerating Them

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

No, because there is no way of discovering what variables are available at run-time, and the locking mechanism to get access to the variable and it's value requires a name.

This type of funtionality is only available through the design-time UI interfaces.

|||You might be able to use the OnVariableValueChanged event to capture the variable values as they are set. You do need to make sure that RaiseChangedEvent is true for each variable.|||

Thanks, but the reason for this was to help me understand the details of how the configuration was working. Will OnVariableValueChanged work even if the change was due to loading a configuration?

|||

No, actually it doesn't. I just tested it, and it appears that the event doesn't fire on configurations. Maybe someone from MSFT could comment on why it doesn't? I can guess a few reasons, but I think there are some valid scenarios where you would want to capture values even when they are set from configurations.

Back to your original question, they are adding a Watson-style debugger in 2008 that will dump the current state of the package on demand. That will include the variable values. Not a big help today, but something to look forward to.

|||

Actually, the 2008 version will help once released. Any info on which CTP that feature will be in?

|||CTP 5, from what MSFT said at the PASS Summit.

Read All Variables in a Script Task w/o Enumerating Them

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

No, because there is no way of discovering what variables are available at run-time, and the locking mechanism to get access to the variable and it's value requires a name.

This type of funtionality is only available through the design-time UI interfaces.

|||You might be able to use the OnVariableValueChanged event to capture the variable values as they are set. You do need to make sure that RaiseChangedEvent is true for each variable.|||

Thanks, but the reason for this was to help me understand the details of how the configuration was working. Will OnVariableValueChanged work even if the change was due to loading a configuration?

|||

No, actually it doesn't. I just tested it, and it appears that the event doesn't fire on configurations. Maybe someone from MSFT could comment on why it doesn't? I can guess a few reasons, but I think there are some valid scenarios where you would want to capture values even when they are set from configurations.

Back to your original question, they are adding a Watson-style debugger in 2008 that will dump the current state of the package on demand. That will include the variable values. Not a big help today, but something to look forward to.

|||

Actually, the 2008 version will help once released. Any info on which CTP that feature will be in?

|||CTP 5, from what MSFT said at the PASS Summit.

Read All Variables in a Script Task w/o Enumerating Them

I'm having trouble with my configurations. All of my configurations are targeting variables and the various properties are all set to expressions containing the targeted variables. I would like (during debugging) to dump out all of the variables.

Is there a way to access all variables from a script task? It looks like you have to enumerate the variables you want in the ReadOnlyVariables or ReadWriteVariables properties. I'd rather not miss one by forgetting to list it.

No, because there is no way of discovering what variables are available at run-time, and the locking mechanism to get access to the variable and it's value requires a name.

This type of funtionality is only available through the design-time UI interfaces.

|||You might be able to use the OnVariableValueChanged event to capture the variable values as they are set. You do need to make sure that RaiseChangedEvent is true for each variable.|||

Thanks, but the reason for this was to help me understand the details of how the configuration was working. Will OnVariableValueChanged work even if the change was due to loading a configuration?

|||

No, actually it doesn't. I just tested it, and it appears that the event doesn't fire on configurations. Maybe someone from MSFT could comment on why it doesn't? I can guess a few reasons, but I think there are some valid scenarios where you would want to capture values even when they are set from configurations.

Back to your original question, they are adding a Watson-style debugger in 2008 that will dump the current state of the package on demand. That will include the variable values. Not a big help today, but something to look forward to.

|||

Actually, the 2008 version will help once released. Any info on which CTP that feature will be in?

|||CTP 5, from what MSFT said at the PASS Summit.

raw file destination and environment variables

when using a raw file destination it would be nice to be able to use an environment variable for the filename property.

like

%my_extract%\data.txt

instead of

c:\my_extract\data.txt

Could you turn your screen round a bit... ...no, sorry, still cannot see it from here. Could you describe which OK button it is, any errors you may have when clicking, and perhaps briefly what lead up to this point.

|||

You could use the script task to define a SSIS variable, storing the file name, built using the environment variable. Then you can use this variable in Raw adapters by using the File Name from Variable access mode.

HTH.

|||

have you actually tried that?

|||

Now, I have. It worked fine for me.

Have you had problems with it?

|||How did you do it, there isn't an expressions setting for raw sources/destinations. What do you do to get the filename to be based on a variable?|||Raw file adapters have an AccessMode property, which allows you to enter a filename or a variable name. Variables can obviously use expressions.|||Doh, looked everywhere for that and ti was right under my nose.|||

not yet. I will give it a try today.

you should post some code and a sample usage.

|||

There is the AccessMode property on the raw adapters; change it to "File name from variable" and set your variable to the FileNameVariable property.

Thanks.

|||

Here is how I did it:

- Define RawFileName variable of type string on the package level

- Add the Script Task and set its ReadWriteVariables property to "RawFileName" and the script like this:

Public Sub Main()

Dts.Variables("RawFileName").Value = System.Environment.GetEnvironmentVariable("<your_env_var>") + "\<your_file_name>"

Dts.TaskResult = Dts.Results.Success

End Sub

- Add the Data Flow Task after the Script Task, define your data flow with the Raw File Destination and set its file name to come from the RawFileName variable, as explained in the previous messages.

HTH.

|||

not working for me.

so you actually ran it. it worked? seriously you actually ran it and it created the file you specified?

I create the variable at the package level.

I create the script task and stick the code in there.

I go to my advanced editor for raw file destination.

select accessmode: file name from variable

filenamevariable: user::rawfilename

it keeps telling me error at dataflow task (raw file destination[23]: the file name is not properly specified. supply the path and name to the raw file either directly in the file name property or by specifying a variable in the filenamevariable property.

what gives?

|||

Have you investigated why it is not working? If the file name is invalid, is the variable getting set correctly? Some ideas-

Set a breakpoint on the PreExecute event of the Data Flow and when broken drag the Rawfilename variable into the Watch window. Examine the value.

Add some breakpoints into the Script Task and examine the values as you step through the code.

|||the gui will not let me click the ok button|||

I can delete my raw file destination out of the data flow and put a break point on the data flow task and look at my variable.

it looks like a file name. correctly formatted in all it's glory.

user::RawFileName c:\\inetpub\\wwwroot\\a.txt

looks like a good file name to me?

why does the gui insist it's not a valid file name?