topic Is it possible to add an index/row number/identity with a Copy activity? in Pipelines

Is it possible to add an index/row number/identity with a Copy activity?

arpost — Fri, 03 Nov 2023 21:01:05 GMT

Greetings, community. I have a pipeline I'm using to insert data into a data warehouse table. Since Fabric's DW doesn't yet provide an identity column, I want to add a unique identifier to the data. I know I can run a script after the Copy Activity completes that assigns a GUID, but is it possible to do this from within the Copy Activity itself so I don't have to first insert and then update?

Re: Is it possible to add an index/row number/identity with a Copy activity?

ajarora — Fri, 03 Nov 2023 21:14:13 GMT

No, unfortunately this is no such way inside Copy. But feel free to submit an idea on Fabric Ideas page.

Re: Is it possible to add an index/row number/identity with a Copy activity?

Anonymous — Sat, 04 Nov 2023 03:27:24 GMT

Thank you ajarora for your insights.

arpost ,

Appreciate if you could share the feedback on our feedback channel . Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

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

Re: Is it possible to add an index/row number/identity with a Copy activity?

Anonymous — Mon, 06 Nov 2023 17:51:28 GMT

Hi arpost ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help .

Re: Is it possible to add an index/row number/identity with a Copy activity?

jwinchell40 — Thu, 09 Nov 2023 13:53:32 GMT

arpost - I had this same issue/requirement. I was able to figure out a way to do this though:

Stage and Prod Tables:

I setup a staging table to load the data I wanted to upsert. In the Prod Table I added what would typically have been my Identity column.

Stored Proc/SQL Query Load:

In the SQL Query that would load the data from Stage to Prod; I created a variable called "MaxKey". That would get the current maximum value currently in my identity column. That would be the starting point for any new records I needed to insert.

DECLARE @MaxIdentity bigint; SET @MaxIdentity = (SELECT MAX(productrowkey) from dimProduct);

Then in my query to actual generate the rows to update you can use the ROW_Number() function:

SELECT ROW_NUMBER() OVER (ORDER BY stg.ProductName ASC) + @MaxIdentity as productkey, stg.ProductName FROM stage_Product stg LEFT JOIN dimProduct prd on stg.ProductName = stg.ProductName WHERE prd.productkey is null

So far everything appears to be working as expected for me. The downside later is that once the Identity function is supported you will have to migrate data to a new table as I don't think you can convert an existing column to an Identity.

Re: Is it possible to add an index/row number/identity with a Copy activity?

arpost — Thu, 09 Nov 2023 14:23:22 GMT

Thanks, jwinchell40. That's similar to the approach I adopted, and it works.