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.

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

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).

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 itSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

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.

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

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.

Its value matters at run time, so please run your package and peek at the contents of this var then.Arthur My Blog

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.

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

Arthur Thanks a lot, I assigned a server name to the variable and it worked.

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