[go: up one dir, main page]
Scribd
Upload
112 views25 pages

Deploying and Configuring SSIS Packages

This module provides an overview of deploying and configuring SQL Server Integration Services (SSIS) packages. It discusses the two deployment models for SSIS packages: the package deployment model and the project deployment model. Key differences between the models include the unit of deployment, storage location, use of dynamic configuration, and troubleshooting capabilities. The module also covers creating an SSIS catalog, deploying projects, configuring environments and variables, running packages, and scheduling packages for automated execution using SQL Server Agent jobs. A lab demonstrates these deployment and configuration tasks hands-on.

Uploaded by

Richie Poo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
112 views25 pages

Deploying and Configuring SSIS Packages

This module provides an overview of deploying and configuring SQL Server Integration Services (SSIS) packages. It discusses the two deployment models for SSIS packages: the package deployment model and the project deployment model. Key differences between the models include the unit of deployment, storage location, use of dynamic configuration, and troubleshooting capabilities. The module also covers creating an SSIS catalog, deploying projects, configuring environments and variables, running packages, and scheduling packages for automated execution using SQL Server Agent jobs. A lab demonstrates these deployment and configuration tasks hands-on.

Uploaded by

Richie Poo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 25

Module 13

Deploying and Configuring SSIS


Packages
Module Overview

Overview of SSIS Development


Deploying SSIS Projects
• Planning SSIS Package Execution
Lesson 1: Overview of SSIS Development

SSIS Deployment Models


Package Deployment Model
Project Deployment Model
• Deployment Model Comparison
SSIS Deployment Models

• Package Deployment Model


• One or more SSIS Packages are deployed at the
same time
• Project Deployment Model
• Multiple packages are deployed in a single project
• Destination for Deployment
• Options depend on deployment model
Package Deployment Model

• Storage
• SSISDB
• File System

• Package Configurations
• Property values to be set dynamically at run time

• Package Deployment Utility


• Generate all required files for easier deployment
Project Deployment Model

• The SSIS catalog


• Storage and management for SSIS projects on an SQL
Server instance
• Folders
• A hierarchical structure for organizing and securing
SSIS projects
Deployment Model Comparison

Key differences between models

Feature Package Deployment Model Project Deployment Model


Unit of deployment One or more packages Project
Storage Packages and all associated files can A project is deployed to the SSIS
be copied to the file system of a local catalog, or a folder within the
or remote computer. They can also catalog, of an instance of SQL
be deployed to the SSIS catalog, or a Server.
folder within the catalog, of an
instance of SQL Server.
Dynamic configuration Both projects and packages can Both projects and packages can
contain parameters and references to contain parameters and references
environments. You can access the to environments. You can access the
parameters and environment parameters and environment
references by using the Configure references by using the Configure
Dialog box. Dialog box.
Compiled format Packages and associated resources The entire project is compiled as a
are each stored as single files in the single file (with an .ispac extension).
file system. The entire project might
consist of many files.
Troubleshooting To log events, you have to add a log Events are automatically logged and
provider to the package and saved to the catalog. These events
configure logging for each one can then be displayed with views
individually. such as catalog.executions and
catalog.event messages.
Lesson 2: Deploying SSIS Projects

Creating an SSIS Catalog


Environments and Variables
Deploying an SSIS Project
Viewing Project Execution Information
• Demonstration: Deploying an SSIS Project
Creating an SSIS Catalog

• Prerequisites
• SQL Server 2012 or later
• SQL CLR enabled
• Creating a catalog
• Use SQL Server Management Studio
• One SSIS catalog per SQL Server instance
• Catalog Security
• Folder Security
• Object Security
• Catalog Encryption
• Sensitive Parameters
Environments and Variables

• Environments
• Execution contexts for projects
• Variables
• Environment-specific values
• Can be mapped to project parameters and connection manager
properties at run time
Deploying an SSIS Project

• Integration Services Deployment Wizard


• Visual Studio
• SQL Server Management Studio

• dtutil.exe to deploy, move, delete, and check the


existence of SSIS packages
Viewing Project Execution Information

• Integration Services Dashboard provides built-in


reports
• Additional sources of information:
• Event handlers
• Error outputs
• Logging
• Debug dump files
Demonstration: Deploying an SSIS Project

In this demonstration, you will see how to:


• Configure the SSIS environment
• Deploy an SSIS project
• Create environments and variables
• Run an SSIS package
• View execution information
Lesson 3: Planning SSIS Package Execution

Options for Running SSIS Packages


Scheduling the ETL Process
Configuring Execution Context
Where to Handle Notifications
Where to Handle Logging
Combining SSIS Tasks with Other Tasks
• Implementing SSIS Agent Jobs and Schedules
Options for Running SSIS Packages

• SQL Server Management Studio


• Specify values for environment and any parameter
• Modify settings for the connection managers
• Specify logging options
• Dtexec and Dtexecui
• Dtexec provides command line to run SSIS packages
• Dtexecui is GUI to run Dtexec
• PowerShell
• Powershell cmdlets make it easier for you to manage, monitor and
execute SSIS packages
• Can integrate SSIS with other Windows PowerShell tasks
• SQL Server Agent
• Automates tasks in SQL Server
• Particularly useful for SSIS packages
Scheduling the ETL Process

• Data acquisition time windows


• Ascertain the best time to extract data from the live
systems
• Package execution order
• Ensure that packages execute in the correct order
• One step is completed before the next is started

• Execution dependencies
• Some packages require others to have already run due
to data dependencies
Configuring Execution Context

• Create a credential in the SQL Server instance


• Create a SQL agent proxy that maps to the
credential
• Activate it for the SQL Server Integration Services
package job subsystem
• Configure package execution job steps to run as
the proxy
Where to Handle Notifications

• Notifications can be handled from:


• SSIS package, using SendMail task
• SQL Server Agent job, using operators
Where to Handle Logging

• Logging can be handled by:


• SSIS: consider using Integration Services Dashboard
• SQL Server Agent job: logging enabled by default
Combining SSIS Tasks with Other Tasks

• Create modular packages


• Augment SSIS packages with SQL Agent tasks
• Ensure all processes are documented
Implementing SSIS Agent Jobs and Schedules

1. Select a Windows account that SQL Server


Agent will use
2. Create job subsystem proxies if required
3. Set SQL Server Agent service to start
automatically
4. Create the required jobs and schedules
Lab: Deploying and Configuring SSIS Packages

Exercise 1: Creating an SSIS Catalog


Exercise 2: Deploying an SSIS Project
Exercise 3: Creating Environments for an SSIS Solution
Exercise 4: Running an SSIS Package in SQL Server
Management Studio
• Exercise 5: Scheduling SSIS Packages with SQL Server
Agent
Logon Information
Virtual machine: 20767C-MIA-SQL
User name: ADVENTUREWORKS\Student
Password Pa55w.rd

Estimated Time: 45 minutes


Lab Scenario

You have completed the SSIS project containing


the packages required for the data warehouse ETL
process. Now you must deploy the project to an
SSIS catalog, configure environments for dynamic
configuration, and schedule automatic execution
of packages.
Lab Review

Having completed this lab, you will now be able to:


• Create an SSIS catalog
• Deploy an SSIS project
• Create environments
• Run an SSIS package
• Schedule SSIS package execution
Module Review and Takeaways

• Review Question(s)

You might also like