Microsoft SQL Server 2012 Integration Services Overview

Setting Up SSIS

You can install all SQL Server features—including SSIS—using a single setup program. The features needed to build, manage, or run SSIS solutions are spread across several SQL Server features, and must be installed appropriately. Figure 1-6 shows the feature selection screen in the SQL Server setup. The main features in SQL Server setup related to SSIS are as follows:

• Integration Services

• SQL Server Data Tools

• Database Engine Services

• Management Tools

Figure 1-6 Feature selection screen in SQL Server Setup.

SQL Server Features Needed for Data Integration

This section takes a brief look at the features that you need to install during SQL Server setup to be able to build and run data integration solutions.

Integration Services

This feature installs the SSIS runtime, some utilities (.exe files), a Windows service named SQL Server Integration Services (followed by a version number), and various libraries (.dll files) used to enable execution of SSIS packages outside of the design environment in SQL Server Data Tools. The Windows service manages locally executed packages and packages stored in msdb in a SQL Server instance. The Integration Services feature is available under Shared Features in the SQL Server feature tree shown in Figure 1-6 because it’s not SQL Server database instance specific; that is, it’s a feature shared by all instances. So even if a machine has multiple database instances, you need only one copy of Integration Services on that machine. Files installed for this feature in SQL Server 2012 reside in the %Program Files%\Microsoft SQL Server\110\DTS folder. In older versions, the folder hierarchy is the same but the number indicating SQL Server version (110 in the preceding path) is different. For example, Integration Services in SQL Server 2008 has 100 in the path.

SQL Server Data Tools

The SQL Server Data Tools feature provides the development environment for building SSIS packages. This graphical design tool is an intuitive and easy-to-use environment for composing data integration activities. The designer has separate design surfaces for authoring workflow for data integration and for creating a data flow pipeline. Composition of control or data flows is simple, and involves adding built-in tasks or components into the design surface from SSIS Toolbox, then configuring and connecting them. Integration Services does not have to be installed on the same machine to develop SSIS packages using SQL Server Data Tools. However, if Integration Services is not installed, you cannot execute packages designed in SQL Server Data Tools outside of that feature on the same machine using the DTExec utility.

SQL Server Data Tools usage is not limited solely to SSIS development; instead, it is an integrated environment for building SQL Server–based business solutions involving Analysis Services, Reporting Services, or database development. SQL Server Data Tools is hosted in Microsoft Visual Studio. All these technologies support the development experience and need a design environment. Because Visual Studio is the default development environment for Microsoft technologies, it’s the natural choice for SSIS development (as well as others). Visual Studio provides a familiar environment for developers, and the SQL Server Data Tools experience is well integrated with familiar Visual Studio features such as Solution Explorer, the Toolbox, the Properties pane, and the Output and Watch windows. Together, these provide a true, consistent end-to-end experience in building solutions. You can execute SSIS packages in SQL Server Data Tools and leverage other powerful capabilities, such as setting breakpoints and debugging during execution. Business Intelligence Development Studio or SQL Server Data Tools work with specific versions of Visual Studio and SSIS packages. Table 1-2 provides details on this.

Table 1-2 Compatibility between Visual Studio and Business Intelligence Development Studio/SQL Server Data Tools in SQL Server

Database Engine Services

An instance of the database engine is installed as a part of this feature. Integration Services packages can be deployed to SQL Server. In SQL Server 2012, packages are deployed to the SSIS catalog, which is a database in a SQL Server instance. In previous versions of SSIS, you could deploy packages to the msdb database in SQL Server. The SSIS catalog is used for management and administration of SSIS packages. The catalog contains system database objects needed for deployment, configuration, and execution of SSIS packages in the context of a SQL Server instance. It also has database objects for monitoring or reporting execution status and troubleshooting data or performance issues. Database engine services features include SQL Server Agent, which is useful for scheduling SSIS package execution. Installing this SQL Server feature installs the components required by the Import and Export Wizard (if that is not already included in the installation through the Integration Services feature during setup). The Import and Export Wizard is included to facilitate getting data into or out of the database engine without explicitly including the Integration Services feature during SQL Server installation.

Management Tools

SQL Server Management Studio is installed as a part of this feature. SQL Server Management Studio is a popular tool among database administrators. It provides a DBA-friendly environment to manage the SSIS catalog, project folders, server variables, and project environment references in Integration Services 2012. After deploying packages to the catalog, you can configure, validate, execute, and monitor them using SQL Server Management Studio. The SSIS catalog is just a database in SQL Server with the name SSISDB, so any feature available in SQL Server Management Studio to interact with or manage a database can be used on the catalog itself. You can also use SQL Server Management Studio to interact with legacy SSIS Windows service or agent jobs that execute SSIS packages. For example, when you design a maintenance plan in SQL Server Management Studio it involves SSIS tasks.

SQL Server Editions and Integration Services Features

Several editions of SQL Server are available, and each edition is associated with a specific usage scenario. The features available in different editions correspond to the target usage scenarios. The functionality of SSIS differs among these various editions. See Table 1-3 for details.

Table 1-3 Integration Services features available in different editions in SQL Server 2012

The Business Intelligence Edition is available only in SQL Server 2012; it is not available in earlier versions. On the other hand, the Workgroup Edition is not available in SQL Server 2012, but it is available in earlier versions. The SQL Server Datacenter edition available in SQL Server 2008 R2 is not available in SQL Server 2012. If an edition is not available in SQL Server 2012, the SQL Server Installer might change the edition during the upgrade to SQL Server 2012. SQL Server Developer and Evaluation editions have restrictions on usage: The Developer edition is licensed for development purposes only, and the Evaluation edition’s license is valid for only 180 days. In editions lower than Standard, the Import and Export Wizard does not allow saving packages or use of other utilities such as the Upgrade Wizard or DTExec; those are blocked. Some of the high-end editions offer flexible licensing models, which are not discussed in this book.