SSIS Execute SQL task based on parameter

问题:

Can i do something like below, let me know IF @parameter=1 BEGIN ...query... END IF @parameter=2 Need the correct syntax if it is possible. It's OLE DB connection. Not a Stored Proc. just a sql query

回答1:

DECLARE @param AS INT = ?;

IF @param  = 1
BEGIN
    SELECT 1 AS Y;
END
ELSE IF @param = 2
BEGIN
    SELECT 2 AS Y;
END

There are two question marks in your query and probably you were passing only one variable. I have seen code where developers pass the same value twice (or multiple) times. This is inefficient. A better way is to receive the passed parameters in SSIS variables. Advantages: 1. You need to pass one value only once. 2. More importantly, if you change the order in which the passed parameters are used in the sql, you do not need to change their order on the user-interface of Execute SQL Task Editor//Parameters. This is what Andy Leonard has suggested later in his response.

回答2:

You can. Assuming you are referring to an Execute SQL Task, the parameters in an Execute SQL Task using an OLE DB connection utilize question marks (?) as parameter placeholders. You map the placeholders to SSIS variables on the Parameter Mapping page of the Execute SQL Task. In the SQLStatement property you would enter:

If (?=1) begin ... {some T-SQL here} ... end If (?=2) begin ... {some T-SQL here} ... end

That's one way to accomplish what I think you are asking.

Another way is to create an Execute SQL Task to read the value of @parameter from the database into an SSIS variable. Then you can build two Execute SQL Tasks - one with each option for T-SQL as the SQLStatement property - and use expressions on precedent constraints to determine which Execute SQL Task to execute.

Hope this helps, :{>

回答3:

You cannot use Execute SQL Task to run Transact-SQL statements.

For setting conditional SQL Statement based on what you are trying to achieve.

In Execute SQL Task editor

1. In general tab, leave the SQLStatement blank.
2. In parameter mapping tab, add parameter and map User::Parameter variable to Parameter Name 0.

3. In Expression tab, set the SQLStatementSource to

   (DT_NUMERIC, 18, 0) @[User::Parameter]==1 ? ...query 1... : ...query 2...