Sql Server Data Tools – Integration Services Deployment Wizard–Error Message–“The variables were not found in the variable collection”

 

Background

Ran into problems deploying and running SSIS Packages.

Here is a brief write-up.

 

Scenario

Importing the package into SQL Server Data Tools.

And, also preparing to deploy it into .ispac file.

 

Error

Import Package into SSDT

Error :- Warning – The variables “$Project – Source [database collection]” were not found

Image

integrationServicesDeploymentWizard_Tab_SelectSource_FailedToDecrypt_20180206_0433PM

 

Textual

Warning: Failed to decrypt an encrypted XML node.
Verify that the Project was created by the same user.
Project load will attempt to continue without the encrypted information.

Package Deployment

Error :- Warning – The variables “$Project – Source [database collection]” were not found

 Image

deployPackage_subjectFailed_20180207_0226PM

 

Textual

 

Warning: The variables “Project-Source_Database_Connection” were not found in the variable collection.

The variables might not exist in the correct scope.

Error :

The expression for variable “Subject” failed evaluation.  There was an error in the expression.

 

TroubleShooting

Expression Builder

Accessed Expression builder for the variable, Subject, noted:

Image

expressionBuilder_20180207_0259PM

 

Text


"DataRefresh From "  +  SUBSTRING( @[$Project::Source_Database_Connection] , 13, 15) +" to " + SUBSTRING( @[$Project::Target_Database_Connection] , 13,15 )

 

Explanation

Noted that the expression makes reference to the Database Connection which can potentially contain sensitive data; such as server name and password.

 

Remediation

Outline

Correct package by:

  1. Removing potential sensitive data
  2. Change package properties
    • Security – “Protection Level”
      • Change from “EncryptSensitiveWithUserKey to “DontSaveSensitive”
  3. Place sensitive data in environment specific configuration files

 

Images

Package

Package – Property

Current
Image

package_properties_20180207_0554PM

 

Revised

Image

package_properties_ProtectionLevel_20180207_0601PM