January 4, 2012 at 10:23 am
I have built a stored procedure that contains a variable @Trigger varchar(1) and at the end of the store procedure has:
SELECT @Trigger
So the stored procedure just prints the value of @Trigger after it runs through all the IF statements.
How can I pass this stored procedure variable @Trigger to my SSIS package variable User::Trigger?
Right now I have an Execute SQL Task built:
1) Result Set: None
2) ConnectionType: OLE DB
3) Connection: SQL table
4) SQLSourceType: Direct Input
5) SQL Statement: exec usp_IssueCheckingProcessing
In my parameter mapping I have the following:
1) Variable Name: User::Trigger
2) Direction: Output
3) Data Type: VARCHAR
4) Parameter Name: @Trigger
5) Parameter: -1
When I run the package I get the following error:
[Execute SQL Task] Error: Executing the query "exec usp_IssueCheckingProcessing" failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Thanks in advance for any hints you may be able to provide!
January 4, 2012 at 11:14 am
If you're using "SELECT @Trigger", that's a result-set, not an output parameter. Change to a simple result set, and map it in the options.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2012 at 12:28 pm
What is a "simple" result set? I tried modifying the execute SQL task like this:
1) Result Set: Single-Row
2) ConnectionType: OLE DB
3) Connection: SQL table
4) SQLSourceType: Direct Input
5) SQL Statement: exec usp_IssueCheckingProcessing
In my parameter mapping I have the following:
1) Variable Name: User::Trigger
2) Direction: Return Value
3) Data Type: VARCHAR
4) Parameter Name: @Trigger
5) Parameter: 1
But received the following error:
[Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_SingleRow".
January 4, 2012 at 12:39 pm
Is @Trigger an output parameter or does the proc Select it?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 4, 2012 at 1:10 pm
The stored procedure selects it.
January 5, 2012 at 5:01 am
Triality (1/4/2012)
The stored procedure selects it.
in the Result Set , add the variable which you want to store the sp value and put 0 in the result name only if sp returns a single output...
Thanks,
Charmer
January 5, 2012 at 10:14 am
Thanks Charmer, that did the trick!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply