Project Parameters are Practical, T-SQL Tuesday #29
This month for T-SQL Tuesday I was invited to a SQL Server 2012 party by Nigel Peter Sammy (@NigelSammy). With the release of the newest version of SQL Server there is a lot of buzz about the new features and Nigel asked everyone to write about the feature we believe is the most useful. There are some very cool things that have been added in SQL Server 2012 (ColumnStore Indexes, xEvents, and Always On to name a few), but the world I live in these days is SQL Server Integration Services and there are some great new features in SSIS. The one I believe will be the most useful long-term is the addition of Parameters.
There will be some pain to convert from using SSIS configurations at the package level, but using parameters completes the solution of passing dynamic values to packages based on the environment.
The current method of managing configurations using files or a config database is managed during the deployment of SSIS packages and each configuration needs to be set up within every package. The old way is still supported which means if you enjoy managing all the moving parts you can keep doing it that way. Parameters can be used across all of the packages in an SSIS project or in individual packages. In addition when using the new project deployment you can pre-configure environments in the new SSIS package catalog. When SSIS projects are deployed using the new ISPAC files these environments can be selected and moving from Testing to QA to Stage to Production just got a little bit easier.
Within the package the Parameter are implemented and used in much the same way an SSIS variable. you can use parameters in expressions for tasks, data flow components, and connection managers.
You can also Paramerterize tasks properties by right clicking on the task and selecting the properties you want to assign to parameters. Very slick.
At my place of work we are not planning an upgrade for some time yet, but I know that once we move over to SQL Server 2012 we will be able to streamline our SSIS deployments using Parameters and assigning different Environments in the Integration Services Catalog.