Transcript Set Presentation Title in 40pt. No more than 2 lines

Erik Veerman
Mentor, Solid Quality Mentors
SQL Server MVP
Overcoming SSIS
Deployment and
Configuration Challenges
Speaker BIO – Erik Veerman
• SQL Server MVP (Most Valuable Professional)
• Mentor with Solid Quality Mentors
• ~15 years in the industry, 10 years SQL Server BI
•
•
•
•
experience (v1 SQL BI, beta SQL 7)… Telco,
Manufacturing, Finance, Insurance, Retail, Real Estate…
MS Worldwide BI Solution of the Year
SQL Server Magazine Innovator Cup
Microsoft Project REAL ETL Architect
Author:
•
•
•
•
SQL Server 2008 Integration Services Professional
MS Press SQL Server BI Training Kit (70-445) (70-448 in progress!)
SQL Server 2005 Integration Services Expert
SQL Server 2005 Integration Services Professional
//ATLANTAMDF
2
Industry-Leading Technical Experts, Authors, Speakers
Solid Quality™ Mentors are more than 100 of the world’s top technical
experts who specialize in assuring client success using integrated
Microsoft technologies.
“Catch the Solid Quality™ Advantage”
www.solidq.com
//ATLANTAMDF
PRACTICE AREAS
SERVICES
Relational Database Management
Advanced Public Training
Business Intelligence
Customized Private Training
Development Methodologies
Solution Delivery and Tuning
SharePoint Collaboration
Enhanced Mentoring Services
3
Let’s be honest…
• Who has had challenges with SSIS deployment or
configurations?
• Has anyone punched a wall or other visible
frustrations?
• Has anyone actually had to go to anger
management?
//ATLANTAMDF
4
Agenda
 Baby Steps: The Books-Online Basics
– Security: Encryption and Connections
– Configurations and Expressions
– Deployment
 The Problem: Bob
 Anger Management: The Solutions
//ATLANTAMDF
http://www.youtube.com/watch?v=C_gqZyUfjrk
BOL Basics: SSIS Package Security
 Security is comprised of several layers to support both SQL
and File System based scenarios
– Packages can be encrypted
– Packages can be digitally signed
– Packages can be stored in SQL DB and protected with roles
– Packages can be stored in the File System and protected with File
System Security
Package
Definition:
Storage:
//ATLANTAMDF
SSIS Package File (.DTSX):
Encrypted, Digitally Signed
File System:
File System Security
MSDB:
MSDB SQL Server
Roles
BOL Basics: SSIS Package Security
 Internal Package Security
–Package encryption – Used to encrypting connection
passwords or the entire package
–Package Password – Optional password applied to the
package to un-encrypt any encrypted contents
 External Package Security
–MSDB database roles for packages deployed to SQL
Server. Reader and Writer roles included for execution
and reading privileges
–File system security for packages deployed to server file
systems or file shares
//ATLANTAMDF
BOL Basics: SSIS Package
Encryption Levels
 Don’t Save Sensitive
 Encrypt Sensitive with User Key
 Encrypt Sensitive with Password
 Encrypt All with User Key
 Encrypt All with Password
 Server Storage
//ATLANTAMDF
demo …
Package Encryption and
MSDB
//ATLANTAMDF
BOL Basics: Securing Data
and Connections
 Data Security
–Data is not stored as part of a package
 Connection Schema Security
–Connection schema information will be stored in
packages depending on the uses (Data Flow
source/destinations and Execute SQL)
–Lock down package access when appropriate
 File system rights
 Package encryption/password
 Package roles and access
//ATLANTAMDF
demo …
Package Connections and
Encryption
//ATLANTAMDF
12
BOL Basics: SSIS Configurations
 Update package properties at execution time based on
external setting
– Variable properties
– Connection Properties
– Package properties
– Task and Container Properties
 Data Flow Transformation properties are
not configurable except at the Task level
SSIS Package
Connection 1 = XYZ
Variable 1 = XYZ
//ATLANTAMDF
Configuration
SSIS Package
• Connection 1 = ABC
• Variable 1 = ABC
SSIS Configuration Types
 XML File Configuration
– Direct path or…
– Environment Variable driven
 SQL Server Configuration
– Table Driven
– Filter Column grouping configurations
 Registry Configuration
 Parent Package Variable Configuration
– Inherit variables from master packages
 Environment Variable Configuration
//ATLANTAMDF
demo …
Package Configurations
//ATLANTAMDF
BOL Basics:
SSIS Property Expressions
 Runtime updates of Control Flow components
– Package level properties
– Task and Container properties
– Connections, too!
 Expressions assignable through property windows and task
editors
 SSIS expression
language based
SSIS Package
Variable1
C:\FileB
= C:\FileA
• Step 1: Run Data Flow
• Access Connection1
• Extract from C:\FileA
• Step 2: Update Variable
• Step 3: Run Data Flow
•Access Connection1
•Extract from C:\FileB
C:\FileA
C:\FileB @Variable1
Connection1 = Expression:
//ATLANTAMDF
SSIS Property Expressions
 Common Property Expression usage
–Dynamic SQL for non-parameterizable statements
–Connections, Checkpoints, Disable flag
–Loop Container enumeration functionality
 Variable values can be updated by expressions
through the EvaluateAsExpression and Expression
property of the variable
//ATLANTAMDF
demo …
Property Expressions
//ATLANTAMDF
BOL Basics: Deployment
 Deployment destinations
– File System (file share or local drive)
– MSDB Database (sysdtspackages90 or sysssispackages)
 Deployment options
– Manual windows command line script (Xcopy or Robocopy)
– DTUtil command line script (file system or MSDB)
– Deployment Wizard and Installer
//ATLANTAMDF
BOL Basics: Deployment
BIDS Project Folder
SSIS
Package
Test or
Production
Development
 File System
File System or Share
SSIS
Package
File Copy, DTUtil, Installer Set
//ATLANTAMDF
BIDS Project Folder
SSIS
Package
Test or
Production
Development
 MSDB
SSMS, DTUtil, Installer Set
MSDB Database
SSIS
Package
BOL Basics: Deployment Wizard
 Designer can build a deployment set which includes a
project's packages, configurations files and an installer file
 You move the installer file set to another
server/environment and execute to install packages to SQL
or file system
 Installer can "copy" misc files but will not install them, such
as custom components
 Deployment overwrites existing packages
//ATLANTAMDF
demo …
Package Deployment
//ATLANTAMDF
Agenda
 Baby Steps: The Books-Online Basics
– Security: Encryption and Connections
– Configurations and Expressions
– Deployment
 The Problem: Bob
 Anger Management: The Solutions
//ATLANTAMDF
• You want to live a normal IT life
but the problem follows you around
• Just when you think you’ve solved
the problem, there it is again
The Problem:
• You’re the expert, but the problem
will not go away
• Just knowing the baby steps doesn’t
get you far enough
24
SSIS Deployment Challenges
 Encryption causes your package to fail
 Connections and other properties aren’t updated
 Passwords are not available or too easily accessible
 Shared configurations require properties and connections exist
 SQL Configurations point to Dev database
SSIS Packages
SSIS Package
Server 2
Server 1
Encryption Failure:
//ATLANTAMDF
Package Deployed
Package
Fails!
SSIS Package
Encryption
With UserKey
Encryption
With UserKey
Connection A
MSDB or File System
Connection A
Can not
decrypt
UserKey
(New
Machine)
SSIS Deployment Challenges
SSIS Packages
SSIS Package
Dev Connection A
Dev Connection B
Server 2
Server 1
 Connections and other properties aren’t updated
Package Deployed
Dev Connection A
Dev Connection B
SSIS Package
Dev Connection A
Dev Connection B
Prod Connection A
Package Error!
Prod Connection B
Connections
Point to Wrong
Databases/Files
//ATLANTAMDF
MSDB or File System
SSIS Deployment Challenges
 SQL Configurations point to Dev database
SSIS Packages
SSIS Package
Dev Connection A
Dev Connection B
Dev Config DB
Server 2
Server 1
(or configuration database not accessible!)
MSDB or File System
SSIS Package
Package Deployed
1
1
2
Dev Connection A
Dev Connection B
Dev Config DB
2
Dev Config DB
Prod Config DB
Dev Connection A
Prod Connection A
Dev Connection B
//ATLANTAMDF
Config DB
Connection Not
Updated!
Prod Connection B
demo …
Pulling Your Hair Out!
//ATLANTAMDF
Agenda
 Baby Steps: The Books-Online Basics
– Security: Encryption and Connections
– Configurations and Expressions
– Deployment
 The Problem: Bob
 Anger Management: The Solutions
//ATLANTAMDF
Solution 1
Solution 1: Tie Bob to the Boat
• Pretend the problem is not there
• Try and man-handle the problem
• Make the problem worse
Solution 2
Solution 2: Blow up the house
• Give up
• Find a punching bag
• Declare to Bill Gates that you
are not a PC anymore
Dealing with Encryption
 Situation: All connections allow Windows Authentication
 Use Windows Authentication only for all connections
SSIS Packages
SSIS Package
Server 2
Server 1
 Turn off encryption (Set encryption to DontSaveSensitive)
//ATLANTAMDF
SSIS Package
Encryption:
DontSaveSensitive
Encryption:
DontSaveSensitive
Connection A/B/C
(Windows Auth)
MSDB or File System
Package Deployed
Connection A/B/C
(Windows Auth)
Dealing with Encryption
 Situation: One or more connections require password
 Option 1: EncryptSensitiveWithPassword
Pass in the package Password during execution
 Option 2: Set encryption to DontSaveSensitive
Use a Configuration to update the password
 Option 3: Deploy to MSDB, set encryption to ServerStorage
SSIS Packages
SSIS Package
Server 2
Server 1
Option 1:
Encryption:
EncryptSensitiveWithPassword
Connection A,
password = ABC
//ATLANTAMDF
Package Deployed
MSDB or File System
Package
Executed with
Encryption:
DTExec and the
EncryptSensitive/P switch
WithPassword
SSIS Package
Connection A,
password = ABC
Dealing with Encryption
 Option 2: Set encryption to DontSaveSensitive
SSIS Packages
SSIS Package
Server 2
Server 1
Use a Configuration to update the password
SSIS Package
Encryption:
DontSaveSensitive
Encryption:
DontSaveSensitive
Connection A,
password = [Blank]
MSDB or File System
Package Deployed
Connection A,
password = [Blank]
Config File
Config File
password =
ABC
password =
ABC
//ATLANTAMDF
demo …
Solving the Encryption
Problem
//ATLANTAMDF
Dealing with Connections
 Option 1: Use a File Configuration (direct or indirect path)
 Option 2: Use a File and SQL Configuration
 Option 3: Use Property Expressions and Variables
Server 2
Server 1
Option 1
SSIS Packages
SSIS Package
Dev Connection A
Dev Connection B
MSDB or File System
SSIS Package
Dev Connection A
Dev Connection B
Package Deployed
1
1
Prod Config File
Dev Config File
Dev Connection A
Dev Connection B
//ATLANTAMDF
2
2
Prod Connection A
Prod Connection B
Dealing with Connections
Server 2
Server 1
 Option 2: Use a File and SQL Configuration
SSIS Packages
SSIS Package
Dev Connection A
Dev Connection B
Dev Config DB
MSDB or File System
SSIS Package
Package Deployed
Dev Connection A
Dev Connection B
Dev Config DB
1
Dev Config File
1
2
SSIS Dev Config DB
Dev Connection A
Dev Connection B
//ATLANTAMDF
Prod Config File
2
3
3
SSIS Prod Config DB
Prod Connection A
Prod Connection B
demo …
Demo, Two-Pass
Configuration Approach
//ATLANTAMDF
Dealing with Connections
3
SSIS Packages
SSIS Package
Package Deployed
Connection A Variable
Connection B Variable
Connection A (Expression)
Connection B (Expression)
1
4
Server 2
Server 1
 Option 3: Use a Package Variable and Expression
Dev Config File
2
3
MSDB or File System
SSIS Package
Connection A Variable
Connection B Variable
Connection A (Expression)
Connection B (Expression)
1
4
Dev Config File
Dev Connection A
Dev Connection A
Dev Connection B
Dev Connection B
Makes sharing configurations easier!
//ATLANTAMDF
2
demo …
Demo, Variable Based
Configuration Approach
//ATLANTAMDF
BIDS Helper
 CodePlex.com – BIDS Helper
 Tools
– Deployment SSIS Packages tool
– dtsConfig File Formatter
– Expression and Configuration Highlighter
– Expression List
– Non-default properties list
– Variables Window Extensions
– FixRelativePaths
– …others…
//ATLANTAMDF
41
Package Storage Decision Flow
Are you using
SQL Server RDBMS?
No
Can your
Source Control tool handle
deployment
Yes
Are you the
DBA or do you have
DBA privileges?
No
No
Will you
have hundreds of SSIS
packages
Yes
Yes
Do you
Need a centralized
package backup and
Security
model
No
//ATLANTAMDF
Yes
Yes
Consider
storing your
packages in
the file system
No
Other factors:
cluster, multiple
servers, etc
Consider storing
your packages
on SQL Server
Yes
Yes
Will you
Have a lot of MasterChild packages?
No
Do you need
Pkg execution or read
privileges for a
team
42
No
Package Deployment Decision Flow
Are you storing
your packages in
SQL Server?
No
Can your Source
Control handle
deployment
Yes
No
Yes
Yes
Do your
Packages for
deployment always
come from the same
BIDS project?
Do you have
a set of packages
to be deployed per
deployment
No
Use the
Source Control
Deployment Tools
for file system
deployment
Manually
deploy
package
Files
No
Do you
Need to automate
deployment
No
Yes
Yes
//ATLANTAMDF
Use the SSIS
Deployment
Wizard
Use DTUtil
and Command
Line Scripting
Package Encryption Decision Flow (version 1)
EncryptSensitiveWithUserKey
Are you
Breathing?
Yes
EncryptAllWithUserKey
DontSaveSensitive
EncryptAllWithPassword
//ATLANTAMDF
EncryptSensitiveWithPassword
ServerStorage
Package Encryption Decision Flow (version 2)
Are you
Obsessive
Compulsive
(like Bob)?
EncryptSensitiveWithUserKey
No
Yes
EncryptAllWithUserKey
DontSaveSensitive
EncryptAllWithPassword
//ATLANTAMDF
EncryptSensitiveWithPassword
ServerStorage
Package Encryption Decision Flow (version 3)
Are you
the only person that
will ever edit/design
the package
Yes
Will
you always
use the same
Server?
No
No
Do you
need a class in
anger management
EncryptAllWithUserKey
No
Yes
EncryptAllWithPassword
//ATLANTAMDF
Yes
EncryptSensitiveWithUserKey
EncryptSensitiveWithPassword
DontSaveSensitive
ServerStorage
Package Encryption Decision Flow (version 4)
Are you
deploying
to SQL Server…
And are deploying using the
SSIS Deployment Tool…
Yes
And don’t use configurations…
And your passwords
don’t change…
No
See other
options…
//ATLANTAMDF
ServerStorage
Package Configuration Decision Flow
Do you have
passwords or
sensitive properties
in the package?
No
Yes
Do you
or your org have
a standard or pref. for
storing settings
in files
or DBs?
Tables
Is your server
locked down (file
and DB)?
Yes
No
Tables
Other factor:
Sharing configs
//ATLANTAMDF
Use SQL Config
with a File Config
as a pointer
Is it
easier in your org to
secure files or
tables?
Files
Use an indirect
(Env Var to
reference) file
configuration
Yes
Will the
file name or
Path change
between
servers?
No
Files
Use a direct file
configuration
Thank you