CODEWITHSUNDEEP
sqlssispackageintegration
Bob Ryder
Bob Ryder

Reputation: 57

Execute SSIS Package Script Task From Stored Procedure Get Variable Value

I have an SSIS package that takes in (through a package parameter) a value, passes it into a script task via a script variable (readonly variable), converts it to another value inside the script task, and finally writes that value out to another script variable (readwrite variable). There are no other SSIS modules in the package aside from the one script task.

What I would like to do, from outside the package (via SQL) is:

  1. Call the SSIS package, passing in a value for my parameter and variable
  2. Get the value of the read/write variable that is determined at the end of the script task execution

I've got step #1 working, just can't figure out #2.

How do I get the value of package variable in an SSIS package after it has executed? Is it accessible? Is it stored anywhere or can I store it somewhere in the SSIS catalog? I've tried to see if it's stored in the SSISDB.[catalog].executions table somewhere, but it doesn't seem to be.

Do I need to write that script variable to a package parameter in order to see it from SQL after execution? Could I then perhaps see it by using EXEC [SSISDB].[catalog].get_parameter_values, or does that only show parameter values before package execution? Am I going about this completely the wrong way?

Thanks in advance!

Upvotes: 0

Views: 1246

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

What I would do is add one last step to the package to write the value of the variable to a table.

Then you can retrieve the value from the table via SQL.

You can either truncate the table every time, or keep a permanent history associated with each time the package runs.

Upvotes: 0

Related Questions