Denali CTP 1: SSIS Parameters – Bring Them On!

The new version of SQL Server code-named Denali brings the concept of Parameters to SSIS. At first glance, I can say they close some of the existing gaps in the package configuration model form earlier versions and it seems a big step in the right direction for the product usability.
If you are interested in learning about the Developer Experience and Usability enchantments, you can read mi earlier post.



In a nutshell,you can think of SSIS parameters as the input parameters you would use in a function or stored procedure. See the link at the end of the post to learn how they work.

The Good

Flexibility. They can be declared at the project and/or package level and can take 3 types of values: Design default value, Server Default value, and Execution value. The last 2 type of values become relevant after the SSIS project has been deployed to the server.
Simplicity. SSIS parameters seems easier to understand and work with. You add them to the packages or projects and reference them via expressions. This is especially true when you compare them to package configurations in the previous version of the product.

Parameter –> Object Property (via expression)

Security. You can mark parameters as sensitive and have their values stored encrypted when the package is deployed. This is helpful when you have parameter values containing sensitive information like IDs or password. Notice that parameter values are not encrypted at design time (BIDS) even if you marked them as sensitive, so you are still responsible of protecting that information in developer’s machine, perhaps leveraging the ProtectionLevel property of the package.

Caveats

Parameters are available only in SSIS projects using the new Project Deployment Model. There are 2 models in the new version of SSIS: Project Deployment Model and Legacy Deployment Model. I will try to cover the differences about this model in a later post, but for now think about them as present and past. The legacy model promises to let you work on the way you were used to in the previous version of the product, but you will be forbidden from using parameters and the new server based SSIS catalog along with its deployment model. Any new project uses the new model by default  and there is a migration path from and to the Legacy model.
Learning curve. Are you of the type that don’t read the instructions and try to figure things out at your own? That did not work for me this time. I started playing around with the parameters and the deployment process, but could not get them to work quite well. It was not until I went trough some of the Wiki articles and videos some of the Microsoft folks put together that things starting to click in my head. You will need to understand how you could use 3 different type of values and how one could override the other. In the server side, you would need to get familiar with the concepts of Catalogs, Folder and Environments and how they play along with parameters. This is not rocket science, and certainly easier than package configurations to explain.

Final Comments and References

Parameters, in my opinion, are in overall better than the old package configuration model that was hard to explain, understand, and manage - especially for new users -, and they also seem a good fit for all the ETL scenarios I come across in a regular basis. However, I think the server part of the story around parameters may need some tweaking as there are some aspects that look little confusing and that could impact negatively the user experience. More on that later.

To learn more about parameters:
SQL Server “Denali” CTP1 - Integration Services (SSIS) Parameters