Cannot execute SSIS package in SQL Server 2016

1

I've seen this posted several times, but none of the fixes are working from my instance. I'm using SSDT 2015 to create the package and deploying to a network server using SQL Server 2016. The package target server version is set to SQL Server 2016 and I've changed the run time from 64 to 32 bit (not sure if this is needed, but listed as a fix for this issue). The connection managers are using my local server (different from the server the package is being executed from) and the network server where the package is being deployed. I'm able to successfully run the package in SSDT 2015, but when deployed to the server I get the errors below stating that the connection to my local server could not be established. I the validation of the connection and the data flow to delay validation and the first two tasks run, but again, it cannot connect to my local. Since I'm running the package with my credentials, I'm not sure why it cannot connect. I am a system admin for both Server instances and I've ensured that my login has ssis_admin role assigned on SSISDB. Any assistance would be greatly appreciated.

Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "localDB\SQL2016.Local" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

ApplicationDataETL: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 Server Native Client 11.0"  Hresult: 0x80004005  Description: "Login timeout expired".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.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.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. ".
sql-server
ssis
asked on Stack Overflow Oct 3, 2018 by R4Z0R53DG3 • edited Oct 4, 2018 by Justin

1 Answer

2

Just to update everyone, this was issues that had issues and they had their issues. First, the Integration Services on the network server had been stopped due to logon error, first major issue. Second, in order to allow the network server to have access to my local server, I had to open the configuration manager on my local SQL instance and enable TCP/IP under the configuration settings for my 2016 instance. At this point, I needed to run netstat on the command prompt using my SQL Server Instance ID to determine if it is listening to port 1433. To ensure remote access, I then had to add rules to my firewall to allow TCP 1433, UDP 1434, sqlservr.exe, and sqlbrowser.exe. Once this was all completed, I was then able to connect to my local server through ssms on the network server. Using the proxy account I created for SQLAgent, I the job was then able to process successfully.

answered on Stack Overflow Oct 3, 2018 by R4Z0R53DG3

User contributions licensed under CC BY-SA 3.0