Hi All,

I have designed a SSIS package which archives historical data from LiveDB to ArchiveDB.

Archival is done on department basis, each dep lead is given choice of how much amount of live data is required. If the LEAD selects '100'. Only 100 days of data is stored at LiveDB and other data is moved to Archival. All this info is stored in a table and dep LEADS have access to UPDATE the values. SSIS PACKAGE refers to this value while archiving.

However I would keep all the DIM tables in sync with LiveDB and ArchivalDB which would be a simple PROC.

This Package only archives FACT tables, all the FACT tables are kept in a SEQUENCE Container such that they run in parallel.

My Organization wants a third party which validates the outcome of the package sent a mail to DBA's about the archival status.

Validations like:

It should trigger the package at a point of time between 1AM and 5AM as per we schedule.

How many records are INSERT(ed) from STAGING table to PROD table.

How much data is loaded into FLAT files using BCP QUERYOUT and how much did the BULK INSERT process load the data into Archival DB.

It should keep a note of the failed process TABLEs and should include in the mail which tables have succeeded and failed.

There should be a choice to select or run only the failed tables manually.

We would appreciate if there is any option to auto-trigger the package to execute only the failed STEPS/ TABLEs on next day at the same time.

Thank you, please let me know if you require any other info.