Do I have to use a stored procedure in exe SQL task to use a parameter?

  • I want to set a conditional in an exec sql task based on the value of one of my ssis variables but would rather not have to create a stored proc. Is it possible to do something like this:

    If ? = "19000101"

    Begin

    Select GetDate()

    End

    ELSE

    Begin

    Select ?

    End

    Id be setting another ssis variable to the result set from the task.

  • The answer is no, I didnt need a stored proc...but, I did need to add the same ssis variable in as a paramter for the second placeholder (?)

  • It may be easier to understand, and maintain, if you created two separate Execute SQL tasks, one for each query and then used expressions on the precedence constraints that lead up to those tasks.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • You should rewrite the query so there is only one parameter.

    SELECT Value = CASE WHEN Arg = '19000101' THEN GETDATE() ELSE Arg END FROM (SELECT Arg = ?) a

    or even

    DECLARE @arg DATETIME;

    SET @arg = ?;

    SELECTValue = CASE @arg WHEN '1900-01-01' THEN GETDATE() ELSE @arg END

  • Are you convinced you require a round trip to the database server at all?

    If your example reflects the true need and the SSIS Package runs on the same machine as the database instance, or at least the same time zone, then a SSIS Variable that takes its value from an Expression like this is all you need:

    @[User::YourDateVariable] == "19000101" ? GetDate() : @[User::YourDateVariable]

    Where @[User::YourDateVariable] is the variable you were substituting into your SQL statement.

    PS Note, you may need to tweak your data types when using an expression this way to compare strings and then returning a datetime from GetDate().

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply