Hi, I've setup an ssis package similar to the one outlined in Rafael's post http://www.rafael-salas.com/2010/01/ssis-loop-through-multiple-database.html. However when I debug the package it fails on the Foreach Loop Editor & subsequently the inner Execute SQL task. I have listed the error below. I am wondering whether the ConnectionString expression for the Dynamic connection is correct? "Provider=SQLNCLI;Server=+@[User::TargetInstanceConnectionString]+;Database=database_name;Trusted_Connection=yes;" The User variable 'User::TargetInstanceConnectionString' has been setup with Scope = Package, Data Type = String & Value = packageProvider=SQLNCLI;Server=servername\instance;Database=SQL_DBA;Trusted_Connection=yes; I am new to SSIS expressions and not sure if I have the syntax right although it evaluates without any error's. Error below:- SSIS package "Package.dtsx" starting. Error: 0xC0202009 at Package, Connection manager "DYNAMIC - servername\instance.database": 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: "Login timeout expired". 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: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ". Error: 0xC00291EC at Retrieve's SQL Build, SQL Service Pack & SQL Edition information, Execute SQL Task: Failed to acquire connection "DYNAMIC - servername\instance.database". Connection may not be configured correctly or you may not have the right permissions on this connection. Thanks in advance for your help!

What is your requirement? please check the value of your user variable.... Let us TRY this | My Blog :: http://quest4gen.blogspot.com/

Looks like your have't pass the correct SQL instance name to your variable (DYNAMIC - servername\instance.databas) ? or your expression was wrong http://uk.linkedin.com/in/ramjaddu

Hi, I can see nothing wrong with the user variable. I would like to run the following tsql against installed sql instances and insert the resultsets into a table. INSERT INTO dbo.tbl_ServicePack (SQL_Server, SQL_Build, SQL_Service_Pack, SQL_Edition) SELECT SERVERPROPERTY ('ServerName') AS 'SQL_Server', SERVERPROPERTY('productversion') AS 'SQL Build', SERVERPROPERTY ('productlevel') AS 'Service Pack', SERVERPROPERTY ('edition') AS 'SQL Edition' Although to be fair the tsql code is irrelevant. I'm more interested in getting the ssis package to iterate through each server instance which are stored in another table as SQL Native Client connection strings Thanks.

The error you are receiving is caused by the sql server instance not being reachable. It could be because You are trying to connect to the server using Named Pipes and this protocal is disabled on the server however the machine you are running the package on is configured to use the Named Pipes protocol. Either change the client protocol to use TCP or enable Named Pipes on the server. Have a read of http://technet.microsoft.com/en-us/library/ms189321(SQL.90).aspx The sql server instance does not allow remote connections. Have a read of http://support.microsoft.com/kb/914277 The server\instance name being wrong. If there is more than 1 sql server instance on the server you are trying to connect to, you need to ensure you pass in [ServerName]\[InstanceName] The account you are running the package under does not have access to the sql server instance you are trying to access. Cheers JeffJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

Thanks for your replies... @Jeff - I've checked these but don't understand how the instance is not reachable? If I use SSMS from the the same machine and the same sa login I can connect to the instance without any problems. @RamJaddu - I was wondering if my expression was wrong too any ideas? I've designed the package under SQL 2008 R2 BIDS and trying to run it against 32 SQL 2005 SP3 named instances does this make a difference? Am happy to post screenshots of package if it will help. Thanks.

Hi, Your connection string states that you are using a Trusted Connection (Trusted_Connection=yes) i.e. Windows Authentication however you stated in your previous reply that you can login using the sa login. If you are using a sql server login account (sa), then you are not using windows authentication to connect to the sql server instance. You need to replace "Trusted_Connection=yes" with "User ID=sa;Password=<your password>" That said, you shouldn't be using the sa account, especially from within SSIS packages so create/use an account specifically created for your package. Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

Instead of using windows authentication why don't you try login with SQL authentication. Create login in SQL Server mapping login user database user and grant persions to user. Connection string must be some thing like this "Server=myServerAddress;Database=myDataBase;User ID=myUsername;Password=myPassword;Trusted_Connection=False;" here are samples for connections strings http://www.connectionstrings.com/sql-server-2005 http://uk.linkedin.com/in/ramjaddu

Isn't that what I just said?Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

Hi Jeff, Just to clarify I am using Windows Authentication. The sa login I was referring to was my windows domain admin account. Which I use to connect to all of our SQL instances via windows authentication.

Thanks RamJaddu, Wanted to see how it worked via Windows Authentication initially as my windows domain account is already setup on the 32 named instances I want to run the package against. I know I can run tsql against multiple servers/instances using other methods but really wanted to try this as it seemed reasonably straight forward with SSIS.

Hi, Ok. You have stated that your connection string expression is: Provider=SQLNCLI;Server=+@[User::TargetInstanceConnectionString]+;Database=database_name;Trusted_Connection=yes; and that the variable User::TargetInstanceConnectionString has a value of packageProvider=SQLNCLI;Server=servername\instance;Database=SQL_DBA;Trusted_Connection=yes; Wouldn't this give you a connection string of: Provider=SQLNCLI;Server=packageProvider=SQLNCLI;Server=servername\instance;Database=SQL_DBA;Trusted_Connection=yes;;Database=database_name;Trusted_Connection=yes; Shouldn't the variable [User::TargetInstanceConnectionString] just contain the name of the sql server instance as that's what you are trying to do by using Server=+@[User::TargetInstanceConnectionString]+;? Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)

Thanks Jeff... I did say I was new to this... :-)

No problem and thanks for marking your question answeredJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt)