Today’s post is from Bob Bojanic – a developer on the SSIS Team.
---------------------------
There is often a need to update connection manager properties for subsequent package runs. Whether you need to point to a different server or change credentials, file locations or some other connectivity detail, all those properties will be exposed on connection managers. People often assign expressions to these properties, and that way add flexibility of parameterizing values associated with connection managers. This is a good practice and we encourage people to continue doing it, as it forces SSIS developers to think of exposing and documenting necessary input sets for their packages.
However, we recognize people will sometimes forget or miss to parameterize these properties. Whenever that happens, it is certain that a need will arise, in the production, to quickly change one of connection manager properties (i.e. point to a different server, share, etc.).
A new feature we are adding to SQL Server “Denali” version of SSIS will keep stress of admins and op engineers under control. We are making sure to automatically parameterize all properties (except read-only ones and those already parameterized) of connection managers when a project gets deployed to the SSIS server. We will do this for both, shared project connection managers and those that belong to packages.
Let us show how it works on a simple example. We are going to use a small data flow to export a table from a database to a flat file.
The OLE DB Source component will get data from the Product table in AdventureWorks as can be seen below.
The Flat File Destination component will send data to the “c:\test\product.txt” file.
There is no need for additional settings in order to parameterize properties of these two connection managers. We can simply deploy the project with our simple package to the local SSIS server.
The connection manager parameters will behave the same way the regular parameters do on the server. They can be set in the same dialogs and values from environments can be referenced in the same fashion they are used for other parameters.
Once the package is deployed we can see all the parameters generated from connection manager properties in Configure or Execute dialogs. We can also change values of those parameters in those two dialogs. If connection manager parameters are changes in the Configure dialog those values will be applied in all subsequent package executions and validations. On the other hand, if connection manager parameters are set in the Execute dialog, they will be applied only for a current package execution.
Here is the example of changes we did for the simple package we have just deployed. The connection manager pointing to the AdventureWorks database is changed to point to another server (sqlcldb2) and the initial catalog on that server has a different name (SSIS_AdventureWorks) as well.
The flat file connection manager is changed as well, so the new connection string places our file (Product.txt) to a location on the drive d.
After we execute the package, the execution overview report will have the following section that lists our changed properties and their assigned values.
As you could see, the automatic connection manager property parameterization feature in SQL Server “Denali” is going to make updates, of connection parameters in production, an extremely simple task. That should increase flexibility in how packages are linked with external dependencies. An important class of configurations/updates will be allowed without a need to redeploy packages.
Let us know how you like this feature.