I'm trying to call a stored procedure in an Execute SQL task which has several parameters. Four of the parameters are input from package variables. A fifth parameter is an output parameter and its result needs to be saved to a package variable.
Here is the entirety of the SQL in the SQLStatement property:
EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = , @Started = NULL, @Status = 1, @FileType = , @FileName = , @FilePath = , @Description = NULL, @ItemId = OUTPUT;
I have also tried it like this:
EXEC log_ItemAdd 'isMedicalClaim', , NULL, 1, , , , NULL, OUTPUT;
Here are my Parameter Mappings:
Variable Name Direction Data Type Parameter Name
User::ImportJobId Input LONG 0
User::FileType Input LONG 1
User::FileName Input LONG 2
User::FilePath Input LONG 3
User::ImportId Output LONG 4
When this task is run, I get the following error:
0xC002F210 at [Task Name], Execute SQL Task: Executing the query "EXEC log_ItemAdd @Destination = 'isMedicalClaim', @ImportJobId = , @Started = NULL, @Status = 1, @FileType = , @FileName = , @FilePath = , @Description = NULL, @ItemId = OUTPUT" failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_I4)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The User::ImportId package variable is scoped to the package and I've given it data types from Byte through Int64. It always fails with the same error. I've also tried adjusting the Data Type on the Parameter Mapping, but nothing seems to work.
Any thoughts on what I might be doing wrong
Thanks.
Stored Proc Output Parameter to a Variable
jagotaas
well I was having the same problem and was doin the same mistake as mike
..now I got the solution for it..
thanks everybody
thembi
In terms of debugging, I would use process of elimination:
1. hard code parameters into sql statement. Does it run OK
2. replace hard coded parameters with one dynamic parameter at a time until you find the first problem.
3. fix that one
4. loop back to step #2.
DeltaQ
I was convinced that it was a problem with my OUTPUT parameter.
As usual, it was an obvious problem that I couldn't see: I was declaring a LONG data type for the User::FileName and User::FilePath variables. Obviously those are strings and not numeric.
After changing those to VARCHAR in the Parameter Mappings, I was able to get it to work.