SSIS setting up ADO.net source & connection using params/variables

Problem:

The .net 'OracleClient Data Provider' does not appear to save the password locally so fails when run in visual studio - but runs when deployed and the password is set via the configuration in SSMS. The .net 'ODP.NET, Managed Driver' works locally, but does not work when deployed on servers (requires components to be installed). This means that two connections are setup in the package; the Managed Driver provider connection is used locally, then the sources are switched to use the OracleClient provider connection before being deployed. And that is a pain.

Solution:

If the ConnectionString value of the OracleClient provider is setup in the expressions then it will work because the password is explicitly set. However, the connectionstring needs to vary depending on where (what environment) the package is deployed, so it needs to be in something that can be configured after deployment. Putting the connection string in a param means it is visible to people from SSMS; making the param sensitive stops it being visible but makes it difficult to know how else this package is configured (e.g. what server it is connecting too).

Get around this by having the password in a sensitive param and the rest of the connectionstring in a normal param and combine them at run time. Since params are readonly the result of the combination has to put in a variable, and the connection's connectionstring linked to that variable.

At design time if that variable does not hold a valid connectionstring then Visual Studio will freak out and mark the connection as offline, so need to have a valid connectionstring in the variable as well.

tl;dr:

• Have parts (most of it) of the connection string in a non-sensitive param
• Have the password in its own sensitive param
• Build the connection string when the package starts
• Params are readonly, so store the constructed connection string into a variable
• Link the ConnectionString value of the connection to the variable (using expressions)
• Keep the design time value of the variable populated to something valid so that at design time Visual Studio doesn't freak out and mark the connection as being in offline mode

Setup variable with a full connection string that can be used in Visual Studio

Link the connection's connectionstring to the variable using expressions - click on the connection and locate the expressions value in the properties

Create params to hold the password and rest of the connectionstring

When package starts have a script task to build the connectionString

On the script task give access to the params and the varaibles

In the script task build the connectionString from the params and 

Once package is deployed configure the param values as required