SSIS Parameters vs Configurations New tricks in 2012 Allen Smith Sr Business Intelligence Consultant Cognitive Information Inc Specialize in BI and Data Warehousing since 99 Healthcare I nsurance ID: 767961
Download Presentation The PPT/PDF document "SSIS Parameters vs. Configurations" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
SSIS Parameters vs. Configurations New tricks in 2012!
Allen Smith Sr. Business Intelligence Consultant Cognitive Information, Inc. Specialize in BI and Data Warehousing since ‘99HealthcareInsurance Energy industries 8/10/2013 | Footer Goes Here 2 | Twitter: @CognitiveBI Email: asmith@cognitiveinfo.com
SSIS Parameters vs. Configurations SQL 2008 R2 Configurations StaticBasic SQL tableDynamic SQL 2012Parameters Project LevelPackage LevelCatalogs
Configurations in SSIS 2008 R2 Package Configurations Static Configurations – entering property values Dynamic ConfigurationsXML filesDatabase lookupEnvironment Variables CombinationsExample: Environment Variables point to Database ServerDatabase stores other variables
Static Properties
Configuration Wizard to SQL Table SSIS Menu ‘Package Configurations’ Wizard will create connection and table Select Properties to export to SQL
Package Configuration Wizard ‘PackagePath’ locates property ‘ConfiguredValueType’ Must match Type ‘ConfiguredValue’Stores value Allows you to write to a table any property for: The PackageAny ConnectionAny Containers Any VariablesAny Task
Demo 2008R2 1. Static 2. SQL Configuration
2008R2 Dynamic Configuration
2008R2 Dynamic Configuration
2008R2 Dynamic Configuration
2008R2 Dynamic Configuration Order Matters!
Static or Dynamic? (2008R2) Get BIDSHelper! http ://bidshelper.codeplex.comStaticSQL ConfigDynamic SQL Static Values will be over written by configuration!
Demo 2008R2 3. Environment Variable Loads SQL Configuration
SQL2008 R2 Summary Configurations Allow flexible deployment Can be static or dynamicStill supported in SQL 2012 Order Matters Top configuration applied firstGet BIDS Helper to quickly identify dynamic connections.
New in SSIS 2012 Deployment Options Server Management EnvironmentsSSISDB CatalogDevelopment Environment EnhancementsParametersPerformance Improvementshttp://msdn.microsoft.com/en-us/library/bb522534. aspx
Deployment Choices… Project Deployment Model Package Deployment Project deployed as a unit Packages deployed separatelyParameters are used to assign values to package properties.Use Configurations Deployed to the SSISDBCopied to file system or saved to MSDBParameters are managed with SQL: set_execution_parameter_valueConfigurations are updated in SQL table.Project Parameters NOT called during execution. FAIL!http://msdn.microsoft.com/en-us/library/hh213290.aspx
Parameter Types in SQL 2012? Project Parameters Available to all Packages in the Project If Parameter = ‘Gaudy’Package ParametersAvailable only to objects in the PackageIf Parameter = ‘Orange’
Add Project Parameter Add Project Parameters Names are Case SenSiTiVe Be careful of Data Types
Data Type Confusion SSIS Data Type SSIS Variables SQL Server Data Type .NET Managed Type DT_WSTRStringnvarchar, nchar,Sql_variant, xml System.StringDT_DBTIMESTAMPDateTimeSmalldatetime, datetimeSystem.DateTimeDT_DBTIME2TimeSystem.TimeSpanDT_NUMERICDecimalNumeric, decimalSystem.DecimalDT_GUIDUniqueidentifierSystem.Guid DT_BOOLBooleanbitSystem.boolean DT_I2Int16SmallintSystem.Int16 SSIS will default to Unicode data types Sample list only. For full list see: http://msdn.microsoft.com/en-us/library/ms141036. aspx http://msdn.microsoft.com/en-us/library/cc716729. aspx
Create Parameter
Create Parameter Settings Parameterize box can: Apply existing parameters Create new parameterPackage scopeProject scopeSensitive = encrypt
Static or Dynamic? (2012) Expression Adorner New in 2012Shows that the property is dynamicStill recommend BIDSHelper…
Demo 2012 Static package Package Parameters Project ParametersBoth Package and Project Parameters
Catalogs, not just for Christmas SSIS Catalog Must be created Manage and query with T-SQLProjectsProject versioningPackagesParameters can be encrypted EnvironmentsObjects secured in folders
Edit Parameters in Catalog
CatalogEdit 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'DestinationFile_ConnectionString’,@object_name=N'SantasList 3 - Project Parameter.dtsx’ , @folder_name=N'OKCSQL-Demo’, @project_name=N'2012Parameters’, @value_type=V, @parameter_value=@varGO
CatalogEnvironments, Variables & Projects Create Environments Dev, Test, Prod, etc. Create Variables in each EnvironmentConfigure Project References to Environments Configure Project Parameter to use Environment Variable
Calling Catalog with Agent Agent Can Use default Parameters Manually set ParametersUse Environment to set all or some Parameters
Demo 2012 Catalogs Environments
SSIS Parameter Summary Parameters vs. Configurations D epends on VersionDepends on deployment decisionsParameter Type depends on deploymentBe careful about data types!SSIS Catalog in 2012 is very usefuland powerful
Questions??? Please fill out evaluations so I can continue to improve my sessions. This session will be at SQL Saturday #191, KC, MO http://sqlsaturday.com/191
References BIDSHelper http://bidshelper.codeplex.com /Professional Microsoft SQL Server 2012 Integration Servicesby Brian Knight, Erik Veerman, Jessica M. Moss, Mike Davis, Chris Rock 2008R2 – Defining a Configuration Approachhttp://msdn.microsoft.com/en-us/library/cc671625.aspx2012 SSIS Parameter Basicshttp://msdn.microsoft.com/en-us/library/hh213214. aspx2012 SSIS Cataloghttp://msdn.microsoft.com/en-us/library/hh479588.aspx