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
January 20, 2022 at 10:34 pm
Have you contacted Microsoft about this issue?
--Jeff Moden
Change is inevitable... Change for the better is not.
January 21, 2022 at 1:46 am
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?
January 21, 2022 at 4:42 am
"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
January 21, 2022 at 4:44 am
"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