ADF copy data from Blob to SQL with ESRI ObjectID

Question

Hello, Sorry if this seems Obvious, I am new to Azure Data Factory and have been trying to learn.
I have an azure Blob that i want to copy into an Azure SQL DB that is for Spatial Data. The Azure Spatial data has an ObjectID field which is of type Object ID and is not editable.
When i create a link to the SQL in ADF, it interprets that field as an int, and i can't seem to change it

My Source Data doesn't have an ObjectID field so i just want to do a copy of the data from the source Blob to the sink SQL table without worrying about the ObjectID and let the database populate that field instead.
Source blob:

Copy Source pipeline:

Sink Pipeline:

mapping pipeline (Note i don't specify the ObjectID):

the error relates to not populating the ObjectID field with Null, but i don't believe i could populate Object ID anyway even if i did have an incrementing integer field in my source to copy from .

Operation on target T1ProertyToAzureESRIProperty failed: Failure happened on 'Sink' side. ErrorCode=SqlOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed. Please search error to get more details.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Data.SqlClient.SqlException,Message=Cannot insert the value NULL into column 'OBJECTID', table 'dcc_gis_prod.sde.PR_PROPERTYADDRESSWITHOWNERTABLETESTBLOBLINK'; column does not allow nulls. INSERT fails.
The statement has been terminated.,Source=.Net SqlClient Data Provider,SqlErrorNumber=515,Class=16,ErrorCode=-2146232060,State=2,Errors=[{Class=16,Number=515,State=2,Message=Cannot insert the value NULL into column 'OBJECTID', table 'dcc_gis_prod.sde.PR_PROPERTYADDRESSWITHOWNERTABLETESTBLOBLINK'; column does not allow nulls. INSERT fails.,},{Class=0,Number=3621,State=0,Message=The statement has been terminated.,},],'

I assume this is obvious to some out there, it's just that I am learning and am still very fresh to working this out, The person who has shown me how to set these up, hasn't had to deal with an ObjectID field or a mismatched source to destination field mapping.
Is this an easy thing that i can resolve, It feels if i can change the field type in the destination map, then i would hopefully be ok, but i can't edit any of those fields.
data type for ObjectID as i see it in GIS Pro

Answer 1

Hi @Dami Barnes ,
Thankyou for the ask. I understand your requirement here . You want to add the object_id column as an incremental identity column. Unfortunately, it won't be possible with Copy activity . You need to use Mapping Data flow in ADF where you can make use of Surrogate key transformation to add an additional identity column . Provide Object_Id in Key column option so as to have the identity column name as 'Object_Id'.

To get more information on surrogate key transformation , please go through this video: https://www.youtube.com/watch?v=UquN1EVaGaM

If the suggested response helped you, please click Accept Answer and kindly upvote the same. If you have any further questions about this answer, please click comment.

Answer 2

Forgive me as i am obviously very much learning this.
The destination table does have an OBJECT_ID field in it. It is of data type 'OBJECT_ID' and is managed by the Spatial Software/database, it is an incremental unique integer generation, but i can't edit or populate that field as an end user (well at least in the spatial software ArcGIS, It automatically increments if i was to add another row in the Spatial table and i can't do anything with that field).
When I created the sink dataset in ADF, it sees the OBJECT_ID but it interprets the field as an int (which although it will be a simple integer number, it is again something i can't edit and is instead managed by the spatial software) instead of an OBJECT_ID field.
If i try and ignore it, it want's to treat the field as null (well that is how i interpreted the above error message) but yes, as you say, it can't be
So i am assuming your Option 1 won't work as it can't be nullable
and Option 2 Well I can't normally edit that field in the spatial database and giving it a generic number won't work as it won't be unique or auto incrementing.
I could maybe try putting in an incremented number but is there a way i could write that as an expression?
So instead of putting '1', is there a way i could try to make that column an auto increment integer starting from 1 and see if that will accept it?

edit: I just tried editing the table directly in SQL server management and it appears as though it accepted it, AND i can read that table still in the spatial software so it might only be locked out for editing in the Spatial software....so hopefully your option 2 will work if i can auto increment the value??

Answer 3

Hi @Dami Barnes ,

Welcome to Microsoft Q&A platform and thank you for posting your query.

As I understand your question here, you are trying to copy data from Azure Blob storage to Azure SQL DB where the sink table is expecting data in ObjectId column , however, in source dataset , there is no column called 'ObjectId' .

Generally, if the column is not present in the source, that column would be treated as NULL and gets inserted into the Sink table . However, Looking at the error you are facing, it seems that in the sink table , ObectId column has been defined as NOT NULL , which is why NULL values are not able to be inserted into the ObjectId column.

You can probably opt for one of the two solutions:

Option1:
Alter the sink table to make ObjectID as Nullable .

Option2:
Go to the Source settings in the copy activity and there you have an option to add Additional column . You can add ObjectID as an additional column for Source dataset and provide a dummy value for the same using Custom value.

Here is the screenshot you can refer to :

To know more about additional column in copy activity , please go through the following video:
https://www.youtube.com/watch?v=m0Zc-CofAuc&t=312s

Hope this will help. Please let us know if any further queries.

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

• Please don't forget to click on or upvote button whenever the information provided helps you.
  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
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators