Post reply

How to import newer records SQL using DTS?

  • December 10, 2006 at 3:42 pm

    #368256

    Hello,

    I am rather baffled as to how to import every day, newer records from an Oracle table using timestamps and DTS Transform Data Task.

    Example:

    The most recent record on the SQL Table has a field named TRANS_DATE with a value of ‘12/11/2006 6:15:18 PM'

    The Oracle table has several newer records created AFTER that timestamp. How do you tell DTS every day, to get the most recent timestamp from the SQL table and import only those records from Oracle created GREATER than that SQL timestamp using DTS?

    Is this possible or am I just dreaming?

    Regards,

    Tom

  • December 10, 2006 at 10:17 pm

    #677145

    You can create linked server call to oracle and use NOT EXISTS clause in your query...

    You can do this using tsql or dts...

    declare @dt datetime

    select @dt = max(TRANS_DATE) from sqltable

    insert into sqltable

    select * from oracle...tablename where TRANS_DATE> @dt

     

     

     

     

    MohammedU
    Microsoft SQL Server MVP

  • December 13, 2006 at 12:09 pm

    #677877

    Mohammed Uddin,

    Many thanks for your prompt and ecxellent suggestion!

    I entered the script as you suggested and immediately got an error message: "MSDAORA' reported an error. The provider ran out of memory"

    So I did a search it appears to be an OLD SQL bug. I have tried several fixes but none have resolved this error.

    I will keep looking to determine the root cause and solution.

    Thanks again for your help!

    Tom

  • December 18, 2006 at 9:49 am

    #678714

    You can create a global variable 'Transdate'

    First step create dynamic property, click 'add', then click on the global variable 'Transdate', press edit, change source to 'Query', then put in the query 'select Transdate from Sqltable'

    Second create a Active x script, I assume the

    Function Main()

    dim checkdate

    Dim oPKG

    Dim fso

    Dim tsk, cus, sql

     Set oPKG = DTSGlobalVariables.Parent

     Set fso = CreateObject("Scripting.FileSystemObject")

     checkdate = DTSGlobalVariables("Transdate").Value

     Set tsk = pkg.Tasks("DTSTask_DTSDataPumpTask_1")

     Set cus = tsk.CustomTask

     sql = "SELECT * FROM OracleTable WHERE oracledate > " & checkdate

     cus.SourceSQLStatement = sql

     Main = DTSTaskExecResult_Success

    End Function

     

Viewing 4 posts - 1 through 4 (of 4 total)

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