SSIS Ole DB Connection hangs without error

0
votes

I am working on an SSIS project in Microsoft SQL Server Data Tools for Visual Studio 2017. We have to load the data from an Oracle 12C database, onto an SQL Server 2017 database, using the OleDB Source component. This works fine if we executethe packages from Visual Studio, however, once we deploy it to the SQL Server, and execute it from there, the package hangs for an indefinite amount of time, once it reached the DataFlow task. The last two SSIS Execution messages we receive are:

OnPrevalidate: package_name: Validation has started.
OnInformation: package_name: Information: Validation phase is beginning.

The package does not proceed further, and stays in Running status permanently. We have the same ODAC(64 bit) component package installed on both our developer computers, and on our SQL Server.

We have also tried to use the ADO.NET source, which was fully functional, but it lacked the "Query from variable" feature, and its query builder does not recognize the query as a valid syntax either (claims || and such are errononous), so we can not parameterize it either.

We have spent several hours looking for people with similar issues, but sadly could not find any that replicated our problems.

Thank you for your replies in advance!

2
sql-serverssis
Have you tried disabling the ValidateExternalMetadata property of the OLE db source? If you open up the Properties of the Connection Manager you're using for your Oracle connection, what is the ConnectionManagerType? Also, what edition of SQL Server 2017 are you using? - digital.aaron
We did try that, it did not work. The version is 14.0.17289.0, connection manager is "Native OLE DB\Oracle Provider for OLE DB". - Akos Balazs

2 Answers

0
votes

We found out the issue: we did not have the 32 bit driver installed. Which is strange, because both our SQL Server and the Oracle server were 64 bit, and the SSIS packages were set to 64 bit mode. At least, the loaders work now :)

EDIT: We also had an issue with the 32 bit version: it had not enough memory, as the 32 bit version has a cap of 2 GB. We had a weird workaround to this: we uninstalled both ODAC drivers, then we installed the 64 bit driver, followed by the 32 bit driver, and finally, the 64 bit again. For some reason, it works now in 64 bit mode. Really, really strange.

0
votes

I was having a problem with an SSIS Oracle data source hanging at about the exact same number of rows each time I tried to run it. After a bunch of trial and error I realized that one of the columns was a CLOB. I put a dbms_lob.substr on it and the extract ran perfectly. I'm guessing the Oracle data provider has some sort of problem with that data type because it's not a lot of data and the query performs fine.