How to pass a run time parameter in a customized store procedure

[Former Member]

Hi Friend

I have created customized Store Procedure for some logic. Presently i am executing the Store Procedure through the Data Manager Package.

But i need to pass a run time parameter at the time of execution the data manager package. Basically i want pass the Materail Dimension Member at the time of data manager execution .

Looking forward to your views on the same.

thanks

krish

[Former Member · Accepted solution]

You can also just keep your packge the way it is and use MODIFYSCRIPT to change the properties of your task(s) or the value of a variable at run time. You'll obviously have to change the MODIFYSCRIPT to prompt the user for input anyway. I think the best way to do it would be to set a variable to the value of %MATERIAL_SET% and then use the variable in your SSIS package to determine how the stored procedure gets executed.

[Former Member]

Thanks for the solution

[Former Member]

Hi Krish,

For this, you maintain a script logic and from the script you call the stored procedure alongwith its parameters. Now build a DM on top of the script logic. While running the DM, it should ask the user to select the material members. These members will be automatically passed to the script through %MATERIAL_SET%. In your script, call the stored proc and once of the parameters should be %MATERAIL_SET%.

Hope this helps.