topic Re: Whitespace in source column name rejected by copy activity in Pipelines

Whitespace in source column name rejected by copy activity

Anonymous — Wed, 28 Aug 2024 11:32:49 GMT

I am using a pipeline with a copy activity to get data from a SQL server. Since I loop through a list of tables, I get the mapping from a meta table that I prepare beforehand. However, if the source column name contains a space, I get the following error:

Failure happened on 'destination' side. ErrorCode=DeltaInvalidCharacterInColumnName,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column name Location Code contains invalid characters. ",;{}()\n\t=" are not supported.,Source=Microsoft.DataTransfer.ClientLibrary,'

However, the failure is certainly NOT happening on the 'destination' side. The schema looks like this (it works neither with the encompassing backticks nor without them):

{ "type": "TabularTranslator", "mappings": [ { "source": { "name": "`timestamp`" }, "sink": { "name": "timestamp" } }, { "source": { "name": "`Location Code`" }, "sink": { "name": "Location_Code" } }, ] }

Since the space is in the name of the source column, I can't change it. So what do I do?

Re: Whitespace in source column name rejected by copy activity

Anonymous — Thu, 29 Aug 2024 01:09:50 GMT

Hi Anonymous

You can try using square brackets as the source column name instead of backquotes. For example:

{ "type": "TabularTranslator", "mappings": [ { "source": { "name": "[timestamp]" }, "sink": { "name": "timestamp" } }, { "source": { "name": "[Location Code]" }, "sink": { "name": "Location_Code" } } ] }

Regards,

Nono Chen

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

Re: Whitespace in source column name rejected by copy activity

Anonymous — Thu, 29 Aug 2024 06:58:27 GMT

Nice try, but it doesn't work, yielding the same error message.

Just in case, the input for the copy activity really looks like this:

{ "source": { "type": "SqlServerSource", "queryTimeout": "02:00:00", "partitionOption": "None", "datasetSettings": { "annotations": [], "type": "SqlServerTable", "schema": [], "typeProperties": { "schema": "dbo", "table": "ABCDEFG", "database": "HIJKLMNOP" }, "externalReferences": { "connection": "684354684651687654657684" } } }, "sink": { "type": "LakehouseTableSink", "tableActionOption": "OverwriteSchema", "partitionOption": "None", "datasetSettings": { "annotations": [], "linkedService": { "name": "Sandbox", "properties": { "annotations": [], "type": "Lakehouse", "typeProperties": { "workspaceId": "XXX", "artifactId": "XXX", "rootFolder": "Tables" } } }, "type": "LakehouseTable", "schema": [], "typeProperties": { "table": "XXX" } } }, "enableStaging": false, "translator": "{\n\t\"type\": \"TabularTranslator\",\n\t\"mappings\": [\n\t\t{\n\t\t\t\"source\": { \"name\": \"[timestamp]\" },\n\t\t\t\"sink\": { \"name\": \"timestamp\" }\n\t\t},\n\t\t{\n\t\t\t\"source\": { \"name\": \"[Location Code]\" },\n\t\t\t\"sink\": { \"name\": \"Location_Code\" }, *[here I removed the other columns]* \n\t]\n}" }

Re: Whitespace in source column name rejected by copy activity

chrichards — Thu, 03 Oct 2024 06:01:03 GMT

Hi - were you able to resolve this? I'm encountering the same issue...

Re: Whitespace in source column name rejected by copy activity

tarektm7 — Sun, 06 Oct 2024 03:11:49 GMT

I was facing the same issue in a similar situation.

The only workaround I could do is to use a notebook and get the data in a dataframe then write it into a delta table as below:

df.write.option('delta.columnMapping.mode', 'name').save(path='Tables/schemaname/tablename', format= 'delta', mode='overwrite')

Re: Whitespace in source column name rejected by copy activity

Anonymous — Fri, 15 Nov 2024 09:14:13 GMT

Alright, I got it and it was trivial in hindsight: The translator above is a literal string. In order to pass it correctly as a parameter, you have to put "@json(mapping_string)" instead of what I did, which was simply "@mapping_string".

Re: Whitespace in source column name rejected by copy activity

priyankabis — Thu, 20 Mar 2025 18:59:16 GMT

Isn't there a way or setting like we do in notebooks so that it can read columns with Spaces and save with _ instead od space