How to script and modify the variables of Package connection

How to script and modify the variables of Package connection

To iterate and modify the variables used by a package, follow these steps:

  1. Create a script task.
  2. Reference CozyRoc.SSISPlus library.Add Reference
  3. In the script get reference to a package connection manager.
  4. Get connection manager InnerObject property and cast it to PackageConnectionManager.
  5. Get Variables property, which is generic Dictionary type. It contains variable name to variable value pairs. Modify this collection, to setup package connection variables.

Check the sample script below demonstrating how to access package connection variables.


1
2
3
4
5
6
Imports System.Collections.Generic
Imports CozyRoc.SqlServer.SSIS
...
Dim manager As ConnectionManager = Dts.Connections("YourPackageConnection")
Dim packageCM As PackageConnectionManager = CType(manager.InnerObject, PackageConnectionManager)
Dim packageVars As Dictionary(Of String, String) = pac