January 7, 2009 at 5:03 pm
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 = 2
IF Status = 0
Do nothing
-- Second SSIS task, executed after the first SSIS task.
IF Status = 2
transfer records from Stage -> Final
Change Status = 3
IF Status = 1
Do nothing
Then 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?
January 7, 2009 at 7:57 pm
Where is the status field? Is it in a SQL Server table?
I understand your logic about importing:
1) Needs to be extracted from Oracle
2) Has been extracted from Oracle and is in staging table
3) Has been moved from staging to SQL Server
As you are finding, this works fine ... once. The important question is: after a record has been imported from Oracle, what needs to happen before it needs to be imported again? That is, what is the trigger that sets the status back to 1?
I would consider removing status 2: it is obvious (from a quick query) that everything which is in staging is status 2.
If you can get to the stage where you have a 'needs to be updated' Boolean flag (or logic which equates to True/False), that's where I would head.
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 8, 2009 at 11:52 am
Where is the status field? Is it in a SQL Server table?
it's in a table called log where I keep track of the time and downloadStatus
what needs to happen before it needs to be imported again?
Need to do some transformation and conversion.
what is the trigger that sets the status back to 1?
I do that using an UPDATE statement after it is finish downloading.
1)Insert new record to table LOG
2)Oracle to stage successfully load and I set the columns Status to 1 and lastSuccessfullExtractTime to a default date as '2009-01-08 12:30:00.000' so it increases by day.
3)Stage to Final table successfully loads and I set the columns Status to 2.
I have a "Precedence Constraint Editor - Expression" enable where it checks the downloadStatus.
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.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply