Tag: SSIS

Posted on

Solving the ROW-00060 Error When Using SSIS with Oracle

SSIS logo

I have built SSIS packages to load data from one Oracle database to another before, but never had I come across this error until recently:

“ROW-00060: Internal error: [dainsert,16] Source: Oracle Destination: Oracle Error Occurred @ after 403k records.”

Initially, I thought that some constraint had been violated on the destination data source – like an attempt at a NULL value being inserted into a NOT NULL column. I ran the package a few times and noticed that the row count was always in the neighborhood of 400k and that the package had run for hours. I thought it odd that so many rows could be inserted with no problem, and so a double- and triple-checked to make certain that the constraints on the source columns were the same as those on the destination ones.

Though there were no clear cut solutions explaining exactly why this error happens, several different people on different fora mentioned that they had overcome this particular error (at different row counts, not always 400k!) by using a connector from Attunity rather than the one that comes with the Oracle Client. Fortunately, this driver is made to work with SSIS and is distributed by Microsoft. This driver not only works with Oracle, but also with Teradata. Choose the version based on the version of SSIS you are using.

Version 2.0 for SQL 2012
Version 3.0 for SQL 2014
Version 4.0 for SQL 2016

After installing the connector into Visual Studio and restarting VS, you should be able to use it by selecting “Oracle Source” and/or “Oracle Destination” from the SSIS Toolbox.

Attunity Oracle Source in SSIS

After setting up the new source and destination connections, I was able to run the SSIS package to completion, loading over five million rows of data in less than an hour!

Posted on

Quick Fixes on SSIS with Oracle Data Sources

SSIS logo

I’m currently in the process of building out an ODS (Operational Data Store) that will integrate data from multiple systems and make that data available for reporting using Spotfire and other BI tools.

Both the ODS DBMS and the first system that will be used as a source for the data in the ODS run on Oracle Database. I am using Visual Studio Ultimate 2013 64-bit to build an SSIS package to flow the data from the source into the ODS.

In debugging the SSIS package, I’ve run into a few things that make troubleshooting much easier. The first thing to know is that the Oracle Database (in this case) is a 32-bit system. The first thing that must be done after loading the SSIS package into Visual Studio is making sure that the Run64BitRuntime setting is set to False in the project’s Properties box. (This can be found in the PROJECT menu.)

Since the data sources for the ODS are Oracle, the SQL queries used to pull data must conform to Oracle syntax. To prevent things like SSIS automatically adding semicolons to the queries (and preventing me from adding them myself), I set the BypassPrepare property to True. This should allow the query to run the same way in SSIS as it does in SQL Developer. (If this is set to False, SSIS will parse the query rather than passing it to Oracle.) This setting made writing queries much easier, as I could test them in SQL Developer and paste them into SSIS without having to rewrite them to conform to SSIS.