Hi Don, my opinion is to employ the Precedence Constraint here. So, this is as follows: 1)Add a package variable (let's say it can be an Int or a bool); 2) Drag and drop the Execute SQL Task and craft a SQL query in it to get the data in form of 1 or 0 based on whether Max(source transactiondate) > Max(destination transaction date); 3) Add a DFT that does the load, connect the #2 to it and then change it to using the Precedence Constraint that would check for the value of the variable returned (say of the query gave 0 then do not proceed, do proceed otherwise). I will give you a blog post to read on how the mechanics look like when dealing with the Precedence Constraints: http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspxArthur My Blog

Hi Don, my opinion is to employ the Precedence Constraint here. So, this is as follows: 1)Add a package variable (let's say it can be an Int or a bool); 2) Drag and drop the Execute SQL Task and craft a SQL query in it to get the data in form of 1 or 0 based on whether Max(source transactiondate) > Max(destination transaction date); 3) Add a DFT that does the load, connect the #2 to it and then change it to using the Precedence Constraint that would check for the value of the variable returned (say of the query gave 0 then do not proceed, do proceed otherwise). I will give you a blog post to read on how the mechanics look like when dealing with the Precedence Constraints: http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspxArthur My Blog

You can alway have your own Auditing system, basically checking things like... PK, FK , validate dates (if date is in a string format), you can always to this by using one or more of the mentioned 1- within your SSIS while doing the transform 2- using SP (stored procedure) in SQL server when is the best time in doing the Auditing? well that depends if you are dealing with less than 1mil record you could say that you are OK in doing the auditing at anytime, but in my case ihad to deal with 20mil records with in few tables, so before the ETL i checked the interation of the data within the tables and each other and cleaned up the bad records, then i ran the ETL. good luck Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

You can alway have your own Auditing system, basically checking things like... PK, FK , validate dates (if date is in a string format), you can always to this by using one or more of the mentioned 1- within your SSIS while doing the transform 2- using SP (stored procedure) in SQL server when is the best time in doing the Auditing? well that depends if you are dealing with less than 1mil record you could say that you are OK in doing the auditing at anytime, but in my case ihad to deal with 20mil records with in few tables, so before the ETL i checked the integration of the data within the tables and each other and cleaned up the bad records, then i ran the ETL. good luck Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Hi There, I am wondering what is best way to do this. I have daily process copy data from source to destination, simple truncate and reload. There is date field in the table (transactiondate). I would like to add check before any processing, I guess, if Max(source transactiondate) > Max(destination transaction date), then start process, otherwise, do nothing. Basically I would like to make sure there is latest data avalaible before doing anything. Many thanksDon

I do something similar in the control flow. I retrieve the dates from each of the database and use a script task to compare their values (I also check a system flag I have setup to force it to run if I want to rerun the file regardless of times stamps) and set a user variable called "continue" to true. I use a constraint on the success flow of the script component to check the flag set in the script component and only continue if the flag is true:

You can use 2 execute sql tasks in your control flow to return the max date from each DB and then use those 2 values in the precedence constraint that moves to the next task. Chuck Pedretti | Magenic North Region | magenic.com

Hi Don, my opinion is to employ the Precedence Constraint here. So, this is as follows: 1)Add a package variable (let's say it can be an Int or a bool); 2) Drag and drop the Execute SQL Task and craft a SQL query in it to get the data in form of 1 or 0 based on whether Max(source transactiondate) > Max(destination transaction date); 3) Add a DFT that does the load, connect the #2 to it and then change it to using the Precedence Constraint that would check for the value of the variable returned (say of the query gave 0 then do not proceed, do proceed otherwise). I will give you a blog post to read on how the mechanics look like when dealing with the Precedence Constraints: http://blogs.msdn.com/b/mattm/archive/2006/10/30/using-expression-based-constraints.aspxArthur My Blog

You can alway have your own Auditing system, basically checking things like... PK, FK , validate dates (if date is in a string format), you can always to this by using one or more of the mentioned 1- within your SSIS while doing the transform 2- using SP (stored procedure) in SQL server when is the best time in doing the Auditing? well that depends if you are dealing with less than 1mil record you could say that you are OK in doing the auditing at anytime, but in my case ihad to deal with 20mil records with in few tables, so before the ETL i checked the interation of the data within the tables and each other and cleaned up the bad records, then i ran the ETL. good luck Sincerely SH -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

I do something similar in the control flow. I retrieve the dates from each of the database and use a script task to compare their values (I also check a system flag I have setup to force it to run if I want to rerun the file regardless of times stamps) and set a user variable called "continue" to true. I use a constraint on the success flow of the script component to check the flag set in the script component and only continue if the flag is true:

Thanks you all for insight and tips. Will test on it nowDon