Chapter 2

Installing SQL Server Integration Services

This book requires that you have SQL Server Data Tools (SSDT) and the SQL Server Integration Services (SSIS) service installed. To develop SSIS, you cannot use SQL Express. The SSIS run time to run packages does ship with all editions, but on some of the lower editions that run time may not work with all SSIS components.

On the subject of editions of SQL Server, you have a decision to make as to which edition you want to install: Standard Edition, Business Intelligence (BI), or Enterprise Edition. Developer Edition is also available. It contains all the components of Enterprise Edition at a tiny fraction of the cost but is licensed for development only. Enterprise Edition gives you a few additional SSIS components that you may be interested in for SQL Server 2012:

• Data Mining components
• Fuzzy Lookup and Group transforms
• Dimension and Partition Processing destinations
• Term Extraction and Lookup transforms
• Higher performance components for ODBC, Oracle, and SAP
• Change Data Capture components

Additionally, the Enterprise Edition of SQL Server gives you database engine features that complement or may affect SSIS. One such feature is the Change Data Capture (CDC) feature, which enables you to easily synchronize two systems by querying SQL Server 2012 for only the changes that have occurred after a given date or time. Data compression is another key feature that may speed up your database reads and reduce your disk cost by 60–75 percent.

Oftentimes, if you care about the Enterprise Edition features enough, but don’t need Enterprise Edition for the database engine, you might decide to license an SSIS server with just the minimum number of client access licenses (CALs) instead of doing a per-core license. This approach reduces your SQL Server licensing cost sizably, but you now have new hardware cost to add.

When you’re installing SQL Server, you need to ensure that the SQL Server Data Tools, Integration Services, and Management Tools - Complete check boxes are selected in the Feature Selection screen (shown in Figure 2-1). The Integration Services option installs the run time and service necessary to run the packages, and is likely all you would need on a production server. The SQL Server Data Tools option installs the designer components, and the Management Tools option installs the DBA tools necessary to manage the packages later.

Figure 2-1

After you complete the Feature Selection screen, SQL Server installs all the necessary components without any wizard configuration required for SSIS. Once the installation is complete, open the configuration file located at C:Program FilesMicrosoft SQL Server110DTSBinnMsDtsSrvr.ini.xml. This file configures the SSIS service. Change the <ServerName> node where it currently says “.” to your SQL Server’s instance name where you want to store your packages. You can also change the directory from ..Packages to the directory of your choice.

<?xml version=”1.0” encoding=”utf-8”?>
<DtsServiceConfiguration xmlns:xsd=”http://www.w3.org/2001/XMLSchema” 
xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”>
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

  <TopLevelFolders>
    <Folder xsi:type=”SqlServerFolder”>
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>

    <Folder xsi:type=”FileSystemFolder”>
      <Name>File System</Name>
      <StorePath>..Packages</StorePath>
    </Folder>

  </TopLevelFolders>
</DtsServiceConfiguration>

Once you modify this file, you need to restart the SSIS service from the SQL Server Configuration Manager under the SQL Server 2012 node in the Start menu or the Services applet.