Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
amaaiia
Responsive Resident
Responsive Resident

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

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:

amaaiia_0-1720680797964.png

And it works, I can access the data:

amaaiia_1-1720680856495.png

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

amaaiia_2-1720680949336.png

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:

amaaiia_3-1720681044693.png 

The error message is:

amaaiia_4-1720681060531.png

 

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?

 

1 ACCEPTED SOLUTION
v-kongfanf-msft
Community Support
Community Support

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.

vkongfanfmsft_0-1720773357111.png

vkongfanfmsft_1-1720773365655.png

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.

View solution in original post

2 REPLIES 2
v-kongfanf-msft
Community Support
Community Support

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.

vkongfanfmsft_0-1720773357111.png

vkongfanfmsft_1-1720773365655.png

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.

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.

amaaiia_0-1720777533551.png

 

 

Helpful resources

Announcements
Oct Fabric Update Carousel

Fabric Monthly Update - October 2024

Check out the October 2024 Fabric update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.