I built an SSIS package that pulls data from multiple servers and writes to a main server where the ssis resides.
I built this on my local and it runs when I right click the package in the solution and executes but when I deployed the dtsx to the server, the package is failing.
On the server, it executes successfully all the task that runs on the main server but fails on all tasks where it needs to connect to the remote server to read the data.
I am calling the package with the cmd: SELECT @Cmd = 'DTexec /FILE "' + @Path + 'Package.dtsx" /MAXCONCURRENT 1 /CHECKPOINTING OFF /REPORTING EW'
EXEC @ReturnCode = xp_cmdshell @Cmd
Errors I am getting are below:
List item 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 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: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".
