Great article, thank you for sharing the code

David1/6/2009 7:26:37 PM

I was that DBA... ;)

For me was better to get the list of the Available Sql Servers using (c#), to avoid use link servers or MultiServer connection to get the information:

using Microsoft.SqlServer.Management.Smo.RegisteredServers;
SmoApplication.EnumAvailableSqlServers(false)
returns a DataSet

Now I want to improve my project.

References:
http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.smoapplication.enumavailablesqlser
vers.aspx

Thanks.

Ludwig2/12/2008 3:30:59 PM

dmbranscome, I'm glad you find the article valuable. Check out Rodney's follow-up article on his SSIS/SSRS solution in the Feb. issue of SQL Server Magazine, online at http://www.sqlmag.com/Article/ArticleID/97840/sql_server_97840.html.

Anne1/29/2008 11:45:26 AM

at a minimum the article provides further examples of SSIS implementations.
This should be good. Thanks dmb

David1/15/2008 6:33:42 PM

Don't know if anyone has pointed this out, but we seemed to have trouble with named instances on servers with more than the default. Discovered that we had to use Servername and port instead of Servername\\Instance. That seems to work.

Rick11/21/2007 9:54:58 AM

What do I do if the stored procedure I am running returns more than one recordset? I want to run a system stored procedure on many server (SQL Server 2000 and 2005). I want the data to go into tables that I can analyze on one server.

STEPHEN11/21/2007 9:16:04 AM

sgreene -
I see your point. Though I stated on page 2 (web) how to set the Connection Manager object expression, I had the crucial details @[User::SRV_Conn] in a screen shot (Figure 8) - not in text. I am glad you figured it out. Yes..all SSIS users unite. I am working on a follow up article right at this moment that has many enhancements to this original SSIS package. Look for it in the upcoming few months.
Thanks for the kind words.
Rodney Landrum - author.

RODNEY9/18/2007 7:43:55 AM

rsqlmag,
This could be caused by a number of things. Here are a couple to check out.
Named Pipes is not enabled by default. If for whatever reason a Named Pipe connection is being tried for the 2005 servers you can enable it via the SQL Server Configuration Manager under SQL Server 2005 Network Configuration. Also, make sure that remote connections are allowed. This can be enabled with the Surface Area Configuration for Services and Connections. Set Remote Connections to use both, or if local connections only is checked select, allow local and remote connections. On SQL Server Express, Local connections only is the default if I am not mistaken. Hope this helps.
Rodney Landrum - author.

RODNEY9/18/2007 7:36:05 AM

Great article, this is going to be immensly useful!
One bit of info, not explicitly stated, that I was having trouble finding, is how the connection is dynamically set. You have the variable set, but never mention that. For those who might be having the same problem finding this vital bit of info(ssis beginners unite!), it is set in the connection manager as an expression: @[User::SRV_Conn].

samuel9/12/2007 11:19:12 AM

Great Article...and appreciate the generosity for the code....:)

I did a similar solution and my SSIS package just gets the backup info....But when I connect to SQL2005 servers I get this message...I am executing the SSIS packages from a JOB in SQL2005, the agent account is a domain account and it has access to all servers...I am able to get information for all 2000 servers but 2005 servers have this issue: Any input will be appreciated:

Error: 2007-09-06 02:26:48.54
Code: 0xC0202009
Source: DatabaseBackupsForEachServer Connection manager "Source Conn"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Named Pipes Provider: No process is on the other end of the pipe.
".
End Error
Error: 2007-09-06 02:26:48.55
Code: 0xC020801C
Source: Data Flow Task Source [1]
Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Source Conn" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
End Error

Ranga9/6/2007 12:49:52 PM