Cast value of the Additional Column in Copy Activity

hannaS 201

Hi,

I am copying data from SQL server to Parquet file in blob and then to Synapse. I need to fetch an additional column from another table in Synapse and add it to the destination (auditID). My stored procedure handles this. I am using Additional columns feature to get the value from Lookup activity and pass it to the Sink, however, the value is returned as String and I need it to be int. Casting it in Additional Column value field gives me a warning. In the Mapping section it does not allow to change data types. How can I make sure that the column is copied as type int and not String?

Accepted answer

hannaS 201 Reputation points

2020-11-24T19:51:16.317+00:00

Hi @HarithaMaddi-MSFT ,

Sorry for a late response. I have found a different solution to the problem. Instead of using additional column I am doing SELECT statement and casting the value in it. Please see picture
Please sign in to rate this answer.

1 person found this answer helpful.
HarithaMaddi-MSFT 10,136 Reputation points

2020-11-25T14:23:22.277+00:00

Hi @hannaS ,

Thanks for sharing the update. I converted your post to an answer so that it can be accepted and will be helpful for the community members reading the post.

-----

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-16T07:54:31.82+00:00
Hi @hannaS ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

Though it is showing the warning over there, this conversion is working fine and pipeline is not failing unless there are any data issues. Please try executing the pipeline and let us know in case of any errors. Below is an repro of the same.

---------------------

Please accept an answer if correct. Original posters help the community find answers faster by identifying the correct answer. Here is how.

Want a reminder to come back and check responses? Here is how to subscribe to a notification.
Please sign in to rate this answer.
hannaS 201 Reputation points

2020-11-16T19:36:30.9+00:00

Hi Haritha,

Thank you for looking into this. When I run the pipeline with "Auto create table" option, it defines AuditID in the final table as data type nvarchar while I need it to be int.

If I manually create the final table and define the AuditID as type int, the pipeline fails and gives the following error "Unexpected error encountered filling record reader buffer: ClassCastException: ,Source=.Net SqlClient Data Provider,SqlErrorNumber=106000,Class=16,ErrorCode=-2146232060,State=1"

So cast to int in the Additional columns section does not seem to do anything.

Thank you,
Hanna

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-17T08:30:29.953+00:00

Hi @hannaS ,

Thanks for sharing the observations. It seems to be an issue with the data in AuditID column, can you please try using isnumeric function on that column in Synapse and see if there are any values that give result as 0 indicating it cannot be loaded into an integer value.

Looking forward for your response!

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-20T08:27:49.73+00:00

Hi @hannaS ,

We have not received a response from you. Please suggest if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Looking forward for your response!

HarithaMaddi-MSFT 10,136 Reputation points

2020-11-24T14:12:15.273+00:00

Hi @hannaS ,

We still have not heard back from you. Following up to check if above suggested approach is helpful. Otherwise, let us know and we will continue to engage with you on the query.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community member
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Cast value of the Additional Column in Copy Activity

1 additional answer

Your answer