locked
ADF v2 SQL On-prem to SQL On-prem Schema Mapping error: Invalid numeric precision/scale RRS feed

  • Question

  • I'm moving data from a SQL Server table to another SQL server on-prem table, however I'm getting a " Invalid numeric precision/scale." error in a Copy Activity, I've identified that the error is caused by the "date" data type in SQL Server.

    in my target sql server on-prem I'm using a stored procedure with a table-type parameter, all of the other tables are getting loaded, however the only ones facing this problem are the ones that have either "Date" or "Money" data types, Datetime is working just fine. I've been using the structure parameter in my data sets but I'm still getting the error:

    This is an example of the structure parameter when creating the SQL Server Table dataset (Using Python)

    structure =  [
    {'name': 'columnA', 'type': 'Int32'},
    {'name': 'columnDate1', 'type': 'Date'}, 
    {'name': 'ModifiedDate', 'type': 'Date'}, 
    {'name': 'BatchID', 'type': 'Int32'}
    ]

    Any ideas of what this could be?

    For testing purposes I changed the datatype of my source to Datetime and it worked , however this won't be possible in a production environment. I'm also doing something similar with the "money" data type and I use Decimal as recommended by Microsoft but I'm getting the same error

    https://docs.microsoft.com/en-us/azure/data-factory/connector-sql-server

    https://docs.microsoft.com/en-us/azure/data-factory/concepts-datasets-linked-services#dataset-structure


    • Edited by saulcruzr Friday, December 8, 2017 4:05 AM
    Friday, December 8, 2017 4:04 AM

Answers

  • Not sure if this is the best answer, but just in case someone else faces this issue, I ended up using a translator which I wasn't even considering as my column names are exactly the same in both source and target...

    Anyway, I did the following (Using Python)

    translator = TabularTranslator('ColumnA: ColumnA,
    ColumnDate1: ColumnDate1, 
    ModifiedDate:ModifiedDate,
    BatchID: BatchID') 
    
    activity_object = CopyActivity(name=activity_name,
    inputs=[source_ref],                                    
    outputs=[target_ref],                                    
    source=SqlSource(),              
    sink=SqlSink(pre_copy_script='TRUNCATE TABLE ' + table_name,                                                   
    sql_writer_stored_procedure_name=usp_name_target,                                                  
     sql_writer_table_type=usp_param_name),                   
    translator=translator                                       )
    If you have a better answer please let me know =)

    • Marked as answer by saulcruzr Sunday, December 10, 2017 11:30 PM
    Friday, December 8, 2017 4:49 AM