EXERCISE 7 USING THE IMPORT AND EXPORT WIZARD. THIS EXERCISE ASSUMES...

8. Switch between the Control Flow and Data Flow tabs to view the

package contents. Select items and view their properties in the

properties window. When you are done, close BIDS.

Using Package Configurations

Developers build the SSIS packages on their workstations. Therefore, the paths to

data files, the connection information to the development servers, and any number

of other settings often can be significantly different from those that are needed

when the package runs in production. Developers can facilitate the simple

management of this problem by implementing package configurations. Package

configurations make it possible for the SSIS runtime to dynamically read alternative

values for these properties and use the new values rather than the values that are

hardcoded in the package.

The package configuration values can be saved in XML Files, SQL Tables,

environment variables, and registry settings. By working with the developers,

administrators can easily reconfigure packages to work correctly in production

environments without have to open and edit the packages themselves. Ideally,

developers will design and configure the package configurations as part of the

package development process. However, even if developers did not facilitate package

configurations, you can manually override almost any property in a package when

using the various execution utilities.

xam

arning

Remember that package configurations allow you to change the

definition of a package’s connections, variable values, and so on.

If a package that ran in development is having problems working in

a production environment, package configurations may well be the

answer!

Managing SSIS

SSIS actually includes a Windows service named SQL Server Integration Services 10.0.

This service is responsible for managing the storage of SSIS packages on the server,

enforcing the security of those packages, and managing the packages while they are

running. The SSIS Service is a shared instance; that is, even if you install multiple

instances of SQL Server on a single computer, there will be only a single SQL Server

Integration Services 10.0 instance.

To manage the SQL Server Integration Services instance, you can connect to it

in SQL Server Management Studio using the Object Explorer. To connect to the

SSIS Instance, you can use the Connect drop down in the Object Explorer

window and select Integration Services…. Once you have connected, the Object

Explorer shows a tree view of any packages that are currently running, as well as a

tree view of the SSIS file system and MSDB storage. Figure 8.6 shows you how to

connect to an Integration Services instance using the Object Explorer.

Figure 8.6

Connecting Object Explorer to an SSIS Instance

Once you select the option, you will be prompted with a standard connect

dialog box. Notice that the only option you can change in the connect dialog box

is the Server name:. You can connect to an SSIS instance only by using Windows

Authentication. Once you have connected, the Object Explorer allows you to interact

with the SSIS instance by managing both running as well as stored packages.

Figure 8.7 shows the Object Explorer when it is connected to an SSIS instance.

Figure 8.7

Working with SSIS in Object Explorer

We showed you before that an SSIS package is actually an XML file. And after

reading about BIDS, you know that the package files themselves can actually exist

on a developer’s workstation when they are being created. Once developers have

completed a package (or a set of packages), they need to get them deployed to the

server where the SQL Server Integration Services service is installed for execution.

There are actually a number of choices for how a package should be stored.

Figure 8.7 shows two containers under the Stored Packages folder. You could

store packages as files in the File System, or as records in the MSDB database on

a SQL Server. As you can see from Figure 8.7, SSIS can access packages in both

storage areas. We’ll discuss your storage options in the next section.

Understanding SSIS Package Storage

Where a package is saved, or stored, really doesn’t have anything to do with where it

is used or executed. An analogy would be that it doesn’t matter where you save a

Word document; as long as you can get to it across the network, you can open and

use it from anywhere. The idea is the same with a package. Where you store a package

impacts where it lives only when the package isn’t being used. Typically, however,

you will store the packages physically on the same server where they will be run.

When developers are done building a package, they either deploy them to a

production server themselves (not recommended) or the give them to an adminis-

trator for deployment to the production server (better). Regardless, when you

“deploy” a package, you are really just copying the package to either the file system

or to the SQL Server MSDB database on the target system.

SSIS supports storing packages as files in the file system or as records in SQL

Server. When you deploy packages to the file system on a server, they should go to

C:\Program Files\Microsoft SQL Server\100\DTS\Packages by default. When you

deploy them to SQL Server, they end up in the msdb.dbo.sysssispackages table. It is

typically recommended that you store the packages into SQL Server rather than to the

file system. There are some benefits to this that we will discuss throughout this section.

When the packages are deployed to a server where the SQL Server Integration

Services service is running, it can broker access to the local storage through what is

called the SSIS Package Storage. It ends up being the same file system and MSDB

locations as if you were to deploy them there directly yourselves, but it formalizes

access to them through the SSIS service.

Deploying SSIS Packages

There are a number of ways that you can deploy packages to your servers. Table 8.5

explains the common deployment mechanisms:

Table 8.5

SSIS Package Deployment Tools

Tool

Description

Deployment Wizard

Developers can use a feature of BIDS to

produce what is called a deployment utility.

The deployment utility produces a file with the

SSISDeploymentManifest extension in the same

directory as the package files and configuration files

extension. The developer can zip the file up and

send it to the administrator. The administrator simply

double clicks the SSISDeploymentManifest file and

follows the prompts. If the packages were designed

with configurations, the deployment wizard even

allows the administrator to provide the alternative

configuration values as part of the deployment.

Object Explorer

In SQL Server Management Studio’s Object Explorer,

you can connect directly to an Instance of the SQL

Server Integration Services Instance. From there,

you can import and export packages from the SSIS

Package Store on the server simply by right clicking

an item and choosing either Import Package…

or Export Package…

Continued