Search code examples
sql-serverssisetlsql-server-data-tools

Conditional Execute SQL task in SSIS

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.

Solution

  • 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