none
(ADF V2) Pre-Copy script to delete data on Oracle DB fails when no records to delete

    Question

  • I am attempting to clean up data in an Oracle sink table using the "pre-copy script" option in the sink definition.

    Through a bit of trial and error, it seems that when there are no records to delete in the destination table the copy fails with the following errors:

    IR Event Log Error:

    Copy failed with error: 'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=An error occurred but no ODBC error information was available.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,StackTrace=   at Microsoft.DataTransfer.Runtime.OdbcSourceProvider.Read()
       at Microsoft.DataTransfer.Runtime.SourceStageProcessor`1.ProcessInternal(NoData data)
       at Microsoft.DataTransfer.Runtime.PipelineProcessStageProcessor`2.Process(TInput data)
       at Microsoft.DataTransfer.Runtime.Provider.HybridRuntimeDataReader..ctor(HybridRuntimeCommand command, String commandBehavior, IMemoryLimit memoryLimit, Int32 previewCount)
       at Microsoft.DataTransfer.Runtime.Provider.HybridRuntimeCommand.ExecuteReaderInternal(String commandBehavior, Int32 rowCount)
       at Microsoft.DataTransfer.Runtime.Provider.HybridRuntimeCommand.ExecuteNonQuery()
       at Microsoft.DataTransfer.Runtime.PreCopyScriptStageProcessor.ProcessInternal(NoData data)
       at Microsoft.DataTransfer.Runtime.PipelineProcessStageProcessor`2.Process(TInput data)
       at Microsoft.DataTransfer.TransferTask.CopyTask.DoCopy()
       at Microsoft.DataTransfer.TransferTask.CopyTaskBase.Execute(),''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Interop.OdbcException,Message=An error occurred but no ODBC error information was available.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.Native,StackTrace=   at Microsoft.DataTransfer.ClientLibrary.Odbc.Interop.OdbcUtils.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at Microsoft.DataTransfer.ClientLibrary.Odbc.Api.AbstractOdbcStatement.ExecuteCore(RowRange rowRange, String[] columnNames)
       at Microsoft.DataTransfer.ClientLibrary.Odbc.Api.AbstractOdbcStatement.ExecuteQuery(RowRange rowRange, String[] columnNames)
       at Microsoft.DataTransfer.ClientLibrary.Odbc.Api.AbstractOdbcStatement.ExecuteQuery()
       at Microsoft.DataTransfer.Runtime.OdbcSourceProvider.Read(),'
    Job ID: 9265c6dc-f134-4384-8dbf-485fd3774820
    Log ID: Error

    The ADF UI Pipeline failure error:

    { "errorCode": "2200", "message": "Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=An error occurred but no ODBC error information was available.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Interop.OdbcException,Message=An error occurred but no ODBC error information was available.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.Native,'", "failureType": "UserError", "target": "Copy1" }

    I'm assuming this is a bug that is causing this?

    Otherwise, is there a way to tell the system to not interpret this as a failure when nothing is deleted? I'd very much expect during normal processing of a transaction log we would be adding data and not deleting, but in the off chance something had to re-run, only then would data be deleted. Therefore, the normal outcome is nothing gets deleted. The process can't fail every time this happens.

    Thanks in advance.


    Thursday, April 19, 2018 7:28 PM

Answers

  • Thanks for reporting this issue. Please try following workaround right now. We will work on the fix as soon as possible.

    Workaround: (have an additional select statement in your precopyscript for oracle scenario)

    Please pay attention that Oracle has to use block (BEGIN/END) for multiple SQL statement

    So the workable script looks like

    DECLARE V1 VARCHAR (512); BEGIN delete from SomeTable where rownum = 999;select somecolumn INTO V1 from SomeTable where rownum=1;END;

    The declared V1 type should be the same as the somecolumn type

    Friday, April 20, 2018 8:54 AM

All replies

  • Thanks for reporting this issue. Please try following workaround right now. We will work on the fix as soon as possible.

    Workaround: (have an additional select statement in your precopyscript for oracle scenario)

    Please pay attention that Oracle has to use block (BEGIN/END) for multiple SQL statement

    So the workable script looks like

    DECLARE V1 VARCHAR (512); BEGIN delete from SomeTable where rownum = 999;select somecolumn INTO V1 from SomeTable where rownum=1;END;

    The declared V1 type should be the same as the somecolumn type

    Friday, April 20, 2018 8:54 AM
  • I had a similar thought after walking away from this for a period of time.

    I'll give this approach a try where we evaluate the table first for anything to delete and then delete rows only if there is something there.

    Friday, April 20, 2018 7:21 PM
  • The proposed script worked.

    I also tried a similar approach in oracle that is basically a SQL IF EXISTS() construct.

    Seems like the final output of the block will drive the success/failure of the script.

    Sunday, April 22, 2018 12:44 PM