Unable to use a SQL Server connection with COPY (with Dataflow Gen2 it works correctly)

[amaaiia]

I have a 'C1' connection created for a SQL Server via on-premise Data gateway. From Dataflow Gen2 I can access to the data correctly, but when I try to use the same 'C1' connection from Data Pipeline's COPY activity, I can't. 

In fact, if I check the connection status in the connection list it says it is offline, when I can actually use it via Dataflow Gen2.

Scenario:

1. Use 'C1' connection to create new query on DFg2:

And it works, I can access the data:

2. Check 'C1' connection status on connections and gateways list:

How can this be possible? I'm already using the connection and it says it's offline (?)

3. Try to use 'C1' connection in COPY activity:

 

The error message is:

The name server I'm using contains the port, I don't know if that might be affecting it. It is listed as serverName:port

I have other connections created to other SQL Server on-prem, in those cases I don't indicate port because it is the default, and they work correctly, this is the only one that is not working.

Any ideas?

[v-kongfanf-msft · Accepted solution]

Hi @amaaiia ,

The error message "server endpoint's format is invalid" means that the server endpoint's format that you specified in the connection configuration is invalid and may be due to one of the following reasons:

• The server name is in an incorrect format.
• The included port number is in an incorrect format or is not required.
• There are invalid characters in the server name.

The following step to troubleshoot and resolve this issue:
Check the server name format. Ensure that the server name is formatted correctly. In general, the server name format should be servername or servername\instance. If a port is specified, the format should be servername,port.
Example: 

Correct format:
myserver
myserver\instance
myserver,1433
Wrong format:
myserver:1433

I use the same configuration information in dataflow Gen2 and pipeline everything works fine.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amaaiia]

Yes, thanks!

It is precisely the format. COPY activity supports fewer formats than DataflowGen2 does. In my case, the format has been:

server\instance,port

This format is supported by DFg2 but not by COPY activity:

server/instance:port

, and when I try to use the same connection string with COPY is when I get this error. I thought it had to be something to do with the format...

Anyway, the info tooltips should be improved because it says that

server:port 

is the valid connection string, and we are seeing that it is not.