none
how can i fix this? works good in visual studio RRS feed

  • Question

  • I am having issues while executing my package in SSMS

    Package runs fine in Visual studio. I have a oracle as source and sql server destination as connection managers

    

    

    Message Type

    Action

    Message Time

    Message

    Message Source Name

    Subcomponent Name

    Execution Path

    OnError

    View Context

    7/3/2018 1:05:03 PM

    apurvatest:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80004005  Description: "ORA-12154: TNS:could not resolve the connect identifier specified".

    apurvatest

    \apurvatest

    OnError

    View Context

    7/3/2018 1:05:03 PM

    Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DWFEI.AAO8290" failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    Data Flow Task

    OLE DB Source [61]

    \apurvatest\Data Flow Task

    OnError

    View Context

    7/3/2018 1:05:03 PM

    Data Flow Task:Error: OLE DB Source failed validation and returned error code 0xC020801C.

    Data Flow Task

    SSIS.Pipeline

    \apurvatest\Data Flow Task

    OnError

    View Context

    7/3/2018 1:05:03 PM

    Data Flow Task:Error: One or more component failed validation.

    Data Flow Task

    SSIS.Pipeline

    \apurvatest\Data Flow Task

    OnError

    View Context

    7/3/2018 1:05:03 PM

    Data Flow Task:Error: There were errors during task validation.

    Data Flow Task

    \apurvatest\Data Flow Task




    • Edited by apsta Tuesday, July 3, 2018 6:49 PM
    Tuesday, July 3, 2018 3:36 PM

All replies

  • Hi,

    Please share the error message so we can understand your issue.


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, July 3, 2018 3:38 PM
  • What kind of issues

    Instead of just specifying issue it would help if you gave information on the error obtained/issue faced


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, July 3, 2018 5:47 PM
  • Package runs fine in Visual studio. I have a oracle as source and sql server destination as connection managers
    Tuesday, July 3, 2018 6:43 PM
  • Package runs fine in Visual studio. I have a oracle as source and sql server destination as connection managers

    Do you've the tnsnames.ora file with correct instance name specified?

    This usually happens when entry for the Oracle instance to connect is missing from tnsnames.ora file

    Since you say it works fine from VS,  my guess is that the executing account (SQL Server Agent service account if from job or logged in account if executing from IS catalog) may not have access to the tnsnames.ora file or may be if executed from server box, then server may not be having tnsnames.ora file with instance name specified


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Tuesday, July 3, 2018 7:14 PM
  • Hi Gouri apurva sthanam venkata sri sai,

    Please make sure following a few things:

    1. use Oracle Provider fro OLE DB instead of OLE DB for oracle in OLE DB Source.

    2. Oracle client and configuration are correct, you can log in Oracle Server via SQL PLUS normally.

    3. The runtime version is appropriate (32 bit or 64 bit depends on your version of driver) 

    4. The execution account has sufficient permission.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 4, 2018 2:58 AM
    Moderator
  • Hello,

    VS/SSDT is a 32 bit application and when it works, then because the 32 bit data Provider for Oracle is installed, but may be not then 64 bit Provider, which ic used when it runs as Agent Job. Either install 64 bit Oracle data Provider, or Switch the Job to run in 32 bit mode.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 4, 2018 5:30 AM
  • An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". ProjectPPE_LoadWorkdayData \ProjectPPE_LoadWorkdayData OnError View Context 7/5/2018 1:11:46 PM Execute SQL Task:Error: Failed to acquire connection "OLE_DST_FEIBUS_DEV1". Connection may not be configured correctly or you may not have the right permissions on this connection. Execute SQL Task \ProjectPPE_LoadWorkdayData\Execute SQL Task

    Messages

    on F00326\SSIS17_DEV1 at 7/5/2018 1:11:54 PM

    This report displays messages for the executions or validations.

    View Overview

    Execution Information

    Operation ID

    60271 (Execution)

    Name

    EIM\DWFEI.ProjectPPE\ProjectPPE_LoadWorkdayData.dtsx

    Environment

    -

    Status

    Failed

    Duration (sec)

    1.206

    Start Time

    7/5/2018 1:11:46 PM

    End Time

    7/5/2018 1:11:47 PM

    Caller

    DS\AAO8290

    Error Messages

    Message Type

    Action

    Message Time

    Message

    Message Source Name

    Subcomponent Name

    Execution Path

    OnError

    View Context

    7/5/2018 1:11:46 PM

    ProjectPPE_LoadWorkdayData:Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

    ProjectPPE_LoadWorkdayData

    \ProjectPPE_LoadWorkdayData

    OnError

    View Context

    7/5/2018 1:11:46 PM

    Execute SQL Task:Error: Failed to acquire connection "OLE_DST_FEIBUS_DEV1". Connection may not be configured correctly or you may not have the right permissions on this connection.

    Execute SQL Task

    \ProjectPPE_LoadWorkdayData\Execute SQL Task


    Thursday, July 5, 2018 5:41 PM
  • Hi Gouri apurva sthanam venkata sri sai,

    Seems like permission issue, try using proxy account which has sufficient permission to execute the package.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 6, 2018 6:59 AM
    Moderator
  • This error ORA-12154 is telling us that it can't find the TSN.

    Is the TSN configured on the server it self ?

    Friday, July 6, 2018 7:50 AM
  • Hi Gouri

    Make sure you add server/account/password info on "Connection Managers" in SSISDB projects.

    Tuesday, July 10, 2018 6:54 PM
  • If proxy account doesnt work , it can even be due to kerberos issue

    https://blogs.technet.microsoft.com/askds/2008/06/13/understanding-kerberos-double-hop/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, July 10, 2018 8:48 PM