Issue with the the new Microsoft managed Oracle data source component for SSIS

  • During preparation for a large SSIS upgrade project from SSIS 2012 to SSIS 2019 we have identified an issue with the new Microsoft managed Oracle data source component for SSIS 2019. This component replaces the Attunity component used in earlier versions of SSIS. If you are fortunate the upgraded SSIS packages will simply fail but the bug has the potential to corrupt data silently. Subsequently we have found this is not just an upgrade issue, it can impact any Oracle data source based on the new component.

    To summarise. The new Oracle data source can, as with other SSIS database data source components accept a query to define the required columns. This is best practice, doing otherwise means a change in the source table would cause a failure.  If this query is modified after the components columns have been created the existing column mapping information in the component will not be updated. So if an additional column is added to the end of query this will be handled correctly. But If the order of the existing columns in the query is changed or a new one inserted this will not be correctly picked up. If columns of a different type appear in changed positions in the query the mapping will fail at runtime. However if the transposed columns are of the same type data will be loaded into the wrong columns.  So for example if you change the order of two date columns the updated source component will return data according to the original column order, i.e. in the wrong columns.

    For clarity, this is new behaviour and is inconsistent with other data source components.  The  only “solution” we have found is to  delete and re-create the data source.

    Clearly  this could result in accumulating data errors and a loss of historical data which would be hard if not impossible to re-create. We have raised this issue with Microsoft and are not looking for other assistance but given the potential impact I thought we had best share that  the problem exists.

    And an update, 21/04/2022: Microsoft have released a fix for this bug, Microsoft Connector for Oracle V1.2 from the Microsoft Download Center

    "https://www.microsoft.com/en-us/download/details.aspx?id=104113"

    Be aware, apparently this adapter requires SQL Server 2019 and latest update for SQL Server 2019, CU15.  I have not tested this

    "https://support.microsoft.com/en-us/topic/kb5008996-cumulative-update-15-for-sql-server-2019-4b6a8ee9-1c61-482d-914f-36e429901fb6"

    Pete

  • Have you contacted Microsoft about this issue?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Does this behavior occur only with upgraded packages? Or does it affect all connections made using the new oracle source? even in a new project created in SSDT 2019?

     

  • "We have raised this issue with Microsoft and are not looking for other assistance but given the potential impact I thought we had best share that  the problem exists."

    Pete

  • "Subsequently we have found this is not just an upgrade issue, it can impact any Oracle data source based on the new component."

    In other words yes, any project including a new SSIS 2019 project

    Pete

  • And an update: Microsoft have released a fix for this bug, Microsoft Connector for Oracle V1.2 from the Microsoft Download Center

    "https://www.microsoft.com/en-us/download/details.aspx?id=104113"

    Be aware, apparently this adapter requires SQL Server 2019 and latest update for SQL Server 2019, CU15.  I have not tested this

    "https://support.microsoft.com/en-us/topic/kb5008996-cumulative-update-15-for-sql-server-2019-4b6a8ee9-1c61-482d-914f-36e429901fb6"

     

     

     

    Pete

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply