User configuration issue copy pipeline

frexville 51 Reputation points
2022-08-11T16:03:58.007+00:00

I created a pipeline to migrate a database from Azure SQL Database to Azure SQL Managed Instance.
I followed the steps in this link with few changes : https://learn.microsoft.com/en-us/azure/data-factory/tutorial-bulk-copy-portal

ErrorCode=SqlOperationFailed,
'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=A database operation failed with the following error: 'Invalid
object name 'DBDEV.dbo.User'.',
Source=,''Type=System.Data.SqlClient.SqlException, Message=Invalid
object name 'DBDEV.dbo.User'., Source=.Net SqlClient Data Provider,
SqlErrorNumber=208, Class=16, ErrorCode=-2146232060, State=1,
Errors=[{Class=16, Number=208, State=1, Message=Invalid object name
'DBDEV.dbo.User'.,},],'

the pipeline is working for an existing table but not for the other tables, even if I checked auto create table (see sink screenshot)

230546-adf-sink-copy.png

that's my precopy script

IF EXISTS (SELECT * FROM DBDEV.[@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]) TRUNCATE TABLE DBDEV.[@{item().TABLE_SCHEMA}].[@{item().TABLE_NAME}]  

following the INPUT result of the run

{  
    "source": {  
        "type": "AzureSqlSource",  
        "sqlReaderQuery": "SELECT * FROM [db-dev].[dbo].[User]",  
        "queryTimeout": "02:00:00",  
        "partitionOption": "None"  
    },  
    "sink": {  
        "type": "SqlMISink",  
        "preCopyScript": "IF EXISTS (SELECT * FROM DBDEV.[dbo].[User]) TRUNCATE TABLE DBDEV.[dbo].[User]",  
        "writeBehavior": "insert",  
        "sqlWriterUseTableLock": true,  
        "tableOption": "autoCreate"  
    },  
    "enableStaging": false,  
    "translator": {  
        "type": "TabularTranslator",  
        "typeConversion": true,  
        "typeConversionSettings": {  
            "allowDataTruncation": true,  
            "treatBooleanAsNumber": false  
        }  
    }  
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,520 questions
{count} votes