ssis 2008, Parameter on Script Component?

Question

I'm working on SSIS 2008.

On the Control Flow of my ssis I have an Execute SQL Task which connects to a Data Flow Task giving the Data Flow Task a value on a variable.

On the Data Flow Task I have a Script Component which would have an sql query and the sql query would work with the variable value.

I want the Script Component to "receive" or "take" the variable as a parameter so I can work with the parameter value.

What I want is something like an OLE DB Source has: If one double clicks on the Ole Db Source and put Sql Command on Data Access mode, you can see the Parameters button, and there you can put the parameter and assign a variable to the parameter.

I have tested the Script Component options, but until now I have not seen a functionality like Ole DB Source has.

How can I do this with a Script Component?

Thanks.

Answer 1

If you're using an OLE DB Source component, you should be able to map parameters just like you do in an Execute SQL Task. Simply use a ? as the variable placeholder.

If you are using an ADO.NET connection, it's more painful as the parameters must be mapped outside of the actual data flow. See this answer for more detail.

If you have a pressing business need for using the Script Component, then by all means use it. Click the ellipses on the ReadOnlyVariable property of the Script Transformation Editor window.

In the resulting window, scroll down and check all the variables you will need access to.

Now in your Script Task you will be able to reference those variables. The syntax should be something like this.ComponentMetadata.Variables.MyVariableName