what we want out ssis framework

what we want out ssis framework

->portability,Meta Data/Traceability(Process,data),ERROR and Exception Handling,Recovery and restart,security,scalability


-------------------------------Metadata------------------------

Traceability(Process,data)

--Transformations,Errors

Process flow

----------------ERROR and Exception Handling-----------------

Master package

-Job completion/Failure

Extraction level

-Data type and length

Load level


-----------Package Templates----------------

Master package

Extraction

-From file
-From database

Load
-Dimension
 -->simple
 -->complex

Fact

--------------Package Templates->Master package-------------------
Master package:

-Error handling
-Alerts
-Exception Handling

----------------Package Templates->Extraction Packages---------------

-handle files
-Maintain extraction related counts
-handle data type errors

--------------------Package Templates->Load Package----------
-Meta data capture for business rules
-load statistics

--------------Package Configuration

external data sources
internal data sources/destinations
File locations(Database or file system)
During development
Project life cycle, DEV-->TEST-->PROD
package sepecific parameters

-----------ETL Reporting-----------

Alerts
failure/success
Historical data for statistics.