Microsoft® SQL Server® 2012 Bible

1193

Chapter 52: Building, Deploying, and Managing ETL Workfl ows in Integration Services

52

This editor enables the overrides of Parameters and Connection Managers, as well as

Environments (more on Environments in next section.) All parameters and their values from

an Execution get logged at run time, so you have an audit trail.

Configuring and Using Environments

Inside the SSISDB catalog is a node named Environments. SSIS Catalog Environments give

users the ability to create sets of variables specifi c to different environments. The best way

to understand these new environments is to think of them as sets of connection values

in stages of development. For instance, most organizations have a separate environment

for QA and Production, with corresponding separate servers. You could easily create a QA

Environment and a separate Production Environment on the SSIS Server and confi gure proj-

ects to use an Environment Reference. Inside each of these two Environments, you can cre-

ate a variable called SQLServer (it’s very important that the variable be named exactly the

same in each environment) for the SQL Server, and confi gure the value to be the respective

connection strings. From there, the environment variable you created in these environments

could be used to populate the value of a connection manager for the Execution of a Package.

With the Environment Variables in place inside the Execution, at run time, the DBA sim-

ply chooses an Environment, and the package executes using the proper values for that

Environment.

Executions of Packages Using Project Deployments

To run a package from the SSIS server, right-click the Package and click Run. The package

runs using the last confi gured set of values.

Transact-SQL for Executions

SSIS in SQL Server 2012 includes a new set of Transact-SQL commands for creating and man-

aging Executions:

■ catalog.create_execution: Creates the execution object. Parameters to confi gure

include the Project folder, the Project Name, the Package name, and optionally, the

environments to use.

■ (^) catalog.set_execution_parameter_value (optional): Overrides a server default
parameter value.
■ (^) catalog.start_execution: Starts the execution.
Scheduling Executions with SQL Server Agent
To create a job that runs a package from the SSIS Catalog, perform the following steps:

1. Click New Job, and give the job a meaningful name.


2. Click New Step, and give the step a meaningful name.


3. Select Integrations Services.


4. Select SSIS Catalog.

c52.indd 1193c52.indd 1193 7/31/2012 10:29:32 AM7/31/2012 10:29:32 AM

http://www.it-ebooks.info