SSIS: How to use one Variable as Input and Output Parameter in an Execute SQL Task
Hello, i need your help,I'm working on this issue since yesterday and have no idea how to deal with it. As I already said in the tilte i want to start a stored procedure via a Execute SQL Task which has around 15 prameters. 10 of these should be used as input AND output value. As an example: i have three Variable: var1 int 2 var2 int 100 var3 int 200 the stroed procedure: sp_test @var1 int @var2 int output @var3 int output AS BEGIN SET @var2 = @var2 * @var1 SET @var3 = @var3 + @var1 END So in the Execute SQL Task i call the Stored Procedure as follwos: Exec sp_test @var1 = ?, @var2 = ? output, @var3 = ? output (I use an OLE DB Connection) The parameter mapping is as follows: User::Var1 input numeric 0 -1 User::Var2 input/output numeric 1 -1 User::Var3 input/output numeric 2 -1 Now my problem. If i set Var2 and Var3 as Input parameter the values are still the same after running the package. If i set them to a output value the are both Null because the procedure doesnt get any values. I already tried to list them a second time - like User::Var2 input numeric 1 -1 User::Var2 output numeric 1 -1 or i use a new variable User::Var2 input numeric 1 -1 User::Var2Return output numeric 1 -1 but i alwas get the error "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." Has anybody an idea how I can solve this problem? Thanks a lot. Kind Regards, Alice
March 25th, 2010 11:44am

Alice, Why do you want to use in out arguments in your stored procs?I would use two input parameters for your stored proc and a resultset as output.In that case you would only need to split the values in the resultset in the right SSIS variables. Besides that, I don't think it is possible to use in out parameter mapping in SSIS... (confirmation needed) Kind regards Alain
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2010 12:16pm

Hi Alain, thx for your answer. I have around 15 procedures called one after the other to calculated and modify my values. Each procedure is responsible for an other but overlapping set of variables. So i thought it would be a good idea to call them one after the other with the needed variables via a execute sql task. So if i use a result set, how i get my stored procedure to return 10 values? I would have to use a Function instead of a procedure, wouldn't i? As if i have 15 procedures this would be a lot of work. But thanks a lot for the idea. I think an other idea would be to create one function which calls all stored procedures and returns all the calculated values as a result set, wouldn't it?. Kind Regards. Alice
March 25th, 2010 12:49pm

Hello Alice, In your stored procedure you can just call following statement select @var1,@var2,@var3,... at the end of the procedure. This will return your variables in a result set. kind regards
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2010 1:17pm

Hallo. Thanks. I didn't know this. And i have one further question: If i use a Result Set (or a Singel Row - i always get only one row back), i have to use a object-variable - haven't i? how can i allocate the value of the first coulumn to a int variable and the value the second one to another? Thank you so much. Kind regards.
March 25th, 2010 1:26pm

As already mentioned by Alain, you'll return all the values as a ResultSet. Capture it in an Object variable, yes, and then parse the resultset, you can use script task for that. The collection will have your parameters in the same sequence as the sequence in which the SELECT query in your stored procedure. So you'd know which index to use for getting a parameter's value. Hope this helps. Cheers!! Muqadder.
Free Windows Admin Tool Kit Click here and download it now
March 25th, 2010 2:54pm

Yes, I have conformed it. It was a painful surprise that I was unable to use the same parameter for both direction of input and output. It gave a wrong result, zero for integer.
June 28th, 2011 11:51am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics