none
Error encountered while creating OCI environment - NEED SOME HELP PLEASE !!! RRS feed

  • Question

  • Hello,

    I have posted the below problem to Attunity Forum already.  I thought I would post it here as well since, at this point, I am sure if this is an SSIS, MSSQL or the Microsoft Oracle Connector (by Attunity).  If anyone has experience with resolving my error, PLEASE HELP!!  Any kind of ideas/suggestios is appreciated because I am running out of ideas.

    Thank you

    -----------------

    http://forums.attunity.com/forums/microsoft-ssis-oracle-connector/error-encountered-while-creating-oci-environment-3196.html

    Hello,

    Some background info:

    - Windows 2012 Server R2
    - Oracle client x32 11gR2
    - Running SSIS x32 DTExec utility
    - Microsoft Oracle Connector x32 2.0

    The package I have connects to Oracle 11gR2, pulls data and loads them into MSSQL 2012. The package runs
    perfectly in SSDT (aka BIDS) as well as executing it from the command line calling Microsoft x32 DTExec.exe. However, when the job
    is invoked from the SQLAgent as a scheduled job, it keeps failing with the following error:


    Error: 2016-04-03 10:34:52.99
    Code: 0x000002C0
    Source: Load_Expense_to_SQL Connection manager "WHSE"
    Description: OCI error encountered. Error encountered while creating OCI environment
    End Error
    Error: 2016-04-03 10:34:52.99
    Code: 0x0000020F
    Source: Execute the Oracle SQL Oracle Source [74]
    Description: The AcquireConnection method call to the connection manager WHSE failed with error code 0x80004005. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
    End Error


    WHSE is my Attunity Oracle connector connection name just as an FYI. It's also the name of my TNS. The SQL Server Agent runs under a domain\sqlagent account. I even logged on to the server as "domain\sqlagent" and executed the x32 DTExec commaind line, it worked perfectly. So this service account clearly has enough permissions to run the job. I also see my Oracle x32 oracle home (C:\app\oracle\product\11.2.0\client_1\bin) as part of the PATH. I have read a lot of posts about ensuring the Oracle Home is part of the PATH. Well it's there.

    Again, I have successfully executed this SSIS using various methods BUT SQL Agent. I created a BAT file and ran it as Windows Scheduled Task. But I
    can't get this to work in SQL Agent job running this same BAT file.

    Can someone please help?


    Thank you.

    Sunday, April 3, 2016 3:38 PM

All replies

  • Hi Sidney,

    Tick on the use 32 Bit mode for the SQL Agent:


    Arthur

    MyBlog


    Twitter

    Sunday, April 3, 2016 7:07 PM
    Moderator
  • Hi Arthur,

    I was hoping it would work.  Unfortunately it still failed with the same error.  Below is entire error message from "View History" in SQL Agent.  Note I intentionally change my Prod domain to XYZ.

    I am aware that SQLAgent runs in x64.  In my original test, I actually created this job to run "Operating System (CmdExec)" as type.  Then in the command line, I pointed to the x32 DTExec like this:

    C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtexec.exe

    So, although SQLAgent starts in x64 but it reality, it runs the x32 SSIS.

    This is driving me crazy!  I have been spending lots of time on the Web and have yet to find a solution.  If you (or anyone) has more ideas, PLEASE POST!

    Message
    Executed as user: XYZ\sqlagent. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.5058.0 for 32-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  3:49:24 PM  Error: 2016-04-03 15:49:25.50     Code: 0x000002C0     Source: Load_Expense_to_SQL Connection manager "WHSE"     Description: OCI error encountered. Error encountered while creating OCI environment  End Error  Error: 2016-04-03 15:49:25.50     Code: 0x0000020F     Source: Execute the Oracle SQL Oracle Source [74]     Description: The AcquireConnection method call to the connection manager WHSE failed with error code 0x80004005.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2016-04-03 15:49:25.50     Code: 0xC0047017     Source: Execute the Oracle SQL SSIS.Pipeline     Description: Oracle Source failed validation and returned error code 0x80004005.  End Error  Error: 2016-04-03 15:49:25.50     Code: 0xC004700C     Source: Execute the Oracle SQL SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2016-04-03 15:49:25.50     Code: 0xC0024107     Source: Execute the Oracle SQL      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  3:49:24 PM  Finished: 3:49:25 PM  Elapsed:  0.797 seconds.  The package execution failed.  The step failed.


    Sunday, April 3, 2016 7:58 PM
  • After some online search I am able to see the following resolution to an issue looking the same you posted at

    http://www.mssqlforum.com/threads/ssis-attunity-connector-for-oracle.24912/

    "As it turns out, this was caused by me installing only the "InstantClient" installation of the Oracle Client Installer. Switching to the full (Administrator) version fixed the issue and allowed me to connect successfully."

    OracleClientInstall.png


    Arthur

    MyBlog


    Twitter

    Monday, April 4, 2016 2:17 AM
    Moderator
  • Arthur,

    I saw that post before when I was researching my problem.  I installed the Oracle client using the "Runtime" option not the "InstantClient".  I could try installing using the "Administrator" option but I am not convinced yet it would resolve my problem simply for the fact that I am able to execute the x32 bit SSIS from the command line as well as within BIDS unless the SQL Agent needs "additional" stuff which is part of the Oracle client using the Administrator option.

    Monday, April 4, 2016 12:54 PM
  • I proceeded to install Oracle Client x32 using the Administrator option on another Windows 2012 R2.  I ran the SSIS and it failed with the same exact error.  I am really running out of options now.

    Monday, April 4, 2016 6:28 PM
  • I noticed it uses XYZ\sqlagent to run the job-package, this is the issue in my view.

    It needs to be a proxy account.


    Arthur

    MyBlog


    Twitter

    Tuesday, April 5, 2016 12:56 AM
    Moderator
  • Arthur,

    If you read my first reply to you (the one with the error), I noted I intentionally removed the real domain in these postings and put XYZ there.  Could you tell me why it needs to run under a proxy account?  I will read up on it as well. Thanks

    Tuesday, April 5, 2016 12:30 PM
  • Because the sqlagent account by default has nearly 0 rights over most things and it prevents the Attunity from working. I bet you did not test with it on your machine.

    Arthur

    MyBlog


    Twitter

    Tuesday, April 5, 2016 1:57 PM
    Moderator
  • Have you rebooted since installing the Oracle client?  The oracle client loads oci.dll from the path, so the location of oci.dll needs to be in the system path.  And Windows Services will not see the change to the system path until you reboot.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, April 5, 2016 2:02 PM
  • Arthur,

    I did all of my development on my local box Win7 Prof box.  The process worked within BIDS as well as running the x32 bit dtexec.  So things worked locally.  I then imported the package to SQL Server 2012.  Created an agent job to run the SSIS.  This is where the error started and spent time researching the issue since last week.

    David,

    A reboot is not required.  If you read my original post, I mentioned I logged on to the server using the domain\sqlagent account and successfully executed the package from the command line.  I even did a PATH at the prompt and saw the Oracle client home path there.

    Arthur is absolutely correct.  I needed to create a proxy account in order to fix my problem.  I created a proxy pointing back to domain\sqlagent and granted the proxy to access Integration Services.  This is what I am still unclear.  Why did it work when I logged on to the server as "domain\sqlagent" and ran the x32 dtexec and not from an agent job which uses domain\sqlagent and the job also specifically configured to run x32 dtexec?   Obviously my issue was permission related but just don't understand why I need a proxy that points back to domain\sqlagent??  Appreciate if you guys can shed some light on this.


    Tuesday, April 5, 2016 2:54 PM
  • "A reboot is not required.  If you read my original post, I mentioned I logged on to the server using the domain\sqlagent account and successfully executed the package from the command line.  I even did a PATH at the prompt and saw the Oracle client home path there."

    A reboot is required.

    Windows programs don't load their PATH at startup from the registry.  Instead they inherit the PATH of their parent process.

    When you change the system path in the registry, Windows Explorer (the desktop) has a special hook that reloads its PATH environment variable from the registry.  Then any program you launch from Explorer will see the updated PATH.  Window Services don't do this, and will only see the PATH as it existed when the service was launched from the Service Control Manager at startup. 

    David


    David http://blogs.msdn.com/b/dbrowne/


    Tuesday, April 5, 2016 3:10 PM
  • ...  I needed to create a proxy account in order to fix my problem.  I created a proxy pointing back to domain\sqlagent and granted the proxy to access Integration Services.  This is what I am still unclear.  Why did it work when I logged on to the server as "domain\sqlagent" and ran the x32 dtexec and not from an agent job which uses domain\sqlagent and the job also specifically configured to run x32 dtexec?   Obviously my issue was permission related but just don't understand why I need a proxy that points back to domain\sqlagent??  Appreciate if you guys can shed some light on this.


    This is because of the account rights, security limitations. And do not point to sqlagent. You need a domain account local or AD wide and assign the proper rights to it. The SSIS step at least needs to run using this account to overcome the aforesaid security limitations of the 'sqlagent' account.

    Arthur

    MyBlog


    Twitter

    Tuesday, April 5, 2016 3:14 PM
    Moderator
  • David,

    I understand your explanation now and it makes sense.  It also becomes clear now why I got the "OCI error encountered. Error encountered while creating OCI environment".  I knew it was looking for "oci.dll" and I knew it resides in ..\BIN of my Oracle Home.  I think the test I did by logging onto the server using the domain\sqlagent account to run the x32 dtexec was not a good test because it could "see" the oracle path.  So I rebooted the server and removed the proxy just to run a quick test and IT WORKED !!

    Arthur,

    I do realize it's best practice to use a proxy account to run SSIS job.  So I will be including this as part of this fix even though is not needed now.  But I will work on it and come up with a good domain credential and strategy.

    Thank you to both as you helped resolve my issue.

    Tuesday, April 5, 2016 6:38 PM
  • Arthur,

    I tried to implement a new proxy account to run the above SSIS but received this error:

    The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'Load_Expense_to_SQL' or a member of the sysadmin role may create new versions of it

    This what I did:

    1. Asked network admin to create a new domain account, say, domain\sqlssis.

    2. Created a credential & proxy to use to use domain\sqlssis.

    3. Created a job & a step to run the SSIS stored in MSDB.

    4.  The job step "Run As" is using the proxy created in Step2.

    The only quick workaround to make it work is I had to grant domain\sqlssis to be member of sysadmin on the database instance I am running.  This doesn't sound like a the right solution.   Do you have any idea what that error mean and how would I go about resolving it?

    Thanks in advance

    Tuesday, April 5, 2016 11:58 PM
  • Looks like the fully qualified domain name is needed. See https://mattsql.wordpress.com/2014/01/06/quick-tip-could-not-load-package-xxx-because-of-error-0xc0014062/ for reference.

    To run packages the proxy needs db_ssisoperator right granted only.


    Arthur

    MyBlog


    Twitter

    Wednesday, April 6, 2016 2:01 AM
    Moderator
  • II read your link.  My error is not a "Login Timeout".   It's "The LoadFromSQLServer method has encountered OLE DB error code 0x80040E14 (Only the owner of DTS Package 'Load_Expense_to_SQL' or a member of the sysadmin role may create new versions of it".
    Wednesday, April 6, 2016 12:59 PM