April 19, 2016 at 2:51 am
Hi all,
I cant believe it but how to do this has completely gone from my mind and I cant find anything specific to this issue which Im sure is an easy one.
I have a stored Procedure with two parameters
Create Procedure [dbo].[USP_StartImportAuditPreSP]
@sp-2 varchar(100),
@SystemArea varchar(100)
et etc
Obviously If I run the parameter like this
Exec [dbo].[USP_StartImportAuditPreSP] 'USPCreateChildren', 'Area1'
It works. I want to have this in my IS package in an Execute SQL task. They will sit in front of other SQL tasks and I want to hard code the parameters as I go.
I thought I did Exec [dbo].[USP_StartImportAuditPreSP] ? ?
But after this I don't know what to do. As in where do I actually write in the information?
Any help would be appreciated
Debbie
April 19, 2016 at 3:02 am
Debbie
There's a Parameter Mapping tab in the Execute SQL Task Editor. Just assign a variable to each parameter, taking care that the order of Parameter Name is the same as the order the parameters appear in the query. Make sure to check the Parameter Name convention for the connection provider you're using (for example it may be 0,1,2 or Param1,Param2,Param3 or something else).
John
April 19, 2016 at 3:09 am
Ive got that set up. I have both my parameters
User::SP Input Varchar 0 -1
User::SystemArea Input Varchar 1 -1
But I don't know where to put the values (Ill be using the same SP throughout the process to write audit values into an audit table
Debbie
April 19, 2016 at 3:23 am
You don't put the values anywhere. The values are passed in from the values of the variables. If you're asking how to assign values to the variables, that depends on what's upstream in your control flow. Maybe the values come from a parent package, or perhaps they're from an output parameter in a previous Execute SQL task - there's lots of possibilities. Unfortunately, there isn't an Assign Variable Value task: if you want to assign the value directly, you'll need to do it in a Script Task. There are lots of examples out there if you search for them.
John
April 19, 2016 at 4:01 am
Ah, all you need to do is the usual. Exec USP_AdminProc 'Test','Area' and it works.
How obvious. Thanks
Debbie
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply