Data type preservation in ADF Copy Activity to Fabric warehouse

[BiJoe]

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.

[R1k91]

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

[BiJoe]

@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. 

[Anonymous]

Hi @BiJoe 

I mean to say both.

Nvarchar(n) -> varchar(8000)

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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.