Hi all The company that I am working for wants to change and redevelop all of its ETL they want to have a new SSIS package with all the whistle and bells, each ETL has mainly 2 steps, 1- the package 2- few SP (stored procedures) that are been fired off by a SQL Job. What they are looking for is how other developer implements their Notification systems in their packages and SP’s; I am just collecting suggestions and experiences What is do is 1- If my package fails I send an email for the package 2- In the SP I keep a log on the number of records in and out and etc... and send an email 3- I set my Job to send emails if they fail 4- Configuration in sending or not sending a notification 5- Etc.... What do you do in your ETL to notify the user ? Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

I've built an SSIS Framework which every package is based off of that does the things you are suggesting (also keeps track of execution times, average and medians for time and row counts, what tables were affected, etc). You can look through the microsoft data warehouse toolkit and the etl toolkit from the kimball group to find the majority of the implementation that I did (as well as examples from project real). I've also added some data quality assessment into the mix with tests from the etl tollkit and nomenclature from data quality the accuracy dimension (Jack Olson). But, for the few companies I have worked for the data quality section has remained as a "nice to have in the future" item and has not been relied upon. However, were I to be starting over today without already having developed this I would be using the BI Xpress tool from pragmatic works as it appears to do everything you are asking for...Please mark answered posts. Thanks for your time.

can you provide me the links to Kimball and the rest of the people , thanksSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Nearly everything I mentioned was a book... So, the following are links to amazon.ETL Toolkit Microsoft Datawarehouse Tookit Data Quality: The Accuracy Dimension This is a microsoft whitepaper: Project Real I've got the slidedeck for one of my presentations up on the sqlsaturday website which talks about my framework. If I remember correctly the file naming was screwed up and you need to add a ".zip" to the end to have it recognized correctly. Also, some of the stuff I mention in the slide deck I'm not sure I would do the same way anymore, but it might give you a good start. I also have a few blog posts on the subject from around April 2009 . I know that Andy Leonard has similar presentations as do Rafael Salas, Jessica Moss and Rushabh Mehta. Jamie Thomson has several articles on his old EMC blog that talk about methods I've used (such as the "framework folder" and building variables dynamically, etc). Really, this has been a pretty common theme over the last several years.Please mark answered posts. Thanks for your time.

Thanks Eric I votedSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).