Applies to: SQL Server SSIS Integration Runtime in Azure Data Factory
Sql Server Integration is a crucial aspect for businesses managing and transforming data. SQL Server Integration Services (SSIS) is a powerful platform to build enterprise-level data integration and data transformations solutions. SQL Server provides a unified setup program that allows you to install various components, including Integration Services. This guide will walk you through the process of installing SQL Server Integration Services, whether you need a standalone instance or alongside other SQL Server features.
Before proceeding with the installation of Microsoft SQL Server Integration Services, it’s important to understand key considerations to ensure a smooth and successful setup.
Preparing for SQL Server Integration Services Installation
Before you begin installing SQL Server Integration Services, take into account the following points:
Standalone or Side-by-Side Installation Options
You have flexibility in how you install SQL Server Integration Services:
- Fresh Installation: Install SQL Server Integration Services on a computer that does not have any previous SQL Server instances. This is ideal for dedicated ETL servers or new environments.
- Side-by-Side Installation: Install Integration Services alongside an existing SQL Server instance. This allows you to upgrade to the latest SSIS version while keeping older versions intact, offering a seamless transition and compatibility.
When upgrading to a newer version of Integration Services on a machine that already has an older version, the latest version installs side by side. For detailed information on upgrading, refer to Upgrade Integration Services.
Step-by-Step Guide to Install Integration Services
Once you have reviewed the system requirements for SQL Server and confirmed your computer meets them, you are ready to install Integration Services.
It’s important to note that selecting certain SQL Server components during setup might install only a partial set of Integration Services features. These partial installations are designed for specific functionalities, like the SQL Server Import and Export Wizard, which relies on some SSIS components. However, for a complete SQL Server integration services installation, you must explicitly select Integration Services on the Feature Selection page within the SQL Server Setup Wizard. This ensures all necessary components for developing, deploying, and managing SSIS packages are installed.
Best Practices: Dedicated Server for ETL Processes
For organizations utilizing a dedicated server for extraction, transformation, and loading (ETL) operations, it’s highly recommended to install a local instance of the SQL Server Database Engine alongside Integration Services. SSIS packages are typically stored and managed within a Database Engine instance, and SQL Server Agent is commonly used for scheduling package executions.
If your ETL server lacks a Database Engine instance, you will need to schedule or run packages from a different server that does have one. This setup means packages are not executed on the dedicated ETL server, but rather on the server initiating them. Consequently, the resources of your dedicated ETL server are underutilized, and the servers initiating the ETL processes may experience unnecessary resource strain. Installing a local Database Engine on the ETL server ensures optimal resource utilization and performance for your data integration tasks.
Configuring SSIS Event Logging for Monitoring
In new installations of Integration Services, event logging for package executions to the Application event log is disabled by default. This default setting is in place to prevent excessive event log entries, particularly when leveraging SQL Server’s Data Collector feature. The specific events that are not logged are EventID 12288 (“Package started”) and EventID 12289 (“Package finished successfully”).
However, if you need to log these events for monitoring and auditing purposes, you can easily enable them. To activate event logging, you will need to modify the Windows Registry. Open the Registry Editor and navigate to the node: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL Server130SSIS. Within this node, locate the DWORD value named LogPackageExecutionToEventLog and change its value from 0 to 1. This adjustment will enable the logging of package execution events to the Application event log, providing a detailed record of SSIS package activity.
Installing Additional Related Components
To ensure a comprehensive SQL Server integration services environment, consider installing these additional components as needed:
- SQL Server Data Tools (SSDT): Essential for developing, testing, and debugging SSIS packages.
- SQL Server Agent: For scheduling and automating SSIS package execution.
- Integration Services Catalogs: To deploy, manage, and monitor SSIS packages in a centralized manner.
By following these guidelines, you can successfully install and configure SQL Server Integration Services to meet your data integration needs.
