2012-04-28 (sql saturday 140 perth) migrating deployment and config to ssis 2012_merchant
TRANSCRIPT
Migrating Deployment and Configuration to SSIS 2012 Bhavik Merchant [email protected]
Twitter: @BhavikMerchant
my other passions
• Cricket (Watching), Squash (Playing)
• Travelling.. Anywhere
• Whisky
Agenda
Speaker Intro
Goals
Looking back
New technologies
Demo
Q&A
Did I hear a chirp?
@BhavikMerchant
#SQLSAT140
A little about me..
Background BI Team Manager and Lead MSBI Consultant at CSG
Certified End-to-End Microsoft BI practitioner
Microsoft vTSP for BI
Trainer (SSAS, SSIS, SSRS, PowerPivot, Sharepoint BI)
Experience Started in Web Development and SysAdmin
Thereafter, been in BI for about 7 years. SQL 2000 to 2012; MOSS 2007 to SP2010
Presentation Goals
Identify config/deployment challenges in SSIS 2008 R2
Learn about new paradigms/architecture in SSIS 2012
Understand migration options
Put knowledge into action via demos
Identify pitfalls in the demos
Audience Poll
Who is currently using
Direct (Static) XML or SQL Server config
Indirect (dynamic) XML or SQL Server config
Others e.g. Registry?
Custom configuration (e.g. script, component)
Can anyone share a war story? I have some, more on this later!
History - The Direct Approach
Direct XML - Options
Direct SQL - Options
Common Problem – Moving environment
Direct works great until you need to promote to TEST, PROD
Path to XML file, or connection to SQL is hardcoded
Things may not be consistent (shares, drive letters, DB names, table names)
What you change depends on your design choices
XML Config - Shared storage vs local, file naming
SQL Config – connection strings
History - The Indirect Approach
Indirect Config
Same concept for both XML and SQL
Store location of XML file or connection string to SQL in Environment Variable
At face value, appears to solve the environment crisis
Indirect is the answer?
Hmmm… What about
Clusters – no “localhost”
Multiple instances on server (e.g. DEV, TEST)
A properly secured environment – config order
External overrides from job
Child packages
Metadata driven framework – custom config tables
Boiling it all Down…
What is the crux of the problem?
There isnt any concept of a solution-wide configuration
Without nifty customisation, we cant simply pick a set of values for an agent job
We cannot escape environment variables, and with them we cant do multiple environments on a box
Parent-child development can be restrictive
Never Fear, Our Hero Returns!
Introducing Project Deployment
Groups an SSIS solution into a logical unit
Different to SSIS 2005-2008R2 .. how?
Shared entities across the Project. Will see in demo
Lots more features for running and configuring
How to Migrate? - Option 1
If it aint broke, don’t fix it – legacy config
It really is time for a DEMO!
How to Migrate? - Option 2
Convert to Project Deployment Model
Another DEMO!
Projects
Parameters
Project Connections
Parameters and Solution Configurations
Deployment
Final DEMO
The SSIS Catalog
Configurations and Execution
Environments
Benefits of the New Model
Proper support for environments = simplified deployment and maintenance = no hacking the default SQL config = multiple instances no longer an issue
Reduced use of variables = cleaner package, variables more traditionally
Configurations are managed centrally = easier development and debugging
Resources
Intro series to new SSIS features:
http://blogs.msdn.com/b/mattm/archive/2011/07/12/30-days-of-ssis.aspx
Jamie Thomson’s great mostly SSIS blog:
http://sqlblog.com/blogs/jamie_thomson/
Microsoft SSIS 2012 migration tips:
http://msdn.microsoft.com/en-us/library/hh667275.aspx
Questions?
Please complete an evaluation form for this session
…and thanks again to our awesome sponsors!