Post reply

cannot load data from oracle to sql server 2005 using SSIS

  • October 23, 2010 at 2:15 pm

    #240131

    Hi all,

    I have made connection to oracle and sql server task is to load data from oracle to sql server. testing connection is fine to both i am able to view data from oracle connection. Connection is using only one table and that is readonly permission in oracle. i have setup the variable for connection string with sql authentication login. Every thing seems to be fine, mapping has no error and warning. but when I execute the package it through error on oracle connection manager. I have no clue what seems to be a problem. I tried both olaedb provider for oracle. Does any one has same issue and way to make correction. Please i need help.

    Thanks

    Sagar

  • October 23, 2010 at 2:47 pm

    #1241305

    If you are running this package in the dev environment fine the "output" window and you should see the full text of the underlying error.

    Also can you confirm if the error was on the source or destination connections?

    MIke John

  • October 23, 2010 at 2:58 pm

    #1241307

    What do the error messages say?

  • October 23, 2010 at 3:00 pm

    #1241309

    Thanks for the reply john.

    error was on source connection which is oracle, i have used olaedb provider for oracle. package is running on same instance where sql server 2005 is.

  • October 24, 2010 at 1:44 am

    #1241322

    Without the error message we are all guessing. Can you post the entire content

    of the window with the error messages?

  • October 24, 2010 at 7:59 am

    #1241330

    Hi,

    here is the error and connection string that had passed in variable.

    [OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.

    The AcquireConnection method call to the connection manager "ONECMSE.rc_read" failed with error code 0xC0202009.

    There may be error messages posted before this with more information on why the AcquireConnection method call failed.

    [DTS.Pipeline] Error: component "OLE DB Source" (1) failed validation and returned error code 0xC020801C.

    [Connection manager "ONECMSE.rc_read"] Error: SSIS Error Code DTS_E_OLEDBERROR.

    An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available.

    Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

    connection string in variable:

    I tried these both

    1:Data Source=ONECMSE;User ID=*****;Password=*****;Provider=OraOLEDB.Oracle.1;Persist Security Info=False;

    --- i also try that security info= true no luck

    2:Data Source=ONECMSE;User ID=*****;Password=*****;Provider=MSDAORA.1;Persist Security Info=True;

    Thanks

    Sagar

  • October 24, 2010 at 5:30 pm

    #1241384

    Any suggestion?

    Thanks

    Sagar

  • October 24, 2010 at 7:09 pm

    #1241388

    it is resolved. actually i just needed to change debug mode in project for runtime64 bit to false.

    Now it is good. 🙂

    Thanks

    Sager

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply