![Page 1: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/1.jpg)
SSIS Parameters vs. Configura3ons
New tricks in 2012!
![Page 2: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/2.jpg)
SSIS Parameters vs. Configura3ons • Christmas Past – SQL 2008 R2 – Configura3ons
• Sta3c • Basic SQL table • Dynamic
• Christmas Present SQL 2012 – Parameters – Variables
![Page 3: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/3.jpg)
Allen Smith
• Business Intelligence developer • Cogni3ve Informa3on, Inc. in Edmond, OK • Specialize in BI and Data Warehousing in healthcare, insurance and energy industries.
• Email: [email protected]
12/10/12 | Footer Goes Here 3 |
Twi\er: @Cogni3veBI
![Page 4: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/4.jpg)
Ghost of Christmas Past SSIS 2008 R2
• Package Configura3ons – Sta3c Configura3ons – Dynamic Configura3ons
• XML files • Database lookup • System Variables
– Combina3ons • Example: System Variables point Database Server • Database stores other variables
![Page 5: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/5.jpg)
SQL Configura3on Table
• SSIS Menu ‘Package Configura3ons’ – Wizard will create connec3on and table
• Select Proper3es to export to SQL
![Page 6: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/6.jpg)
Opera3ng System • OS Loads two (2) Environmental Variables: • SSIS_Config_Server_Name • SSIS_Root_DIR
SSIS Package • Loads OS Variables into Package loca3ons • Load order is important.
SSIS Connec3on • SSIS Loads Server Name Variable into a ‘Connec3on Manager’
SSIS Loads Variables • Database table used for remaining variables.
2008R2 Dynamic Configura3on
![Page 7: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/7.jpg)
Opera3ng System • OS Loads two (2) Environmental Variables: • SSIS_Config_Server_Name • SSIS_Root_DIR
SSIS Package • Loads OS Variables into Package loca3ons • Load order is important.
SSIS Connec3on • SSIS Loads Server Name Variable into a ‘Connec3on Manager’
SSIS Loads Variables • Database table used for remaining variables.
2008R2 Dynamic Configura3on
![Page 8: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/8.jpg)
Opera3ng System • OS Loads two (2) Environmental Variables: • SSIS_Config_Server_Name • SSIS_Root_DIR
SSIS Package • Loads OS Variables into Package loca3ons • Load order is important.
SSIS Connec3on • SSIS Loads Server Name Variable into a ‘Connec3on Manager’
SSIS Loads Variables • Database table used for remaining variables.
2008R2 Dynamic Configura3on
![Page 9: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/9.jpg)
Opera3ng System • OS Loads two (2) Environmental Variables: • SSIS_Config_Server_Name • SSIS_Root_DIR
SSIS Package • Loads OS Variables into Package loca3ons • Load order is important.
SSIS Connec3on • SSIS Loads Server Name Variable into a ‘Connec3on Manager’
SSIS Loads Variables • Database table used for remaining variables.
2008R2 Dynamic Configura3on
![Page 10: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/10.jpg)
Dynamic or Sta3c?
Get BIDSHelper! h\p://bidshelper.codeplex.com • Sta3c
• SQL Config
• Dynamic SQL Sta3c Values will be over wri\en by configura3on!
![Page 11: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/11.jpg)
Demo
![Page 12: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/12.jpg)
SQL2008 R2 Summary
• Configura3ons – Allow flexible deployment – Can be sta3c or dynamic – S3ll supported in SQL 2012
• Get BIDS Helper to quickly iden3fy dynamic connec3ons.
![Page 13: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/13.jpg)
New in SSIS 2012
• Deployment Op3ons • Server Management – Environments – SSISDB Catalog
• Development Environment Enhancements – Parameters
• Performance Improvements h\p://msdn.microsoi.com/en-‐us/library/bb522534.aspx
![Page 14: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/14.jpg)
Deployment Choices… Project Deployment Model Package Deployment
Project deployed as a unit Packages deployed separately
Parameters are used to assign values to package proper3es.
Use Configura3ons
Deployed to the SSISDB Copied to file system or saved to MSDB
Parameters are managed with SQL: set_execu3on_parameter_value
Configura3ons are updated in SQL table.
Project Parameters NOT called during execu3on. FAIL!
h\p://msdn.microsoi.com/en-‐us/library/hh213290.aspx
![Page 15: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/15.jpg)
Parameter Types in SQL 2012?
Project Parameters • Available to all Packages in the Project
• If Parameter = ‘Gaudy’
Package Parameters • Available only to objects in the Package
• If Parameter = ‘Orange’
![Page 16: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/16.jpg)
Add Project Parameter
• Add Project Parameters • Names are Case SenSiTiVe • Be careful of Data Types
![Page 17: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/17.jpg)
Data Type Confusion SSIS Data Type SSIS Variables SQL Server Data
Type .NET Managed Type
DT_WSTR String nvarchar, nchar, Sql_variant, xml
System.String
DT_DBTIMESTAMP DateTime Smalldate3me, date3me
System.DateTime
DT_DBTIME2 Time System.TimeSpan
DT_NUMERIC Decimal Numeric, decimal System.Decimal
DT_GUID Uniqueiden3fier System.Guid
DT_BOOL Boolean bit System.boolean
DT_I2 Int16 Smallint System.Int16
SSIS will default to Unicode data types Sample list only. For full list see: h\p://msdn.microsoi.com/en-‐us/library/ms141036.aspx h\p://msdn.microsoi.com/en-‐us/library/cc716729.aspx
![Page 18: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/18.jpg)
Create Parameter
![Page 19: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/19.jpg)
Create Parameter Setngs
• Parameterize box can: – Apply exis3ng parameters
– Create new parameter • Package scope • Project scope • Sensi3ve = encrypt
![Page 20: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/20.jpg)
Expression Adorner
• New in 2012 • Shows that the property is dynamic
![Page 21: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/21.jpg)
Christmas Catalog!
• SSIS Catalog – Must be created – Manage and query with T-‐SQL
• Projects – Project versioning
• Packages • Parameters
– can be encrypted • Environments
– Objects secured in folders
![Page 22: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/22.jpg)
Edit Parameters in Catalog
![Page 23: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/23.jpg)
Edit Parameter with T-‐SQL DECLARE @var sql_variant = N'C:\SSIS_DEV\FolderC\GoodBoysAndGirls.txt' EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name=N'Des3na3onFile_Connec3onString’ ,@object_name=N'SantasList 3 -‐ Project Parameter.dtsx’ , @folder_name=N'OKCSQL-‐Demo’ , @project_name=N'2012Parameters’ , @value_type=V , @parameter_value=@var GO
![Page 24: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/24.jpg)
Environments, Variables & Projects
• Create Environments – Dev, Test, Prod, etc.
• Create Variables in each Environment
• Configure Project References to Environments
• Configure Project Parameter to use Environment Variable
Huh? Demo Please
![Page 25: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/25.jpg)
SSIS Parameter Summary
• Parameters vs. Configura3ons depends on deployment decisions
• Parameter Type depends on deployment • Be careful about data types! • SSIS Catalog is very useful and powerful
![Page 26: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/26.jpg)
References • Professional Microsoi SQL Server 2012 Integra3on Services
– by Brian Knight, Erik Veerman, Jessica M. Moss, Mike Davis, Chris Rock • 2008R2 – Defining a Configura3on Approach
– h\p://msdn.microsoi.com/en-‐us/library/cc671625.aspx • 2012 SSIS Parameter Basics
– h\p://msdn.microsoi.com/en-‐us/library/hh213214.aspx • 2012 SSIS Catalog
– h\p://msdn.microsoi.com/en-‐us/library/hh479588.aspx
• Crea3ve Commons A\ribu3ons: h\p://www.flickr.com/photos/gazeronly/
• h\p://www.flickr.com/photos/calsidyrose/
![Page 27: SSIS#Parameters#vs.# Configuraons# - Cognitive Info](https://reader031.vdocuments.us/reader031/viewer/2022013001/61cb0a4dba5bd30f3938b55c/html5/thumbnails/27.jpg)
Merry Christmas and
Happy New Year!