September 1, 2009 at 8:00 am
The domain must be accessible from the server running the package. A trust must be establish between domains. When accessing servers in another domain, I use the full name of the server in the table SSIS_ServerList.
Example: servername.domain.com
I do not know how to setup a domain. I let the network team handle it.
David Bird
September 1, 2009 at 9:57 am
I actually used the ip address but no go.
I can connect to them via machinename\localuser using sql management tools, ideras toolset, windows explorer, ect.
Just hoping for an easier solution.
Thanks anyways. 🙂 Still works great for my main servers.
February 4, 2010 at 9:16 am
David,
This article is extremely helpful. Great job and thank you for submitting!
I am running into a problem connecting to some of my servers, not all. I have compared between the environments and cannot find a difference. Can you tell me what all should be open on the server side in order for this connection to be successful? From my troubleshooting, I have found that I had to enable OPENROWSET advanced option on the remote server. I thought this would fix the problem, but apparently there is something else blocking it. I also ensured that named pipes was enabled. I am running this from my machine and can connect to all servers in my ServerList table, so I don't think security is an issue.
Any help would be greatly appreciated. I have been working on this for hours!
The error populating the Errors table is:
SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Named Pipes Provider: Could not open a connection to SQL Server [2]. ".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "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: 0x80040E14 Description: "OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".".
February 4, 2010 at 11:56 am
I've experienced the timeout error when SQL Server is down or I VPN from home using a different domain. Some containers fail from being unable to obtain a lock.
Try using the full domain server name in the SSIS Server table
Verify the login being used by SSIS has the needed permission. You can start a profile on the remote servers to see what login is being used on both successful and unsuccessful connections.
Review the error log for the SQL Server having connection failures. Make sure the capture Login Failure setting is enabled on the server.
David Bird
February 4, 2010 at 2:16 pm
David Bird (2/4/2010)
I've experienced the timeout error when SQL Server is down or I VPN from home using a different domain. Some containers fail from being unable to obtain a lock.Try using the full domain server name in the SSIS Server table
I am not using VPN, I am directly connected to our network. I verified all servers are online. Most of the servers failing to connect are test servers and I know they are not utilized much, so I don't think locking is an issue. As one of my troubleshooting steps, I already have fully qualified the server name with the domain.
Verify the login being used by SSIS has the needed permission. You can start a profile on the remote servers to see what login is being used on both successful and unsuccessful connections.
All connection managers are setup to use Windows authentication. I am logging into SSIS using my windows ID and running (debugging) from there. My id is SYSADMIN on all servers in the ServerList table.
Review the error log for the SQL Server having connection failures. Make sure the capture Login Failure setting is enabled on the server.
I have reviewed the logs and there are no entries of failed login attempts or any errors for that matter. They are all set to capture failed login attempts only.
I thought maybe it's a firewall issue, but then I would have issues trying to connect to the servers from Management Studio as well. I am at a loss.
February 4, 2010 at 2:41 pm
David,
I just had a thought. The servers that are working are running under the default port #, the others that I'm having issues with are not. I tried to connect to a problem server with the port included in Management Studio to make sure I get the string correct before adding to the ServerList table. The string that worked was ServerName.domain.com,Port#\Instance. I added this to the table, but it still errors out! Am I doing something wrong? I feel like this is the smoking gun!
April 19, 2010 at 7:15 am
Hi,
I have such a problem: In the dbo_SSIS_ServerList I have 236 servers.
Most of the server will through the errors - I know that and I want that, the expected problem is with connection.
When package finishes, in the dbo.SSIS_Errors I have 200 servers and in dbo.Database_Status 15, so 21 servers are missing.
I have already changed the Maximum error count to 99999. but it does not help.
Can you suggest me the solution?
Kasia
April 19, 2010 at 11:06 am
MaximumErrorCount is set for each container. For example Populate ADO Variable is set to 1 because if it can't set the variable there is no reason to attempt the other containers. Most of the remaining containers are set to 999.
If your MaximumErrorCount is set correctly for all containers, you can try running the script for just the just the servers you are missing and maybe find out why they are missing.
You can also run two two copies of these packages, with each one processing a separate list of servers
David Bird
February 17, 2011 at 1:06 am
I tried to set this application up in a 64-bit environment, but I ran into this error below, which I did not get when I previously set this up in a 32-bit environment. Any tweaks that you guys have made to make this run in 64-bit?
[OLE DB Destination [1015]] Error: 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: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Violation of PRIMARY KEY constraint 'PK_SQL_Servers'. Cannot insert duplicate key in object 'dbo.SQL_Servers'.".
Thanks in advance.
July 5, 2011 at 10:27 am
Thank you David Bird -- it's a job well done!!
Works like a charm running against 103 active servers/1046 databases....
Looking forward to Parts 2 and 3.
May 10, 2012 at 4:11 am
Hi,
I'm trying to create this SSIS package I can definately get the server info for all the server which are on windows authentication however I can't figure out how to get the server info for the servers which are on different domain and are using SQL authentication. Any suggestions please?
Regards,
Netra
February 24, 2014 at 12:38 pm
I get error at step 7. When I try to preview my data I get following error.
Error at SQL_Overview_Package [Connection manager "MultiServer"]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Login timeout expired".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
This is my connection string for Dynamic connection
Data Source=(local);Initial Catalog=DBA;Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False;Application Name=SSIS-SQL_Overview_Package-{818761D7-5168-450D-A372-4F3169897015}MultiServer;
Viewing 12 posts - 46 through 56 (of 56 total)
You must be logged in to reply to this topic. Login to reply