Home› Platform Support› SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS)

AnalyticsCreator generates SQL Server Integration Services (SSIS) packages from the processing definitions maintained in the AnalyticsCreator project. The generated packages can be used to load, historise, persist and orchestrate data warehouse processing in Microsoft SQL Server environments.

AnalyticsCreator is the design and generation layer. The generated packages are executed by Microsoft SQL Server Integration Services rather than by an AnalyticsCreator production runtime.

Overview

AnalyticsCreator separates data warehouse design from its SSIS implementation. Imports, historisation rules, persisting logic, workflows and processing dependencies are maintained as metadata and used to generate the corresponding SSIS orchestration.

This allows teams to maintain processing logic centrally in the AnalyticsCreator project while producing native Microsoft SSIS assets for execution in the target environment.

What AnalyticsCreator Generates

Depending on the project design and deployment configuration, AnalyticsCreator can generate SSIS packages for activities such as:

  • source data import
  • data loading into generated warehouse structures
  • historisation processing
  • execution of generated persisting logic
  • script-based processing where configured
  • workflow orchestration
  • dependency-aware execution of related processing packages

The generated packages form part of the deployment output and can be included selectively according to the packages required by the target implementation.

AnalyticsCreator Package Model

Processing is organised in AnalyticsCreator using package definitions. Different package types represent different parts of the data-processing workflow.

These include:

  • Import packages for source-to-target data loading
  • Historisation packages for maintaining historical versions of records
  • Persisting packages for materialising transformation results
  • Workflow packages for coordinating other packages and their execution order
  • Script packages for script-based processing
  • External packages where execution is handled outside the normal generated package sequence
  • Export packages for outbound processing where configured

The package definition in AnalyticsCreator describes the processing work. SSIS is one of the technologies that can be selected to generate the corresponding executable orchestration.

Workflow and Dependency Orchestration

AnalyticsCreator maintains dependencies between processing packages and can use these definitions when generating the SSIS workflow.

Workflow packages can coordinate multiple child packages and define processing behaviour such as:

  • which packages participate in the workflow
  • execution order and dependencies
  • whether processing should stop when a child package fails
  • retry attempts
  • the interval between retry attempts

This allows the execution sequence to be maintained as part of the AnalyticsCreator metadata rather than recreated manually in SSIS each time the design changes.

Import Packages

Import packages are used to load data from configured source systems into the corresponding target structures.

AnalyticsCreator maintains the source-to-target mapping together with the package assignment and relevant loading configuration. Generated SSIS packages then execute that data movement in the target environment.

Depending on the connector and import definition, the package configuration can include field mappings, queries or filters, package variables, logging options and pre- or post-import processing.

Historisation

AnalyticsCreator can generate processing for historical data management based on the historisation definition maintained in the project.

Historisation definitions can include Slowly Changing Dimension behaviour, validity handling and treatment of records that are no longer present in the source.

Depending on the configured historisation method, the processing can be implemented through generated SSIS package logic or generated stored procedures.

Persisting

Transformation results can be persisted physically when the project design requires materialised output rather than a view-only implementation.

AnalyticsCreator maintains the persisting definition and its dependencies. Generated SSIS orchestration can then coordinate execution of the corresponding database processing in the required sequence.

SSIS Configuration

AnalyticsCreator provides deployment settings for controlling how generated SSIS packages receive connection and configuration information.

For generated SSIS output, connection information can be configured using supported storage approaches such as:

  • environment variables
  • configuration files
  • package parameters
  • project parameters

The appropriate approach depends on the selected SSIS deployment model and the customer's environment-management requirements.

AnalyticsCreator also provides configuration for the connection used by its SSIS configuration structures where that pattern is used.

Project References and SSIS Catalog Integration

AnalyticsCreator provides SSIS deployment settings for project-reference behaviour with the Microsoft Integration Services catalog.

Microsoft SSIS supports both project-based and package-based deployment approaches. The deployment model determines how parameters, configurations and environment-specific values are managed.

AnalyticsCreator should therefore be configured according to the SSIS deployment approach selected for the target environment rather than assuming one deployment model for every project.

Package Compatibility

Generated SSIS output can be configured with the required package compatibility level in the AnalyticsCreator deployment definition.

The selected compatibility level should match the Microsoft SQL Server Integration Services environment in which the packages will be developed, deployed and executed.

Compatibility should be validated when upgrading SQL Server or changing the SSIS execution environment.

Generation, Deployment and Execution

Package generation, deployment and execution are separate activities.

  1. Design. Define imports, historisation, transformations, persisting logic, workflows and dependencies in AnalyticsCreator.
  2. Generate. Select the required packages for SSIS output and generate the deployment artifacts.
  3. Review. Inspect the generated SSIS project and package output together with the related database artifacts.
  4. Configure. Apply the required environment-specific connection strings, parameters, variables and SSIS runtime settings.
  5. Deploy. Deploy the generated SSIS assets using the Microsoft SSIS deployment process selected for the environment.
  6. Execute. Run the deployed packages using Microsoft SQL Server Integration Services and validate the resulting data processing.

Generating or deploying an SSIS package does not itself mean that business data has been loaded. Data movement and processing occur when the package is executed.

Typical Processing Workflow

A typical SQL Server implementation can use generated SSIS packages to coordinate several processing stages.

For example, a workflow can:

  1. execute source import packages
  2. load data into the configured staging structures
  3. run historisation where it is enabled
  4. execute generated transformation and persisting logic
  5. process dependent downstream packages
  6. prepare updated warehouse structures for analytical consumption

The actual sequence is determined by the AnalyticsCreator project. This is an example of a generated workflow, not a required architecture for every implementation.

Visual Studio and Microsoft SSIS Tooling

Generated SSIS packages can be reviewed and worked with using the normal Microsoft SSIS development tooling.

AnalyticsCreator deployment output can provide the SSIS project content required for development and deployment workflows. Microsoft tools remain responsible for building, deploying, executing and monitoring the resulting SSIS implementation according to the selected deployment model.

Manual changes to generated package definitions should be managed carefully. Where AnalyticsCreator owns the package definition, subsequent regeneration can replace generated output.

Changes and Regeneration

When AnalyticsCreator-owned processing definitions change, the corresponding SSIS packages can be regenerated from the updated metadata.

This can include changes to:

  • source mappings
  • package contents
  • historisation rules
  • persisting definitions
  • workflow dependencies
  • execution order

Generated differences should be reviewed before release, particularly where packages have been extended or modified outside AnalyticsCreator.

Regeneration does not imply bidirectional synchronisation of external package edits back into the AnalyticsCreator metadata model.

CI/CD and Version Control

AnalyticsCreator project definitions and generated SSIS deployment artifacts can be incorporated into controlled development and release processes.

Teams can use this approach to:

  • version AnalyticsCreator project definitions
  • review metadata changes before generation
  • regenerate SSIS packages from an approved design
  • review generated package and database changes
  • promote approved artifacts between environments
  • retain an auditable history of implementation changes

The exact build and deployment process depends on the customer's source-control system, SQL Server environment and selected Microsoft SSIS deployment model.

Prerequisites and Considerations

  • The required SQL Server and SQL Server Integration Services environment must be available.
  • The selected SSIS package compatibility level must match the intended runtime environment.
  • Source and target systems must be reachable from the SSIS execution environment.
  • Connection strings, credentials, parameters and environment-specific values must be configured appropriately.
  • The selected SSIS deployment model should be agreed before configuring parameter and configuration handling.
  • Generated package output should be reviewed before deployment to production.
  • Package execution and resulting data should be validated after deployment.
  • Manual changes to generated packages require an agreed ownership model to avoid conflicts during regeneration.

Example Use Cases

  • Generate SSIS packages for source-to-staging data loading.
  • Coordinate historisation and warehouse processing.
  • Execute generated SQL Server stored procedures in dependency order.
  • Generate workflow packages for multi-stage data warehouse processing.
  • Regenerate package orchestration when the metadata design changes.
  • Use SSIS as the execution layer for an on-premises SQL Server data warehouse.

Related Content

  • SQL Server
  • Packages
  • Deployment
  • Historisation
  • Persisting
  • Import
  • Workflow
  • Version Control

Key Takeaway

AnalyticsCreator maintains the processing design and generates native SSIS orchestration from that metadata. Microsoft SQL Server Integration Services executes the resulting packages, while environment-specific configuration, deployment and runtime management remain under the control of the customer's Microsoft environment.