locked
Getting error when using a variable in connection manager RRS feed

  • Question

  • In my SSIS package I am trying to loop the database servers to get the information. I have defined the variable and assigned server name to it, I am able to test it using script task in For Loop Container. I have created a connection Manager called MultiServer and passing the ServerName Variable thru Expression as @[User::SRV_Conn]. When I click evaluate expression, it is showing as 0. Now in Data Flow I selected a new OLE DB Source and when I try to select MultiServer I am getting error Hresult:0x80004005 Server is not found or not accessible. For testing purpose I have created the OLE DB for the error server and it works fine. Not sure what I have missed. I used http://www.simple-talk.com/sql/ssis/using-ssis-to-monitor-sql-server-databases-/ and trying to do the same. Please help me. Thanks.

    Thursday, April 14, 2011 6:34 PM

Answers

  • Got it, then just pre-populate this variable at design time (in the variables editor) with a dummy or actual server name, then at run time hopefully the proper names will be used (from the sql query).

    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Coorg Thursday, April 14, 2011 9:58 PM
    Thursday, April 14, 2011 9:38 PM

All replies

  • you should set a valid servername value for that variable, this will check only in Compile time in SSIS,

    or you can use DelayValidation as True to avoid validation errors.


    http://www.rad.pasfu.com
    • Proposed as answer by Reza RaadMVP Friday, April 15, 2011 5:39 AM
    Thursday, April 14, 2011 6:40 PM
  • so you hare saying that if you hard code it it will work????????????

    what i sthe DELAY VALIDATION for the connections and the DB names ???????


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, April 14, 2011 6:44 PM
  • you should set a valid servername value for that variable, this will check only in Compile time in SSIS,

    or you can use DelayValidation as True to avoid validation errors.


    http://www.rad.pasfu.com

    i think that you have got it
    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Thursday, April 14, 2011 6:45 PM
  • I have valid server names in my table which I select it thru SQL Execute Task and write it to the variable. I also set the DelayValidation to True, still I am getting the error.
    Thursday, April 14, 2011 8:54 PM
  • So, did you inspect the contents of the @[User::SRV_Conn] variable at run time?

    I suspect you do not get the values properly. You can use a watch window to debug or a script task to pop a message box to examine the contents of the variable before it hits the connection.


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, April 14, 2011 9:04 PM
  • Yes I used the Script task and I am able to see the server name correctly. But in MultiServer connection manager where expression value set as @[User::SRV_Conn] and when hit evaluate button I am seeing the value as 0.

    Thursday, April 14, 2011 9:18 PM
  • Its value matters at run time, so please run your package and peek at the contents of this var then.

    Arthur My Blog
    By: TwitterButtons.com
    Thursday, April 14, 2011 9:23 PM
  • I agree, but in Data Flow Task - > OLE DB Source when I select MultiServer as connection manager, enter the sql command and hit Ok I am getting the error.
    Thursday, April 14, 2011 9:28 PM
  • Got it, then just pre-populate this variable at design time (in the variables editor) with a dummy or actual server name, then at run time hopefully the proper names will be used (from the sql query).

    Arthur My Blog
    By: TwitterButtons.com
    • Marked as answer by Coorg Thursday, April 14, 2011 9:58 PM
    Thursday, April 14, 2011 9:38 PM
  • Arthur Thanks a lot, I assigned a server name to the variable and it worked.
    Thursday, April 14, 2011 9:46 PM
  • Arthur Thanks a lot, I assigned a server name to the variable and it worked.

    That was my First Suggestion !:

    "you should set a valid servername value for that variable"


    http://www.rad.pasfu.com
    Friday, April 15, 2011 5:38 AM