Post reply

Trouble using parameters in SQL Command

  • August 31, 2010 at 2:16 pm

    #148560

    I have a query I need to run on multiple databases. To do this, I have created a foreach loop to loop through my resultset that contains the names of the databases I want my query to execute against. I have then stored the name of the database into a variable. I'm attempting to use this varaiable in my OLE DB Source in SSIS. I have selected 'SQL Command' as the data access mode and selected the proper connection manager. What I am trying to do is this:

    Use ?

    GO

    'SQL Command Here'

    I click the 'Parameters...' button and expect to get prompted to set my ? to my varaiable I created, however I receive the following error message:

    'Parameters cannot be extracted from the SQL Command. The provider not help to parse parameter information from the command. In that case, use the "SQL command from varaible' access mode, in which the entrie SQL Command is stored in a variable.'

    I have even tried simplie queries such as select ? from 'table_name', and recieve the same error. Can someone direct me to what I'm diong wrong?

    Thanks in advance,

    Adam

  • September 1, 2010 at 11:33 am

    #1215178

    Ran into this before myself, and the answer is.. you can't directly..

    BUT, you have several options to make it happen:

    1. Use an EXEC SQL or Script Task before the data-flow to build the whole command that is used in the data-flow source.

    2. Use Expressions to set the initial catalog on the source data connection. Then the query doesn't need to even know WHAT database it is running in.

    Any questions?

    CEWII

  • September 1, 2010 at 11:40 am

    #1215184

    Good ideas..I'm a DBA who is just beginning to dive into SSIS, so the response is much appreciated. I'll give those a try. It still perplexes me as to why the parameter button is even there if you can't even use it.

  • September 1, 2010 at 12:46 pm

    #1215220

    Elliott Whitlow (9/1/2010)

    Ran into this before myself, and the answer is.. you can't directly..

    BUT, you have several options to make it happen:

    1. Use an EXEC SQL or Script Task before the data-flow to build the whole command that is used in the data-flow source.

    2. Use Expressions to set the initial catalog on the source data connection. Then the query doesn't need to even know WHAT database it is running in.

    Any questions?

    CEWII

    Option #2 would be my preferred option


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • September 1, 2010 at 12:49 pm

    #1215223

    As would be mine.

    CEWII

  • September 2, 2010 at 8:02 am

    #1215620

    I haven't used expressions much - could one of you explain how to use the variable I created that is storing the database name as an expression that I can set the inital catalog as?

    Thanks in advance,

    Adam

  • September 2, 2010 at 9:07 am

    #1215682

    Nevermind, I've figured it out. Thanks again for the replies.

Viewing 7 posts - 1 through 6 (of 6 total)

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