0

My goal is to get data through a GET Request and, using Copy Data, introduce it in parquet file format in the Data Lake. My pipeline currently works, but I wish not to map manually all the variables and their respective types.

I would like to use the Copy Data without a specific variable mapping in order to keep it flexible, and for that purpose I believe I can select the option "Map complex values to string". However, when I try Debugging my pipeline, the following message appears:

{ "errorCode": "2200", "message": "ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'Value' contains an invalid value '541.9481'. Cannot convert '541.9481' to type 'Int64'.,Source=Microsoft.DataTransfer.Common,''Type=System.OverflowException,Message=Value was either too large or too small for an Int64.,Source=mscorlib,'", "failureType": "UserError", "target": "Copy to Landing", "details": [] }

How can I avoid this situation so that every variable in the response to the GET request flows into the parquet file without errors like the one mentioned above?

Is there another way to achieve the same goal (i.e., Copy Data without manually mapping the variables)?

Thanks!

Mateo

Mateo Estrada
  • 15
  • 3

1 Answers1

0

Copy data with Auto Mapping will automatically detect the type for sink dataset. Therefore, this may result in error.

Message=Value was either too large or too small for an Int64.,

Is there another way to achieve the same goal (i.e., Copy Data without manually mapping the variables)?

You can give the mapping in the dynamic content.

  • You need to give type, mapping and collection reference as a json document.

Sample Json mapping document:

 { 
 "type": "TabularTranslator", 
 "mappings": [
  {
   "source": 
      { "name": "Id" },
      {"type": "int"}
   "sink":
      { "name": "CustomerID" },
      {"type": "int"} 
   },
   {
    "source": 
      { "name": "Name" },
      {"type": "string"}
    "sink": 
      { "name": "LastName" },
      {"type": "string"} 
    },
    { 
     "source": 
       { "name": "LastModifiedDate" },,
      {"type": "string"}
     "sink":
       { "name": "ModifiedDate" } ,
      {"type": "string"}
     }
 ] 
}
  • Create a pipeline parameter of object type and assign the Json mapping document as default value or give the Json mapping document during runtime.
  • Add source and sink dataset.
  • In mapping, Click Add dynamic content.

enter image description here

  • Click the pipeline parameter name that is created. enter image description here

  • If we have multiple copy activities to be done in single pipeline, You can store the Mapping Json for each copy activity in Lookup table and call it using lookup activity and refer that value in mapping section of copy activity.

Reference: How to do a Dynamic Column mapping in Copy Activity (microsoft.com)

Aswin
  • 4,090
  • 2
  • 4
  • 16
  • Hello Aswin, thank you so much for your answer! I would like to know especifically if I can avoid the aforementioned error ("Message=Column 'Value' contains an invalid value '541.9481'. Cannot convert '541.9481' to type 'Int64'." and "Message=Value was either too large or too small for an Int64.,") when selecting the "map complex values into string" option, so that every variable in the response to the GET request flows into the parquet file without these kind of errors and without schema errors. Again, thank you for your time. – Mateo Estrada Jan 18 '23 at 12:20
  • Selecting the "map complex values into string" option and Automapping will result in error. It is necessary to give mapping dynamically if you don't want to map manually. – Aswin Jan 19 '23 at 05:24