none
Dynamic Query in OLEDB source

    Question

  • Hi,

    First im gonna explain what i am trying to do.

    I have stored procedure to get database information and store it in a table. As i need to collect all server information implementing SSIS package to read all instance info. In the procedure i have dynamic query(in @command variable) and i have 'exec sp_MSForEachDb @Command'.

    I dont know how to implement this in SSIS. I created variable (evaluate expression as true and gave query into expression). I tried to use that variable in OLEDB source,

    how to achieve this?

    thank you

     

    Wednesday, November 21, 2012 9:45 PM

Answers

  • Hi thanks for the reply. i used execute sql task and it working good..
    • Marked as answer by neel24 Tuesday, November 27, 2012 8:42 PM
    Tuesday, November 27, 2012 8:41 PM

All replies

  • Hi,

    You can not do this way. If the parameter is gonna constant, you can keep it in a variable and take the source command from the variable. And use script task to frame the query and assign it to a variable in control flow and then use the variable in the source command. I have done this when i was handling similar case. I haven't left with option to use parameterised source query..

    I wonder why the "Parameters" button is available. But i used OLE DB. That parameter button may work for ODBC, not sure


    Bunch of thanks ~ Deva ~ mark it as answer if it answered your question :)

    Wednesday, November 21, 2012 10:33 PM
  • if ur loading of data in table is done by by that stored procedure itself ,then u can use execute sql task.

    check this link

    http://blogs.msdn.com/b/msdnts/archive/2006/11/17/amazing-ssis-parameters-in-execute-sql-task.aspx

    or

    using oledb command check this too

    http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx

    • Edited by Nishink Thursday, November 22, 2012 5:37 AM
    Thursday, November 22, 2012 5:33 AM
  • Hi,

    First im gonna explain what i am trying to do.

    I have stored procedure to get database information and store it in a table. As i need to collect all server information implementing SSIS package to read all instance info. In the procedure i have dynamic query(in @command variable) and i have 'exec sp_MSForEachDb @Command'.

    I dont know how to implement this in SSIS. I created variable (evaluate expression as true and gave query into expression). I tried to use that variable in OLEDB source,

    how to achieve this?

    thank you

     

    Can you add this line SET FMTONLY OFF ..  to your stored proc and give it a try ? This worked for me .

    Thanks !


    Rajkumar Yelugu

    Thursday, November 22, 2012 6:00 AM
  • Hi thanks for the reply. i used execute sql task and it working good..
    • Marked as answer by neel24 Tuesday, November 27, 2012 8:42 PM
    Tuesday, November 27, 2012 8:41 PM