This is one of those errors which you have no idea how to solve, and you think that all is lost and there is nothing you can do about it. And then you decide to Google – and all is well.
Anyway,
this is how I ran into this error:
Scheduling a SSIS (SQL Integration Services) job with SQL Server Agent which involves connecting to Siebel backend (Oracle), I first had to change from SSIS job to CmdExec to use 32 bit version of DTExec (also required of you are using 32 bit Oracle Drivers – or any other 32 bit libraries in your SSIS). (Dont forget to add SERVICE_NAME(s) etc to tnsnames.ora file and point to it in system environment variable – TNS_ADMIN etc..). Everything looked good and ran it and got this error:
ORA-06413: Connection not open.
what does this error mean:
After searching online I found out that Oracle Provider cannot handle parenthesis in the file path for the apps connecting to the provider.
how did i solve the issue:
Originally my 32bit dtexec statement was:
“D:\Program Files\MICROSOFT SQL SERVER (x86)\90\DTS\Binn\DTExec.exe” /SQL “\BITS\[PackageName]” /SERVER [ServerName] /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E
Then I changed it to:
“D:\Program Files\MICROS~1\90\DTS\Binn\DTExec.exe” /SQL “\BITS\[PackageName]” /SERVER [ServerName] /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E
This resolved the issue but is just a temporary workaround. I didnt come up with the solution, I found had to search google, thanks to the following links:
More info:
http://phil-austin.blogspot.com/2008/08/ssis-and-oracle-providers-another.html
Hope this was useful!
Sameer
SammyQS
0 Responses to “Oracle Connection from SSIS – ORA-06413: Connection not open.”