1) I created a variable in package level like @id
2) I want to use this variable in Execute Sql task
like this:
select
col1,col2
from table
where col1=@id
and col2=@id
Add a comment
|
3 Answers
You can map the variables through Parameter Mapping tab on the execute SQL task.
Comments
In Execute Sql Task Editor in General tab, write your query like this:
select
col1,col2
from table
where col1=?
and col2=?
Then in Parameter Mapping tab add two parameters and fill:
Variable Name = [pick your variable], Direction="Input", Data Type = [pick your data type], Parameter Size = -1
Both entries should differ only by Parameter Name: 0 for first parameter, and 1 for second parameter.
1 Comment
Anoop Verma
@frikzoid, your solution is just fine. That's why I upvoted it. The only change I would suggest is passing one parameter only once, even if it's going to be used multiple times within query query/code. Again, the code was still correct and useful!
Pass it only once. Receive the parameter in a local variable, and use that variable in your code.
Let me explain it by an example. Hopefully, you will see what I meant by passing one parameter only once, even if it is going to be used in multiple places inside the query/code.
Also, there is an advantage is converting the received parameters to local variables. By doing so, we would not have to reconfigure the parameters if we modify the order of these parameters inside our query/code.
CREATE TABLE [dbo].[Test]
(
[RecID] [int] IDENTITY(1,1) NOT NULL,
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[Result] [varchar](50) NOT NULL
) ON [PRIMARY]
--Populate the table
RecID col1 col2 Result
1 10 10 R1
2 10 11 R2
3 11 11 R3
4 11 11 R4
-- Test this code inside SSMS first. Notice, I am declaring the variable only once.
DECLARE @id AS INT = 11;
SELECT
col1,col2
FROM
Test
WHERE
col1=@id
AND col2=@id;
-- Now all we need to do it put this code inside the Execute SQL Task Editor, and pass the parameter
In the general section: Result Set: Full result set SQLSourceType: Direct input SQLStatement:
DECLARE @id AS INT = ?;
SELECT
col1,col2
FROM
Test
WHERE
col1=@id
AND col2=@id;
In the Parameter mapping section:
Variable Name: User:id (or whatever you have created in your package)
Direction: Input
Data Type: LONG (for this example)
Parameter Name: 0
Parameter size: -1
In the Result set section:
Result Name: 0
Variable Name: User::oResultSet (or whatever you have created in your package)
