Sql job fails when connection string is updated with port number,where as it succeeds without port number

Question

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.

Answer 1

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"