Reference Package Level Variables in a Script Component.

I am trying to reference a package level variable in a script component (in the Code) and am unable to do so successfully. I have it listed as a ReadOnlyVariables in the custom properties of the script component, however unable to reference it in the code.

Any help will be appreciated.

Thanks,

Andy.

Type "Me." and let intellisense help you from there.

-Jamie

|||

Jamie,

Thanks for your suggestion. I did that and I was able to locate the variable. What does "ME" refer to? Is it refering to the Script Component? Does typing ME in different procedures/functions within the Script Component refer to different things?

Thanks,

Andy.

|||

Now that I can reference the variable I am getting script component errors.

I am basically using the RowCount Component variable and reading into the script and writing it out to the destination.

I put the RowCount Variable in the ReadWriteVariables property of the Script Component since having it in the ReadOnlyVariables was hanging the application. But when I put it under the ReadWriteVariables, I get the following error.

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

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponent.get_ReadWriteVariables()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.Variables.get_CountID()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.WriteTrailerCount2()

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.ScriptMain.Input0_ProcessInput(Input0Buffer Buffer)

at ScriptComponent_e27beb91ebac4849bc732f8d8577fd5c.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

|||

OK, a couple of things:

1) Are your script and rowcount components in the same data-flow? This is not a good idea because the Rowcount component will not populate the variable until the data-flow is complete. If you want to use that value in the script component then you will have to break the data-flow into 2 and pass the data between the 2 using a raw file.

2) You can't reference variables listed in ReadWriteVariables inside the Input0_ProcessInputRow(Row) method. You can override the PostExecute() method which gets called when all the rows in the pipeline have passed through the component. ReadWriteVariables CAN be accessed in there.

What is it you're trying to achieve here?

-Jamie

|||

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer? I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

3) Is there any documentation on the sequence of execution of methods in the script component?

|||

Andy_1979 wrote:

Jamie,

Firstly, I am very new to SSIS with no prior DTS experience, so please pardon my stupidity while asking questions.

No problem. We're all here to learn right? :)

Andy_1979 wrote:

1) Yes, RowCount and Script component are in the same dataflow. I wasn't aware that the RowCount variable gets populated once the data flow is completed.

Well this may not actually physically be the case but logically that is how you should think of it. There is certainly no guarantee that a variable populated in a component will be readable by another component in the same data-flow. You should never attempt to do this.

Don't think of the components as seperate "things". The lowest unit of execution is a data-flow. The components are just the constituent parts of that and it helps to think of them as of they are actually all executing at the same time

Andy_1979 wrote:

2) By the time I reference my variable in the PostExecute() method isn't it too late since I need to write it out to the output buffer?

Well it depends what you want to do. If you want to write it to the output buffer then you should be able to do this by splitting the data-flow into 2 as I previously suggested.

I'm actually more concerned about why you want to do this. I know I don't know your requirement but do you really need to put the same value into every single row in the pipeline? That strikes me as a strange thing to do.

Andy_1979 wrote:

I was able to programatically count the input rows and write it out. I was trying another way of accomplishing the same thing but instead of me programatically counting, I was wanting the RowCount Component to do that for me and me just grabbing the variable with the count in it and writing it out to the destination. Having the variable as ReadOnly on the script component basically hangs the entire dataflow and having it as ReadWrite causes an error when it reaches the script component.

The reason putting it in ReadOnlyVariables causes it to hang is because you are trying to read from it before the Rowcount component has finished with it. The Rowcount component has locked the variable. I refer you to my earlier point about how a data-flow executes. it is not as simple as just a series of sequential operations even though it looks as though it is.

Andy_1979 wrote:

3) Is there any documentation on the sequence of execution of methods in the script component?

Errr... probably yeah. Its fairly intuitive though. Perhaps this article in BOL will help: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/2a0aae82-39cc-4423-b09a-72d2f61033bd.htm

Why do you want to put this value into the pipeline?

-Jamie

|||

Jamie,

Thank you so much for taking time to explain all of this. The RowCount Variable is written out to destination at the end of processing as a new row. It is not outputted on every single row. I basically want to count the number of input rows coming into my script component, process them and write them out but at the end write out an additional line which has the count of total rows processed.

Andy.

|||

Well it sounds as though the extra row you want to output will most likely have different column metadata to the rest of the pipeline and if that's the case then you won't be able to do this.

But...if you do want to...then here's what you should do.

Use a script component|||This whole variable story is a joke isn't it?|||

orbit wrote:

This whole variable story is a joke isn't it?

That's not a particularly useful statement. What do you mean? Do you have feedback for the product? If so, submit it at Microsoft Conenct.

-Jamie