We have a sql job which creates package dynamically based on metadata config values.We have specified connection string of servers in below format.
Source : sat\sql2012,5000
destination:tak3\war2012,55750
our job tries to first validate schema of metadata after acquiring source connection and then creates package and execute it.
We are seeing strange issues with the job,job is executing fine ,if we don't specify port number
Source : sat\sql2012,5000
Even with named pipes it is working,
np:sat\sql2012
But when we specify port number along connection string,job is failing in below order.
1.Package creation started
2.Unable to connect to given source destination Exception from HRESULT: 0xC020801C
3.Package creation failed
Steps we have tried:
1.Make sure port is enabled in firewall ..it is
2.port is set to static
3.Spn is set and there are no duplicates
4.we are able to connect through SSMS locally and remotely
5.checked which node server is listening thru portqry,it is 5000 only
SSIS account and sql agent account are both the same,but it doesnt matter,job working fine without port number,so no issues with permissions
We tried creating test package in server and tested connection,connection succeded too.
No info in error logs or eventvwr,except framework error which get in logs (as pasted above with exe name).
We tried debugging using windbg,when it failed ,it showed same info as eventvwr logs
Our devs came up with theory,that job requires strong connection while validating schema,so it is failing and it is an infra issue.
We are out of options,even when we ran profiler and procmon,we are not seeing any connection drops from source and destinationCan some one help me on this please on how to debug further.
Best Answer
You should not include the instance name when specifying the port, so instead of
Source : sat\sql2012,5000 destination:tak3\war2012,55750
You should use
Source : sat,5000 destination:tak3,55750
** Note: following queries about this, I can find no documentation to support my answer. Consider it to be my personal experience only.
There is some detailed information on connection string attributes at the following URL, Using Connection String Keywords With SQL Native Client which defines the syntax for server addressing as:
[protocol:]Address[,port |\pipe\pipename] for an "Address"
or
Server= [protocol:]Server[,port] for a "Server"