Post reply

How can I use parameterized query (Select statement Where Col1= 'U') in OLEDB source of Data Flow task?

  • June 27, 2016 at 2:13 pm

    #400333

    How can I use parameterized query (Select statement Where Col1= 'U') in OLEDB source of Data Flow task?

    Attached is a test project that has three data flow taks:

    1 - regular select statement in OLEDB source of Data Flow (WORKS)

    2 - select statement in Variable (WORKS, too)

    3 - variable is used as ? in sq statement as param -- DOES NOT WORK

    3 a even if I use statement as COMMAND in OLEDB source and use ? inside statement and pap parameter in the task, still does not work.

    How can I proceed?

    Thanks a lot in advance

    Voldemar

    likes to play chess

    Likes to play Chess

  • June 27, 2016 at 2:28 pm

    #1887138

    You need to build the value of the variable as an expression as there's no place to define parameters in the data flow for the data source.

    "select name as [name], id as [id] from sysobjects where type ='"+ @[User::sSQLVariable] +"'"

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 11, 2016 at 3:16 am

    #1889359

    3 - variable is used as ? in sq statement as param -- DOES NOT WORK

    Can't see why this should not work. Which version of SSIS are you doing this on?

    "The price of anything is the amount of life you exchange for it" - Henry David Thoreau

  • July 11, 2016 at 6:19 am

    #1889388

    Mohit Dhiman (7/11/2016)

    3 - variable is used as ? in sq statement as param -- DOES NOT WORK

    Can't see why this should not work. Which version of SSIS are you doing this on?

    That only works for SQL Tasks, not as a source on a data flow task.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 11, 2016 at 7:12 am

    #1889400

    MSSQL 2008 R2 SP3

    Likes to play Chess

  • July 11, 2016 at 7:41 am

    #1889411

    Luis Cazares (7/11/2016)

    Mohit Dhiman (7/11/2016)

    3 - variable is used as ? in sq statement as param -- DOES NOT WORK

    Can't see why this should not work. Which version of SSIS are you doing this on?

    That only works for SQL Tasks, not as a source on a data flow task.

    Luis, are you sure about that? If you choose SQL command as the Data access mode, you get a Parameters button on the right hand side, and you can define parameters similar to how you do in an Execute SQL Task.

    John

  • July 11, 2016 at 7:55 am

    #1889416

    John Mitchell-245523 (7/11/2016)

    Luis Cazares (7/11/2016)

    Mohit Dhiman (7/11/2016)

    3 - variable is used as ? in sq statement as param -- DOES NOT WORK

    Can't see why this should not work. Which version of SSIS are you doing this on?

    That only works for SQL Tasks, not as a source on a data flow task.

    Luis, are you sure about that? If you choose SQL command as the Data access mode, you get a Parameters button on the right hand side, and you can define parameters similar to how you do in an Execute SQL Task.

    John

    Oh my god, I didn't see that button. I checked on the wrong place.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 11, 2016 at 8:06 am

    #1889419

    Voldemar, you're mixing up variables and parameters. If you want to use a variable, build up the SQL statement in the variable outside of the data flow task. If you want to use parameters, choose "SQL command" instead of "SQL command from variable" as your data access mode.

    John

  • July 11, 2016 at 8:27 am

    #1889424

    So, inside a data flow task, on an OLEDB source, if you select SQL command as the data access mode and click the Parameters button you can pass value/s to your source query and you can use a SSIS variable/s as that value/s..

    Just refer to these values/variables as ? inside your query..

    "The price of anything is the amount of life you exchange for it" - Henry David Thoreau

Viewing 9 posts - 1 through 8 (of 8 total)

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