CODEWITHSUNDEEP
ssis
user1810575
user1810575

Reputation: 831

SSIS Execute SQL task with parameter

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.

Upvotes: 3

Views: 3978

Answers (2)

S.M
S.M

Reputation: 776

user1810575 has asked this question again in ssis-execute-sql-task-based-on-parameter, see my answer (which is copied here as well).

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

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

  • In Expression tab, set the SQLStatementSource to

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

Upvotes: 1

Registered User
Registered User

Reputation: 8395

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.

Upvotes: 2

Related Questions