Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
 Posted - 2009-01-07 : 13:59:04
|
| Trying SSIS to import data from Oracle to stage and from stage to Final. I'm working out in the logic part. I'm using a column status to download the records and I'm working on the logic part which is not doing what I want.-- First SSIS task.IF Status = 1 transfer records from Oracle -> Stage Change Status = 2IF Status = 0 Do nothing-- Second SSIS task, executed after the first SSIS task.IF Status = 2 transfer records from Stage -> Final Change Status = 3IF Status = 1 Do nothingThen the next day the job gets executed again. But the status is now 3. If I change the status to 1 after the second task. It wont get the right number of records if I wanted to execute the job 2,3,n number of times.It also doesnt work if the status = 3 as the next day the IF statement is looking for status = 1. How could I solve this? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-08 : 02:43:26
|
| whats the purpose of this Status bit? are you trying to get incremetal data? then you've task called slowly changing dimension tasks which does this. or you can even add a audit column like datemodified and use it to determine incremental data. |
 |
|
|
basicconfiguration
Constraint Violating Yak Guru
358 Posts |
Posted - 2009-01-08 : 13:56:15
|
| Yes it's a incremental download.If I re-run the package Oracle to Stage table won't download any records. But then Stage to final table process starts moving the records. I don't want this to happen. It's a vicious loop. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-09 : 08:56:36
|
quote:
Originally posted by basicconfiguration
Yes it's a incremental download.If I re-run the package Oracle to Stage table won't download any records. But then Stage to final table process starts moving the records. I don't want this to happen. It's a vicious loop.
either add a datemodified column to your oracle table and use it to determine changed data. Use a trigger in your table to automatically change column value to date when it was modified. You can use a log table in sql server and save the latestdate value from oracle table during each package run. for next run, you retrieve max date from log table and use it to determine changed data ( all changed data will have datemodified value greater than this) |
|
|
|
|
|
|
downloading records with ssis