2

Is it possible to return output parameters from a SSIS "Script Task" that has been called by a stored procedure using xp_cmdshell?

All the samples I have found so far show how to assign values to dts package variables etc; and show them via a message box but every single sample I see shows the script task only returning a Dts.TaskResult = (int)ScriptResults.Success; or Dts.TaskResult = (int)ScriptResults.Failure ...

Basically, I have a script task that gets several values from a dll call that works as anticipated; values are verified with message boxes; but I have not been able to ferret out how to return those back to the stored procedure that originally executed the SSIS package.

Am I missing something obvious? please provide a functional code example & or screen shots of control flow / data flow, etc., of how to do this end to end... E.G.: From a stored procedure -> Execute a *.dtsx package, passing it parameters, including OUTPUT parameters; and how that same stored procedure can read those output parameters in this type of call; when the results are returned...

thanks in advance.

user1011144
  • 193
  • 2
  • 4
  • 13

1 Answers1

5

I don't know if it is possible to return values in the script task to your calling stored procedure, but here's an alternative way I would do this and may get you your end result. Create local SSIS variables that can capture the values in your script task that are currently being shown in your message boxes. For example create one called mySSISVariable:

Variable Window

On the Script screen of your task make sure to select mySSISVariable as ReadWriteVariables:

Script Task Script Screen

Now in your script task assign this SSIS variable to whatever you are currently outputting to message boxes:Dts.Variables["User::mySSISVariable"].Value = returnedValue; Now that SSIS has this value stored in a variable you can use it when calling your new stored procedure, that would have done whatever your calling stored procedure was supposed to do with these variables, in an Execute SQL Task(for more than one parameter keep adding ? as such usp_MyNewStoredProcedure ?, ?, ?:

Execute SQL Task General Screen

Making sure to assign your SSIS user variable as an input parameter(in this example I used OLE DB, this link shows how this can be accomplished using others):

Execute SQL Task Parameter Screen

jymbo
  • 1,335
  • 1
  • 15
  • 26