Table name format changed for Oracle Destination moving from SSIS 2014 to 2019

user_12974820 0
Viewed 50

We have a server that is end of life with SQL 2014. We migrated the SSISDB from 2014 to 2019 and it seems to have worked (we did upgrade the SSISDB and it matches the 2019 version).

Packages can be executed, deployed, imported to local machines.

Some of the packages were using the Oracle Destination and Oracle Source. The Oracle source seems to work fine. Some of the Oracle Destinations are not working.

It appears that the previous version had the format schema.table and that says table not found. If in the Oracle Destination Editor, in the name of the table drop down, if I click on lookup table, they now show as "schema"."table". Once I select the table with this format, it works.

So my question is, is there a way to mass convert these table names, or am I stuck with having to re-select each table in each destination?

Jeffery

ssis oracle sql-server
1 Answers

IF you import the packages from the SSISDB into an Integration Services project in SSDT or Visual Studio with the correct project types installed, you can edit the dtsx files (which are simply just XML files).

You can open and edit these files in any text editor, where you can use either some XML/XPath magic to update, RegExp or simple text replace.

Before you begin, be sure, that you have a backup of all files in a safe place.

I personally don't have an Oracle source, but you should look for something like this in the dtsx files:

<component
              refId="Package\(DFT) Load Data\(DST-OLEDB) Load Data into Destination"
              componentClassID="Microsoft.OLEDBDestination"
              contactInfo="OLE DB Destination;Microsoft Corporation; Microsoft SQL Server; (C) Microsoft Corporation; All Rights Reserved; http://www.microsoft.com/sql/support;4"
              description="OLE DB Destination"
              name="(DST-OLEDB) Load Data into Destination"
              usesDispositions="true"
              version="4">
              <properties>
                <property
                  dataType="System.Int32"
                  description="The number of seconds before a command times out.  A value of 0 indicates an infinite time-out."
                  name="CommandTimeout">0</property>
                <property
                  dataType="System.String"
                  description="Specifies the name of the database object used to open a rowset."
                  name="OpenRowset">[schema].[Table]</property>
                <property
user_34693910
Related