Post reply

SSIS dynamically parameterized query

  • December 26, 2008 at 7:55 am

    #127750

    I am trying to migrate and automate few works with the help of SSIS.

    Now, that i am automating the whole thing i need to use sql statements

    and have them executed in the pipeline.

    However, one of the requirements is to assign few values to the sql statement dynamically and execute them.These values will be assigned on the fly from the varibales already defined.

    e.g select [col1].[col2] from [table1] where [col3]=

    Could you please let me know, what are the options that i have in SSIS for doing that?

  • December 26, 2008 at 8:39 am

    #917806

    Create a new variable that will store your paramaterized query.

    Go to the variable's properties window (F4) and set "Evaluate As Expression" to true.

    Click the ellipsis next to the Expression property and build your parameterized statement. It would look something like this, assuming the WHERE clause is going to use a string variable named CompanyName:

    "select [col1].[col2] from [table1] where [col3]= '" + @[User::CompanyName] + "'"

    Notice that I've added single quotes in the expression around the string variable.

    Set your SQL Source or Execute SQL task to use this new variable.

  • December 29, 2008 at 7:39 am

    #918374

    Thanks for the reply...

    still it does not seem to work.

    first i have defined a package level varibale StartDate of value type datetime

    then i created another variable sqlstring for holding the whole sql statement

    turned the Evalluate Expression True and just in the next box(expression)

    entered "sp_OracleInterface_InvoiceNoTemp '" + @[user::StartDate] +"'"

    Now created a execute sql task, and selected variables in the sqlsource section, selected the variable user::sqlstring

    But it is giving me the following error while running the application

    Nonfatal errors occurred while saving the package:

    Error at Package: The variable "user::StartDate" was not found in the Variables collection. The variable might not exist in the correct scope.

  • December 31, 2008 at 12:59 pm

    #919806

    There are many places in SSIS that are case sensitive - I notice that we are showing

    @[User:: and @[user::

    I believe the capitalized version is correct. That may be your issue.

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

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