2012-04-28 (sql saturday 140 perth) migrating deployment and config to ssis 2012_merchant
TRANSCRIPT
![Page 1: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/1.jpg)
Migrating Deployment and Configuration to SSIS 2012 Bhavik Merchant [email protected]
Twitter: @BhavikMerchant
my other passions
• Cricket (Watching), Squash (Playing)
• Travelling.. Anywhere
• Whisky
![Page 2: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/2.jpg)
Agenda
Speaker Intro
Goals
Looking back
New technologies
Demo
Q&A
![Page 3: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/3.jpg)
Did I hear a chirp?
@BhavikMerchant
#SQLSAT140
![Page 4: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/4.jpg)
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
![Page 5: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/5.jpg)
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
![Page 6: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/6.jpg)
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!
![Page 7: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/7.jpg)
History - The Direct Approach
![Page 8: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/8.jpg)
Direct XML - Options
![Page 9: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/9.jpg)
Direct SQL - Options
![Page 10: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/10.jpg)
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
![Page 11: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/11.jpg)
History - The Indirect Approach
![Page 12: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/12.jpg)
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
![Page 13: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/13.jpg)
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
![Page 14: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/14.jpg)
Boiling it all Down…
![Page 15: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/15.jpg)
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
![Page 16: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/16.jpg)
Never Fear, Our Hero Returns!
![Page 17: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/17.jpg)
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
![Page 18: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/18.jpg)
How to Migrate? - Option 1
If it aint broke, don’t fix it – legacy config
It really is time for a DEMO!
![Page 19: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/19.jpg)
How to Migrate? - Option 2
Convert to Project Deployment Model
Another DEMO!
Projects
Parameters
Project Connections
Parameters and Solution Configurations
![Page 20: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/20.jpg)
Deployment
Final DEMO
The SSIS Catalog
Configurations and Execution
Environments
![Page 21: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/21.jpg)
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
![Page 22: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/22.jpg)
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
![Page 23: 2012-04-28 (SQL Saturday 140 Perth) Migrating Deployment and Config to SSIS 2012_merchant](https://reader033.vdocuments.us/reader033/viewer/2022060205/55a1dfbf1a28ab27778b46f7/html5/thumbnails/23.jpg)
Questions?
Please complete an evaluation form for this session
…and thanks again to our awesome sponsors!