SSIS Framework for professional environments

Even with the improvements the SSIS DB has undergone since SQL Server 2012, protocolization is insufficient for professional enterprise data warehouse systems.

A professional logging should help answer the following questions:

  • With which package execution has data record X made it into the system?
  • Which other data records are possibly affected by the same logic error as data record X?
  • Which transformations have taken the longest time?
  • Of X data records that were read from the data source, how many data records have been A) reinserted, B) changed, C) discarded during the data load process?

The second aspect is that in practice, I see a lot of manually repeated development of SSIS packages. A professional template, already included by the according logging, saves the more time the more packages are developed, and additionally ensures consistency, which is essential for troubleshooting.

More than 12 years of experience in the field of data warehouse with SQL Server, Integration and Analysis Services thus have produced a comprehensive framework including a best practices collection, which I can adapt according to the customer’s requirements.

Features comprise:

    • Standardized SSIS project template in Visual Studio
    • Standardized SSIS package templates for SCD1, SCD2 and SCD6 or master and fact tables
    • Scripting for environment variables and assignment of these to the SSIS project in Integration Services
    • RAPID Development – up to 10 x less development work
    • Standardized approach allows applications BIML, EziApi or XQuery
    • Management and maintenance of all package parameters in a global SQL table as extension of the SSISDB
    • Call of the packages by a designated Stored Procedure with automatic assignment of the parameters from the SQL table
    • Merge statement for (initial) loading of the parameter table in accordance with dynamic and fixed parameters
    • Incremental loading process by means of dynamic assignment of the relevant SSIS package parameters
    • SSIS technique according to best practices for error detection and logging in the dataflow in accordance with transactions and best possible performance
    • Standardized naming conventions of all task- and dataflow components
    • Transfer of a BatchID from the master package to the child packages for reporting and performance optimization
    • Logging of the extracted and loaded number of data records permits control of the entire dataflow
    • Logging for changing variable values
    • Logging of used connections
    • Logging of errors of all Stored Procedures in the SSIS log used for the SSIS process, and storing in designated SQL log error table
    • Scheduling engine for packages and dependent parameter configuration (permits more precise execution of packages as SQL Server agent alone)
    • SSIS DB index optimization

Learn more about the benefits and ask for your individual offer. Contact me here.