Applies to: SQL Server, SSIS Integration Runtime in Azure Data Factory
In the realm of data integration and workflow automation, the Sql Server Integration Services Package stands as a fundamental unit. Essentially, an SSIS package is a meticulously organized collection of components that work together to perform complex data operations. These components include connections to various data sources, control flow elements that dictate the order of operations, data flow elements for data transformation, event handlers to manage package behavior, variables and parameters for dynamic configuration, and configurations to manage deployment settings. You can assemble these packages using the intuitive graphical design tools provided by SQL Server Integration Services, or for more advanced scenarios, build them programmatically. Once created, an SSIS package can be saved to SQL Server, the SSIS Package Store, or the file system. For modern deployment strategies, you also have the option to deploy the SSIS project directly to the SSIS server. Crucially, the package represents the atomic unit of work within SSIS – it’s the entity that is retrieved, executed, and saved as a whole.
When you initiate the creation of a new sql server integration services package, it begins as a blank slate, devoid of any specific actions. To imbue it with functionality, the initial step involves incorporating a control flow. Subsequently, you can enhance its data processing capabilities by adding one or more data flows.
The illustration below depicts a basic SSIS package. It features a control flow that includes a Data Flow task. This Data Flow task, in turn, houses a data flow, showcasing the hierarchical structure within an SSIS package.
Alt text: Diagram illustrating the structure of an SSIS package, showing a control flow containing a Data Flow Task, which itself contains a data flow, highlighting the nested architecture of SSIS packages.
After establishing the core structure of your sql server integration services package, you can further enrich it with advanced features. These include logging mechanisms to track execution details and variables to introduce dynamic behavior, significantly extending the package’s capabilities. For a deeper dive into these enhancements, refer to the section on Objects that Extend Package Functionality.
Finally, to tailor the behavior of your completed SSIS package, you can configure package-level properties. These properties enable you to implement security measures, enable package restart capabilities from checkpoints for robust execution, and integrate transactions into the package workflow to ensure data consistency. More details on these advanced settings can be found in the section concerning Properties that Support Extended Features.
Contents of an SSIS Package
An sql server integration services package is composed of several key elements, each playing a distinct role in the overall functionality.
Tasks and Containers (Control Flow). The control flow is the backbone of any SSIS package, orchestrating the sequence of operations. It’s built from one or more tasks and containers that execute in a defined order when the package runs. To manage this order and set conditions for the execution of subsequent tasks or containers within the package’s control flow, precedence constraints are used to link them together. Furthermore, for repetitive operations, a subset of tasks and containers can be grouped and executed as a unit within the overall package control flow. For a comprehensive understanding, explore Control Flow.
Data Sources and Destinations (Data Flow). Data flow is the engine that drives data transformation within an SSIS package. It comprises sources to extract data, destinations to load data, transformations to modify and enrich the data in transit, and paths that connect these components. Before incorporating a data flow into a package, it’s essential that the package’s control flow includes a Data Flow task. The Data Flow task is the executable component within the sql server integration services package responsible for creating, ordering, and running the data flow. Notably, each Data Flow task in a package initiates a separate instance of the data flow engine, allowing for parallel data processing if needed. Delve deeper into Data Flow Task and Data Flow for more information.
Connection Managers (Connections). A typical SSIS package relies on connection managers to interact with external data sources. A connection manager serves as a bridge between the package and a data source. It meticulously defines the connection string necessary for accessing the data utilized by tasks, transformations, and event handlers within the package. Integration Services offers a wide array of connection types catering to diverse data sources such as text files, XML documents, relational databases (like SQL Server itself), and analytical platforms like Analysis Services databases and projects. To learn more, refer to Integration Services (SSIS) Connections.
Objects that Extend SSIS Package Functionality
To enhance the robustness, flexibility, and manageability of sql server integration services packages, SSIS provides several objects that extend their core functionality.
Event Handlers
Event handlers in an SSIS package introduce reactive workflow capabilities. An event handler is essentially a miniature workflow that executes in direct response to events raised by the package itself, or by tasks or containers within it. For instance, you could configure an event handler to proactively monitor disk space when a pre-execution event occurs, or to automatically send an email notification to an administrator containing error details should a failure occur. Structurally, an event handler mirrors a package, complete with its own control flow and optional data flows. Event handlers can be associated with individual tasks or containers within the SSIS package, providing granular control over event-driven behavior. For comprehensive details, see Integration Services (SSIS) Event Handlers.
Configurations
Configurations in sql server integration services packages provide a mechanism for externalizing package settings. A configuration is a curated set of property-value pairs that dynamically define the properties of the package, its tasks, containers, variables, connections, and event handlers at runtime. Leveraging configurations allows you to update property values without requiring direct modification of the package file itself. When the package is executed, the configuration information is loaded, effectively updating the relevant property values on-the-fly. A common use case is updating connection strings based on the deployment environment.
Configurations are saved separately and deployed alongside the SSIS package. This separation makes it easy to adapt a package to different environments post-deployment. The values within a configuration can be modified during installation to seamlessly support the package’s operation in a new environment. For detailed guidance, refer to Create Package Configurations.
Logging and Log Providers
Logging is crucial for monitoring and auditing sql server integration services package executions. A log is a repository of information meticulously collected during package execution. This information can range from timestamps indicating package start and finish times to detailed execution paths and error messages. A log provider defines both the destination for these logs (e.g., SQL Server database, text file) and the format in which the log data is structured. While logs are associated with a package, individual tasks and containers within the package can contribute information to any of the package’s logs. Integration Services offers a diverse set of built-in log providers, including providers for writing logs to SQL Server databases and text files. For specialized logging needs, you can also create custom log providers. For in-depth information, consult Integration Services (SSIS) Logging.
Variables
Variables in SSIS packages introduce dynamism and flexibility. Integration Services supports two categories of variables: system variables and user-defined variables. System variables are pre-defined and provide valuable context about package objects at runtime. User-defined variables empower you to create custom variables to support specific scenarios within your packages. Both system and user-defined variables can be seamlessly integrated into expressions, scripts, and configurations, making them powerful tools for dynamic package behavior.
Package-level variables encompass both the built-in system variables available to a package and user-defined variables scoped at the package level. To learn more, refer to Integration Services (SSIS) Variables.
Parameters
Parameters in sql server integration services packages offer a modern approach to dynamic configuration, particularly beneficial in newer deployment models. SSIS parameters allow you to assign values to package properties precisely at the moment of package execution. You can define project parameters at the project level, making them available to all packages within the project, and package parameters specifically scoped to individual packages. Project parameters are intended to supply external input to a project, which can then be used by multiple packages. Package parameters, on the other hand, provide a way to modify package execution without necessitating package edits and redeployments, enhancing deployment agility. For a detailed exploration, see Integration Services (SSIS) Parameters.
Package Properties that Support Extended Features
The sql server integration services package object itself has properties that enable advanced capabilities beyond basic execution.
Restarting Packages
For long-running SSIS packages, especially those with complex workflows, the ability to restart from a specific point of failure is invaluable. The package object provides checkpoint properties that facilitate package restart. For example, if a package updates multiple tables sequentially and a failure occurs midway, checkpointing allows you to rerun the package starting from the failed task, avoiding redundant execution of preceding successful tasks. This restart capability significantly reduces execution time and resource consumption for lengthy packages. Restarting a package means initiating execution from the point of failure rather than from the beginning. For detailed instructions, refer to Restart Packages by Using Checkpoints.
Securing Packages
Security is paramount when dealing with sensitive data. SSIS packages offer security features to protect both the package itself and the sensitive data it processes. A package can be digitally signed to verify its source and integrity. Additionally, packages can be encrypted using passwords or user keys to control access to sensitive data within the package. While digital signatures authenticate the package’s origin, you must explicitly configure Integration Services to validate these signatures during package loading. For more information on securing packages, see Identify the Source of Packages with Digital Signatures and Access Control for Sensitive Data in Packages.
Supporting Transactions
To ensure data consistency and integrity across multiple operations within an SSIS package, transactions are essential. Setting a transaction attribute on the package enables tasks, containers, and connections within the package to participate in a transaction. Transaction attributes guarantee that all operations within the transaction either succeed or fail as a single atomic unit. Furthermore, packages can invoke other packages and enroll them in transactions, enabling you to orchestrate complex, multi-package workflows as a cohesive unit of work. To understand transaction management in SSIS, see Integration Services Transactions.
Custom Log Entries Available on the Package
For detailed auditing and monitoring, sql server integration services packages provide custom log entries that capture specific package lifecycle events.
| Log entry | Description |
|---|---|
| PackageStart | Indicates the precise moment when the SSIS package began execution. Note: This log entry is automatically generated and cannot be excluded from logging. |
| PackageEnd | Signals the completion of the SSIS package execution, regardless of success or failure. Note: Similar to PackageStart, this log entry is automatically generated and mandatory in the logs. |
| Diagnostic | Provides valuable insights into the system configuration that can influence package execution. This includes details such as the number of executables that can run concurrently, resource availability, and other system-level factors that might impact performance. This log entry is particularly useful for troubleshooting performance bottlenecks and understanding the execution environment of your sql server integration services package. |
Set the Properties of a Package
Configuring the properties of an SSIS package is crucial for tailoring its behavior and performance. You can set these properties using two primary methods:
-
Properties Window in SQL Server Data Tools (SSDT): This graphical interface provides an intuitive way to browse and modify package properties. For step-by-step instructions, see Set Package Properties.
-
Programmatically: For automated configuration or more advanced scenarios, you can programmatically access and set package properties using the SSIS object model. Refer to the Package class documentation for details.
Reuse an Existing Package as a Template
Efficiency and consistency are key in data integration projects. SSIS packages are often designed to be reusable templates, serving as blueprints for creating new packages with shared functionalities. You can create a base package embodying common logic and then either copy it to create new packages or designate it as a formal template. For example, a package designed to download, copy, and process files might include FTP and File System tasks within a Foreach Loop container to iterate through files in a directory. It might also incorporate Flat File connection managers and sources for extracting data from these files. While the data source and processing logic remain consistent, the destination might vary between packages. In such cases, the base package serves as a template, and the destination components are added to each new package derived from it. You can also leverage existing packages as templates when adding new packages to an Integration Services project. For detailed guidance, see Create Packages in SQL Server Data Tools.
When a new sql server integration services package is created, either programmatically or via SSIS Designer, it’s automatically assigned a unique GUID to its ID property and a default name to its Name property. However, when you create a new package by copying an existing one or using a template, these identifiers are also duplicated. This duplication can pose issues, especially when using logging, as the package GUID and name are used to identify the source of log entries. Therefore, it’s crucial to update both the name and GUID of newly created packages derived from templates or copies to ensure clear differentiation in log data and overall package management.
To regenerate the package GUID, you can easily do so within the Properties window in SQL Server Data Tools (SSDT) by right-clicking in the ID property and selecting “Generate New GUID”. To modify the package name, simply update the value of the Name property in the same Properties window. Alternatively, you can utilize the command-line dtutil utility or programmatically modify the GUID and name. For detailed instructions, refer to Set Package Properties and dtutil Utility.
Related Tasks
Integration Services offers multiple tools for creating and managing sql server integration services packages, including graphical designers and programmatic interfaces. Explore the following resources for more information:
