October 16, 2002 at 2:59 pm
I have built a package where my source is Oracle and Destination is SQL DB. In this package while transformation I have written functions in Active X script, also I am calling my lookups queries in Active X Script.
Since I have lots of records. I am loading the data in chunks. The very first
time I am loading 3months data starting today(ex if today is 12/1/2002 then 3 months data=from 9/1/2002 - 12/1/2002) and then 3 months before that(which will be from 6/1/2002 - 9/1/2002) plus modified data in first 3 months (from 9/1/2002 - 12/1/2002).
I am not sure how should I achieve this since I want to do an incremental update for
already loaded data in a table?
Also, I am adding many additional columns in my destination table which contains values based on my Active X scripts so its not a direct copy of source.
Thanks,
October 16, 2002 at 8:05 pm
One way to handle the modified data is to keep track of when you first moved it and when it was last modified. Then you can move only the records that were modified since the last time they were moved.
Another way could be to keep an extra column in the source table. Use a bit data type and set it to 1 after you move the records. Anytime a record is modified set the bit column to 0. The next time your DTS package runs you only move previously moved records that have a 0 in your tracking column.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 17, 2002 at 4:29 am
Do you have type of field that would stamp a new date or as robert suggestions you can flag on the Oracle side to let you know the data has change?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 17, 2002 at 8:36 am
On Oracle side I cannot add any column but, Oracle side it has Modified Date column which can help me to track the modified records and also new records.
But my question is how should I implement that in DTS. Because right now I have built a package in which very first step is get 3 months data. Where and how should I implement within DTS to get any new or modified data? Do I need to use Data Driven Query task or what?
October 18, 2002 at 4:48 am
Actually on the Source tab of the transformation task you can code a query, just code to look at the modified date on the Oracle side so that you get your results. We pull all changes that have occurred in the last week even thou we do our update daily just for safety.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 18, 2002 at 8:36 am
Exactly thats what I want to do.
But the problem is lets say the very first time I load the data
Select * from ORACLETABLE where <Modified Date> <= (Select max <Modified Date> from ORACLETABLE)
This will ensure that any records created while the data migration process is being done would be ignored
This is fine. I am successful in this task.
Now I want to load New and Update any Changed data. Here is where I am stuck.
I want to do a query something like this
Select * from ORACLETABLE where <Modified Date> >= (Select max <Modified Date> from SQLDBTABLE)
Can I put this query in my Source Tab?
If yes how? Because the above query is based
on Source and Destination and Source tab is unable to understand Destination table.?????
Thanks,
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply