topic Data type preservation in ADF Copy Activity to Fabric warehouse in Pipelines

Data type preservation in ADF Copy Activity to Fabric warehouse

BiJoe — Wed, 20 Mar 2024 15:36:33 GMT

We have grown tired of transforming tables in our Fabric warehouse, and decided to copy all dimension and fact tables from our existing SQL warehouse to Fabric, using Copy Activities in Azure Data Factory through a linked service.

We have made a generic Copy Activity pipeline, with parameterized table name, and a corresponding copy pipeline with tens of Execute Pipelines invoking the parameterized generic pipeline.

The pipeline is working just as it should, except that all data types in the new table columns in Fabric warehouse are varchar. How can we dynamically preserve or map datatypes from the SQL tables?

Is this the only way? I have tried with a mapping parameter, and passed a JSON mapping object, but the data type of the relevant columns was not changed in Fabric.

Re: Data type preservation in ADF Copy Activity to Fabric warehouse

Anonymous — Thu, 21 Mar 2024 04:10:59 GMT

Hi BiJoe
Thanks for using Fabric Community.
Apologies for the issue you have been facing. This is known issue that all varchar is of 8000 characters, when a user copies over data using auto create table option DF. Our internal team is working on this issue but will take time. We do not have a timeline to provide a fix on this issue.
As a work around, you can create a SQL project, fix any issues and deploy the dacpac targeting to Fabric DW. Once done, the copy activity can copy data with data type intact.

Hope this helps. Please let me know if you have any further questions.

Re: Data type preservation in ADF Copy Activity to Fabric warehouse

BiJoe — Thu, 21 Mar 2024 07:46:07 GMT

Anonymous thanks for the info. Regarding the Database projects there is this issue:

Re: [Database Projects] Statement 'CREATE TABLE' i... - Microsoft Fabric Community

where I can actually see in the CREATE statements for the Fabric warehouse tables that all VARCHAR columns have 8k characters..

EDIT: did you mean to say that all VARCHAR columns when using auto create table from ADF copy are transformed to 8k characters, or that all columns are transformed to VARCHAR types? I am experiencing the latter.

Re: Data type preservation in ADF Copy Activity to Fabric warehouse

Anonymous — Thu, 21 Mar 2024 12:11:53 GMT

Hi BiJoe

I mean to say both.

Nvarchar(n) -> varchar(8000)

Re: Data type preservation in ADF Copy Activity to Fabric warehouse

R1k91 — Fri, 22 Mar 2024 17:23:30 GMT

experiencing the same.

to bypass we do this:

- encapsulate casting/converting in a view

CREATE VIEW dbo.MyViewWithCast AS SELECT a = convert(varchar(10), t.a) b = convert(varchar(20), t.b) FROM dbo.Table1 AS t

- leverage CREATE TABLE AS SELECT in an stored procedure

CREATE TABLE dbo.MyTable AS SELECT * FROM dbo.MyViewWithCasting

- call the stored procedure from a pipeline activity

Re: Data type preservation in ADF Copy Activity to Fabric warehouse

Anonymous — Mon, 25 Mar 2024 15:46:57 GMT

Hi BiJoe
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

Re: Data type preservation in ADF Copy Activity to Fabric warehouse

Anonymous — Tue, 26 Mar 2024 16:20:21 GMT

Hi @BiJoe
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks