Post reply

Please Help, has no one done this before???

  • August 22, 2003 at 7:49 am

    #106323

    Currently, I have a job that runs like this:

    1. Truncate table <tablename>

    2. Drop Index/Indices

    3. DTS Run (which is a straight copy of the table into SQL

    The problem is, this was written over two years ago (not by me) and it worked fine. However, today the table has approx. 50 million rows and I think you know where I'm going with this. Not efficient.

    Rather than truncate the table I would prefer to append the data. I have a binary file on the Oracle server (and an app which reads it) which keeps track of changes to the records, deletes, updates and inserts. Is there someway I can create a package that reads this file and determines what records need to be loaded?

    Thanks


    Aurora

  • August 22, 2003 at 8:06 am

    #470978

    Should be possible.

    You should be able to create the logic for reading the binary file from an ActiveX task.

    Insert the record ids (primary keys) inside a staging table and 'synchronise' everything using that staging table.

  • August 22, 2003 at 9:02 am

    #471001

    Great, but although I have written a few ActiveX task before, its never been to read a Binary file. Any advice or resources I can turn to for assistance?

    Thanks again,

    Christine


    Aurora

  • August 22, 2003 at 9:47 am

    #471008

    Try searching at http://www.msdn.microsoft.com for "file system object"...also might try running the same search on Google. Also maybe try searching this site for 'binary file'. There is lots of good info out there...

    hth,

    Michael

    Michael Weiss


    Michael Weiss

  • August 22, 2003 at 10:40 am

    #471032

    Oops. Apparently there is no easy way to read a binary file using VBScripting. Maybe using JScript it gets easier?

    An alternative solution could be to write your own object to read the file.

    Can you use the BULK INSERT or BCP to copy in the data? This might be possible using the NATIVE types...

  • August 22, 2003 at 1:21 pm

    #471058

    I could certainly use either of those options, but still unsure how to tackle reading the binary file.

    Ideas?


    Aurora

  • August 22, 2003 at 7:43 pm

    #471082

    Can you give the format of the file? Maybe upload a small sample, and I will try to do some testing...

  • August 24, 2003 at 4:12 am

    #471099

    Also have a look at http://www.sqldts.com/default.aspx

  • August 28, 2003 at 6:50 am

    #471787

    The binary file I speak of is simply the Oracle log. Is there anyway to read this so I can determine which records need to be loaded. Does anyone currently "append" new data to a SQL server dB table from an Oracle dB table?

    Thanks


    Aurora

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

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