This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it
Scenario.
I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.
To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.
SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry
Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.
How do I get this working - can't seem to get my head around how I can compare the 2 dates ?
Derived column transformation has DateDiff function.
or
Script Component, you can write your own function in vb.net
|||Use the precedence constraints in the control flow. Use Execute SQL tasks to get your dates, and then hook them up to a sequence container which houses your data flow(s). The precedence constraint can perform your date comparison.|||Hey Phil,
Thanks for that... you just confirmed what I had gradually worked out for myself!!
My solution was.....
I created 2 Execute SQL tasks. One to get the source database date, the other to get the "next date" due for loading into the DW and joined them to the first execute package task. But, between the final Execut SQL task & the package task I created a precedence "expression" constraint as follows
@.[User::MasterPackage_vLoaddate]== @.[User::NextLoadDate]
which were the 2 variables assigned from the Execute SQL tasks. On success, the remaining packages run but on failure the job fails.
It works great, so thanks for confirming that....
|||I appreciate that you tried this on your own and got it to work. I also appreciate that you posted your solution for others to use.Glad it worked out for you!
Thanks,
Phil
