Pre-process to check record count in source

[sam93]

Hi,

I have an ETL process that will be executed daily. The
requirement is to make sure that data has been loaded in the source for this process for the previous day (in this case, a table in the staging area), before kicking off this ETL process.
I plan to do this by doing a record count in the source for that date. If the record count is greater than zero, then the ETL process should be kicked off, otherwise it should be skipped.

The question is what is the best way to accomplish this? Should this logic be built within the mapping or in a separate task that could then be included in the workflow?

Thanks.

 

[blom0344]

Probably more than one way to accomplish this.
My 2 cents:

Use one source qualifier that loads the distinct date from the staging table and by means of an aggregate expression calculate the highest date value (or write an override sql to accomplish this)
In the same mapping read data from staging table in the second source qualifier and pass only data that conform to sysdate-1.
Now join these 2 flows over the date.

If data exists that belongs to yesterday, than data will be processed. If no data exists in staging table, than no data will be loaded and the job will succeed very quickly with zero rows loaded.

Ties Blom