I have a scenario where we need to check the condition based on the condition we need to insert the record in the respective table.
My requirement is like if we pass the parameter value ‘I’ it means to need to insert a record in the table and if the parameter value is ‘T’ need to truncate the table.
Here I am taking Execute SQL task.
Now I am creating a variable know as Flag and assign default value is ‘I’ see below
Now I am configuring the execute SQL task connection manager. Creating the connection and writing the query.
Mapping the parameter here in place of?
Now we run this package.
Before running this package record in the table
Now running this package with parameter value ‘I’
Package executed successfully.
See the records in the table.
Now I am testing with the other value ‘T’. If we set the value as ‘T’ the table will be truncated.
Running the package.
Package executed successfully. Now see the records in the tables.
Another way to use this dynamic query to use
Select direct input.
Now go to expression.
In the expression, I am writing below query which is dynamically generated.
The value in the expression tab
|
"DECLARE @FLAG VARCHAR(1)
SET @FLAG ='"+ @[User::Flag] +"'
IF @FLAG = 'I'
BEGIN
INSERT INTO EMP VALUES(1,'BAGESH')
END
IF @FLAG = 'T'
BEGIN
TRUNCATE TABLE EMP
END"
|
Click on the evaluate expression we will get below value.
|
DECLARE @FLAG VARCHAR(1)
SET @FLAG = 'I'
IF @FLAG = 'I'
BEGIN
INSERT INTO EMP VALUES(1,'BAGESH')
END
IF @FLAG = 'T'
BEGIN
TRUNCATE TABLE EMP
END
|
Now click ok.
We are seeing the fx sign on the execute SQL task.
It means in this task we are using the dynamic expression.
Now I am executing my package.
Package executed successfully.
See the record in the table.
Best practices: when we are using dynamic SQL query it will be better to build using expression.
No comments:
Post a Comment
If you have any doubt, please let me know.