SSIS Execute SQL task with parameter

Question

I need to execute sql task based on parameter. Lets say if my @parameter = 1 then execute this sql if @parameter = 2 then execute this sql. I think of a work around but is there anything straight forward such as Len(?) or Len(@parameter1) ..

Bottom line: I need to execute sql query based on what's passed to parameter. Let me know if that's possible.

Answer 1

If you want an Execute SQL Task to run a different stored procedure based on a variable, then there are a few options:

1. You could create a stored procedure that takes a parameter. The stored procedure would use IF ELSE code to execute the code as described in a comment by Lamak. This is a less than ideal solution if you want to execute different stored procedures. This could work if you only have a very small number of queries or stored procedures to execute.

2. You could write a variable that calculates the name of the stored procedure based on an expression. This could work well if you only have a few stored procedures to execute, but it does not scale for a large number of stored procedures. It also is hard to understand from a coding perspective, particularly if the expressions are complex.

3. You could write a query or stored procedure that generates a separate stored procedure call command. You could run an Execute SQL Task the loads a result set. The result set would map to a variable of Object data type. You could then iterate through the variable in a For Each Container to assign values to variables. Easier to manage than 100 expressions if you have a lot of code to vary.

Based on your comment to me it sounds like you want to try option 2. The following are detailed steps for option 2:

1. In the Variables window at the package-level scope create a variable called SqlCommand of data type String.

2. Set the EvaluateAsExpression property for the SqlCommand variable to True.

3. Click on the expression builder link.

4. The following is a sample IF THEN ELSE expression using the Conditional operator.

   1 == 0 ? "SELECT SomeField = GETDATE();" : "SELECT SomeField = GETDATE() - 2;"

   If 1 equals 0, then the first command will be returned. If 1 does not equal 0, then the second command will be returned. In this case, since 1 does not equal 0, the second command is returned. You can change the 1 == 0 section to be the condition you actually want to evaluate.

5. Add an Execute SQL Task to the control flow.

6. Open the Execute SQL Task Editor.

7. Set Connection to your desired database connection manager.

8. Set SQLSourceType = Variable.

9. Set SourceVariable to User::SqlCommand.

10. Close the editor and test the package.