DevOps, CI/CD and Automation

The Oracle driver for Visual Studio has two parts. Part "A" you install on your notebook or desktop. Part "B" has to be installed on the server... and we have not installed the 2nd part on the server. Why? Because we do not know who to itstall it as (SYS, SYSTEM... etc). So my DBA has not installed it and we can't find this information anywhere.

While in SSIS I am still able to see tables and columns and one would guess the driver is working. Symptoms of the failure of not installing all of the components on the 2nd part of the Oracle Driver Installation might be why we are experiencing these issues.

So, I want to ask each of you if you did install part "B" of the installation on the server where your database resides?

We did NOT install Part "B" of the installation when the wizard completed and I have been having many symptoms appear and I will number them below:

1. On an SSIS OLEDB SOURCE transformation I can see columns but not "Preview" the data.

2. On an "OLEDB COMMAND" transformation using an Oracle connection the table name is in a "DELETE FROM TABLE WHERE COLUMN = ?" command statement within that transformation. The same usage of the OLEDB Command works with in the opposite direction when SQL Server is the connection within the OLEDB Command. The error given through SSIS is the name of the table in this OLEDB COMMAND transformation is not recognized when connecting to Oracle but it does recognize the table name when a SQL Server table is used and a SQL Server Connection is used. The Oracle table name is valid and the Oracle Connection is valid and both are functional in other SSIS transformations.

3. Oracle Table PRIMARY KEY CONSTRAINT failure. Believe it or not, I can insert multiple rows using SSIS into an Oracle table and the Primary Key Constraint is failing to tell SSIS that there is a problem. I sent 6.1 million rows back to the same table in Oracle they just came from with no constrait violation as I would have expected. It basically broke the table and indexes and had to truncate it to use it again. We are getting no messaging from Oracle on the primary key constraint so they keep pouring into the table causing duplicate rows. This violates every principle of Primary Key Constraint!

4. I have an Oracle Stored Procedure that I want to load from a file. To load it from a file, I use an SSIS tranformation called Execute SQL Task in SSIS. It is 1,300 lines of pure Oracle code that has no comments in it and no spaces between lines. It compiles fine in TOAD. It fails in the SSIS Execute SQL Task. The number of lines prohibit me from loading this into the task itself so I load the code from a file on my notebook. All things are good until the script sees the word "EXIT" in the line of code that says "EXIT WHEN BKEY_CUR%NOTFOUND;". I know it is the word "EXIT" becuase SSIS fails when I say "EXIT WHEN 1=1". When I remove the line with "EXIT" SSIS sends the code to Oracle and it compiles just fine. When SSIS fails I get the error message:

SSIS package "C:\Users\lmdraper\Desktop\_SSIS\DEV\DW1\DW1\_DBA_CREATE_ORACLE_OBJECTS.dtsx" starting.

Error: 0xC002F210 at CREATE OR REPLACE PROCEDURE BANINST1 UALRDW1_REBUILD_RPRATRM_PRC, Execute SQL Task: Executing the query "" failed with the following error: "". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: CREATE OR REPLACE PROCEDURE BANINST1 UALRDW1_REBUILD_RPRATRM_PRC

Warning: 0x80019002 at _DBA_CREATE_ORACLE_OBJECTS: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "C:\Users\lmdraper\Desktop\_SSIS\DEV\DW1\DW1\_DBA_CREATE_ORACLE_OBJECTS.dtsx" finished: Failure.

The program '[18996] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

This isnt because my code is bad. I believe it has everything to do with this OLEDB driver I downloaded from Oracle for Visual Studio back in July and we did not complete part "B" of the installation. Did any of the rest of you do it on the server when your database lives?

5. With only Part "A" installed you will be able to connect to your database and see tables and columns from withing Visual Studio and SSIS as I am using... but you may be experiencing one of the above 4 problems as I am. Without Part "B", in SSIS I am able to use the connector to Oracle as a data source, move data to and from SQL Server and one might feel content that they dont need Part "B" of this installation until they experience failure to report from Oracle to SSIS a constraint violation, or the inablinity for SSIS to recognize the name of a table inside an SSIS OLEDB COMMAND transformation, or be able to load a stored procedure creation script to "create or replace a stored procedure" that has the word "EXIT" in the context of the script (which causes that failure) within an SSIS Execute SQL Task after loading it from the file through a file connector in the connection manager. It is acting as though there is messaging that an OLEDB driver needs that is not being communicated properly.

This is Part "B" of the installation I am referring to:

While installing the OLEDB driver, the message on the Oracle Developer Tools for Visual Studio - Installation Wizard says:

Installation Completed

To comnplete the ASP.NET provider setup, run the SQL scripts in C:\Program Files(x86)\Oracle Developer Tools for VS2017\ASP.NET\SQL directory on the provider's target database server.

Then the Oracle Logo is on the left of the screen followed by:

Sample Oracle network configuration files have been copied to C:\Program Files(x86)\Oracle Developer Tools for VS2017\Network\Admin. These are a template to create and configure database connection aliases.

NOW MY QUESTIONS:

• Does anyone know what "user" you should be in the database to run these scripts?
• Has anyone run these (part B) scripts and had success with previewing their Oracle Data?

WANT TO ESTABLISH:

• If any of you are still experiencing any of the numbered issues and have installed Part "B" of this installation, please drop me a note and tell me what "user" was used for this installation on your server. Perhaps we can rule out what not to do bases on your experience.
• If any of you have none of these issues, please drop me a note about which "user" you used to install Part "B" of this installation.

Thanks everyone!