Post reply

SSIS from ODBC source using a dynamic sqlcommand

  • November 14, 2007 at 4:59 pm

    #121111

    Does anyone know how I can use a user variable in a sqlcommand in a Datareader source with an ODBC connection as the source?

    I am storing a date value in a user variable(Date) I fill with a SQL Task and then want to use the value in the sqlcommand I use in the Datareader Source. It won't let me use the @variablename in the sql command.

    Can anyone help with some advice on how I can make this work?

    Appreciate any help I can get.

    Thank you

  • November 15, 2007 at 5:45 am

    #751458

    You have to use expressions

  • November 15, 2007 at 6:26 am

    #751476

    Thanks, it works now. I created a select statement in a script task and assigned it to a user variable and then used the variable in the expression.

    Appreciate it.

    Thank you

  • April 15, 2008 at 9:41 am

    #802661

    Did you use dataReader??

  • April 15, 2008 at 9:43 am

    #802662

    How can I build the relationship between expression and DataReader?

  • April 26, 2008 at 9:00 pm

    #807533

    Hi Can you share the exact steps?

    I created a variable and assigned it a dynamically SQL query.

    Then I used that variable in SQLCommand of DataReader Source Component as @[User::Query]

    But now I dont see any output columns? How do I map those to my OLE DB Destinataion?

    Thank you

    Siddharth

  • April 28, 2008 at 2:47 am

    #807643

    Initialize your variable with a dummy SELECT statement at design time. The result should reflect the output structure (data types and column names) of your final query result.

    Ex.: SELECT CAST(1 AS INT) AS Column1, CAST('' AS VARCHAR(50)) AS Column2

  • January 12, 2010 at 6:39 pm

    #1102847

    I would appreciate more details. I have tried to use a variable both in a sql statement in the sqlcommand property and even created an entire sql statement within an expression. Errors out every time. Tried parens vs brackets vs neither.

    I would like to make sure the :: is simply the colon typed twice. If not, what is it?

    If so, need further assistance.

    My end state is to create a statement with a more flexible date structure. Currently I use current_date-1, but if a day goes by w/o it running, I have some hoops to jump through to get some items straight.

    Thanks,

  • January 13, 2010 at 2:04 am

    #1102926

    RonKyle (1/12/2010)

    I would appreciate more details. I have tried to use a variable both in a sql statement in the sqlcommand property and even created an entire sql statement within an expression. Errors out every time. Tried parens vs brackets vs neither.

    I would like to make sure the :: is simply the colon typed twice. If not, what is it?

    If so, need further assistance.

    My end state is to create a statement with a more flexible date structure. Currently I use current_date-1, but if a day goes by w/o it running, I have some hoops to jump through to get some items straight.

    Thanks,

    :: is just two consecutive colons, confirmed.

    Can you give an example of your erroneous SQL Statement, which property you assigned it to and what error you received please?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • January 13, 2010 at 6:31 am

    #1102996

    I solved this. I had to edit the expression from the data flow overall icon (the one you see when you are looking at the package). I had done all my editing within the datapump, and the sqlcommand value, while there, can't be edited as an expression there (which I think it should).

    Once found, I took the "failed" work from before and it worked fine.

  • January 13, 2010 at 7:21 am

    #1103034

    Bravo!

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • April 13, 2011 at 3:56 pm

    #1311367

    I figured it out just like metioned earlier. Take a look at this link http://lukehayler.com/2010/03/setting-the-sql-command-property-using-expressions-in-ssis-2005/

    The trick is that even though you are using the data reader data flow transformation, you can modify the data reader data flow transformation sql command in the PARENT of it --what I mean is you have to look to the data flow task that is in the control flow tab.

    This is not intuitive but what you do is go to the control flow task, NOT the data reader itself. Look at the properties of the data flow task, there will be an [...] next to the "Expressions" property. This is where can set the data reader source sql command property.

    It is totally non-intuitive because you have to go to the PARENT, not the child to set this property.

  • March 24, 2019 at 2:55 pm

    #2025310

    jarmstrong-537702 - Wednesday, November 14, 2007 4:59 PM

    Does anyone know how I can use a user variable in a sqlcommand in a Datareader source with an ODBC connection as the source?I am storing a date value in a user variable(Date) I fill with a SQL Task and then want to use the value in the sqlcommand I use in the Datareader Source. It won't let me use the @variablename in the sql command.Can anyone help with some advice on how I can make this work?Appreciate any help I can get.Thank you

    this video may help you 

    SSIS Dynamic SQL Command

Viewing 13 posts - 1 through 12 (of 12 total)

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