Post reply

Loading data from SQL Server 2005 to Oracle 8i through SSIS

  • June 19, 2007 at 12:49 pm

    #117227

    Hi all, hope someone can help me out here.

    I am trying to build a SSIS package to load data from a SQL Server 2005 database to an Oracle 8i database. The databases and table structures are identical on both.

    I am using MS OLEDB driver for SQL Server and Oracle OLEDB driver for Oracle as MS drivers seem to not recognise unicode fields at Oracle database. To complicate things, I have only read/write access to Oracle database which is remote and at another company.

    Both the source and the destination are setup to use AccessMode = OpenRowset and AlwaysUseDefaultCodePage = True.

    The execution starts fine, the step shows progess up to the total number of records at source than it hangs forever. Here's a screenshot of how it looks like:

    Mostly when it fails, after a few hours, it shos the following error:

    DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 6 buffers were considered and 6 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

    Anyone have any idea what's going on?

    Thanks,

    Ardian

    Thanks,

    Ardian

  • June 20, 2007 at 2:26 am

    #714031

    Try reducing the buffer settings in the data flow slightly. (Reduced Performance though)

    How much memory you got in the machine?

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 20, 2007 at 7:02 am

    #714081

    Have you tried executing the process with just a few records? That way you would know whether it was the number of records or not.

    Regards,

    Carla Hepker
    Documentation: A Shaft of light into a coded world.

  • June 21, 2007 at 5:53 am

    #714307

    Crispin,

    I tried reducing teh buffer settings and also added 1GB of RAM on the my PC (I run the SSIS package from my PC inside VS 2005) but no progress. I even left it running overnight and this morning is still running.

    I can't figure out any way how to trace where the problem is.

    Any suggestion anybody?

    Thanks,

    Ardian

    Thanks,

    Ardian

  • June 21, 2007 at 6:01 am

    #714312

    Do you have the problem if you push to a SQL dest? What is the size of your row? Many big fields?

    Some trial and error:

    Create a small DF with 1000 rows, one column wide and see if that has problems. Increase it until you see the problem. At what point does the problem occur?

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • June 21, 2007 at 6:45 am

    #714326

    The table is pretty simple:

    CREATE

    TABLE [dbo].[cd_tbl](

         [cd_tbl_id] [numeric](7, 0) NOT NULL,

         [cd_tbl_name] [nvarchar](192) NOT NULL,

         [expl_text] [nvarchar](2560) NULL,

         [inac_ind] [dbo].[ind] NULL

    and it contains only 393 rows.

    The error happens during the execute step and the progress (number of records) shown on screen is the total number of records being transfered.

    I just realized I do not have SQL server 2005 SP2 installed so it is currently being downloaded. I'll see if it makes a difference.

    Thanks,

    Ardian

  • June 21, 2007 at 8:04 am

    #714354

    More info on the issue:

    Microsoft OLEDB Oracle driver identifies Oracle 8i unicode fields (Unicode string [DT_WSTR]) as non unicode (string [DT_STR]). For this reason, I can not directly map SQL Server 2005 unicode fields to Oracle fields.

    I identified to workarounds:

    1. Use a Data Transformation object to convert SQL Server 2005 unicode fields to non-unicode and then map them to the Oracle 8i fields using the Microsoft OLEDB Oracle driver. Unsing this approach I am able to copy the data over. However this is not acceptable as we lose accented characters (Canadian French data).

    2. Use the Oracle OLEDB driver to connect to Oracle 8i. The Oracle driver does recognise the unicode fields and allows me to directly map the SQL Server unicode fields to Oracle unicode fields. This approach is teh one causing the error I describe in the original post.

    Anyone has a suggestion?

    Thanks,

    Ardian

  • June 21, 2007 at 8:19 am

    #714372

    I have not used SSIS to push data to Oricle but pretty much sucks what you getting here. I cannot see or think of anything that is wrong.

    Having a scratch around, I think you looking at a bug in the the MS driver.

    See:

    http://sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx (See comments at the bottom)

    http://blogs.conchango.com/jamiethomson/archive/2005/11/15/2393.aspx (Link from Donald's post)

    Maybe try using a script component as a dest then using ODBC, insert the records into oracle. By no means ideal.

    have no more suggestions

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

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

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