I have an Execute SQL task in my SSIS package that should execute a SQL stored procedure only if a certain condition is true.
I have a user variable [User::run] that gets a value when the package is started. In my SQL task, I have the following code:
declare @run varchar(1)
set @run = '" + @[User::run] + "'
if @run = 'Y'
begin
EXEC sp_procedure()
end
However, the stored procedure never gets executed. Any help or suggestions would greatly be appreciated.
This look like an expression not a sql.
Open Execute SQL Task , go to Expressions tab , add an expression to SqlStatementSource
"declare @run varchar(1)
set @run = '" + @[User::run] + "'
if @run = 'Y'
begin
EXEC sp_procedure()
end"
"you can use an expression to concatenate the parameter values into the query string and to set the SqlStatementSource property of the task." Read more
What if instead of dynamically setting the [User::run] variable, you manually set it. Does it execute the procedure then? If yes, you know there's an issue with setting the variable, if no, then you know it's something with the task.
Is there a particular reason you want to use an if statement to try and execute the procedure? I'd recommend two precedence constraints. One directs to the end of the job, perhaps an email stating that it finished and log any results. The other directs to the stored procedure.
As an example, one precedence constraint would connect from your first step to the second step, second step being your exec t-sql. The connector properties would be as seen below to evaluate if variable is set correctly, then it would flow to your T-SQL task.
Here is what the other connector would be set to as well, it would flow to another task, one that would end the ssis package.
