vrijdag 8 mei 2015

SSIS 2012 / 2014 SSIS Catalog Package Protection level

The last 2 days I’ve spend too much time struggling with the protection level of my SSIS Catalog deployed packages. So I investigated why I was having all those problems, and I will give you an overview of how to solve it.


We have our SQLServer 2014 SSIS servers installed for more than half a year, and after some initial problems (Double Hop, executing Packages from within a Stored Procedure,…) and going through a learning curve, we were really comfortable and pleased with this SSIS Catalog environment, so we wanted to convert some “older” (2008R2) packages and jobs, and deploy them to our new environment.

The Deployment of our packages is done by a PowerShell script to reduce the manual work.


Some of our packages use SQLServer authentication :-S, because they need to connect to an untrusted domain. There for the user and password were specified in the connection manager.

The Protection Level of the project and the packages was set “EncryptSensitiveWithPassword”, which allows us to run the packages during the development (Data Tools), and allowed us also to Decrypt the package at runtime from within a command or job:

/FILE "C:\Program Files\Microsoft SQL Server\100\DTS\Packages

       \MainSSISPackage.dtsx" /DECRYPT P@ssWord /CONFIGFILE "C:\Program Files

       \Microsoft SQL Server\100\DTS\Packages\DataConfig.dtsConfig"


This is where all troubles began. Apparently SQLServer 2012 / 2014 SSIS catalogs have their own encryption “system” (ServerStorage), and the configuration of the Decryption password seems to be “missing”.

(More detailed information about SSIS Encryption can be found at: https://msdn.microsoft.com/en-us/library/ms141747.aspx)



After the conversion of the package (as-is: “EncryptSensitiveWithPassword”) and deploy it to the SSIS Catalog, we create our Job, based on our “experience” with the previous version like this:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Start Demoload Package',









                @os_run_priority=0, @subsystem=N'SSIS',

                @command=N'/ISSERVER "\"\SSISDB\Acc\MyProject\DemoUpload.dtsx\"" /DECRYPT CalculatorDbUpload /SERVER "\"env-ssis-asql\"" /Par "\"$Project::Compatibilitygroup\"";9616EE37E32A8D9 /Par "\"$Project::Country\"";ES /Par "\"$Project::DestinationDb\"";MyDB /Par "\"$Project::DestinationServer\"";"\"databasecalculator.sqldo.acc.intacc.conseur.org\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',




When executing the job, or executing the package directly, we receive an error:


Failed to decrypt an encrypted XML node because the password was not specified or not correct

So, the decrypt option in our job statement didn’t do the trick, and, as a result of this, the password for our user (sensitive data) couldn’t be decrypted, so we can’t connect to our Destination connection manager.

Although some of the posts I found on this on the internet tell me that “EncryptSensitiveWithPassword” is the correct option to deploy packages to the SSIS Catalog, it doesn’t work for me. A possible reason for can be the fact that I deploy my packages by a PowerShell script, and not by the “default” Deploy option in the Data Tools.

So, when looking a little bit further, I blogs of people who had a similar problem, and putting the protection level to “DontSaveSensitive” did the trick for them. When they deployed their package, the SSIS Catalog Automagically ( ;-) ) knew the password. Unfortunately, also this solution didn’t work for me. Again, maybe my way of Deploying (PowerShell scripts) makes the difference.

The “Decryption” of the package is ok (there is no encryption anymore), but the user password is missing.


After being looking for a way to include the Package Decryption Password in my PowerShell script, which I didn’t found, I found that there is a configuration option at the package level to use Environment Variables to set my user Password. In that way they are not included in the package, and thus, not encrypted by the Package Password anymore.


So, I expanded my PowerShell script a little bit:


$SourceDB = "OCU_Small"

$DestinationDB = "CalculatorDB"

$SourcePW = "MySourcePw"

$DestinationPW = "MyDestinationPw"


$environment.Variables.Add("SourceDB",[System.TypeCode]::String, $SourceDB, $false,"SourceDB")

$environment.Variables.Add("DestinationDB",[System.TypeCode]::String, $DestinationDB, $false,"DestinationDB")

$environment.Variables.Add("DestinationPW",[System.TypeCode]::String, $DestinationPW, $false,"DestinationPW")

$environment.Variables.Add("SourcePW",[System.TypeCode]::String, $SourcePW, $false,"SourcePW")




And I bound the environment Variables to my package in the configuration:

And then, the package did run successfully directly started from the catalog!!!


Unfortunately, it still fails when called from the job (it doesn’t take the environment variable). So let’s create an environment reference to solve this, and specify it in the Package Call from the job:





DECLARE @reference_id bigint


EXEC [SSISDB].[catalog].[create_environment_reference]

    @environment_name = N'MyEnvironmentName'

,   @reference_id = @reference_id OUTPUT

,   @project_name = N'MyProjectName'

,   @folder_name = N'MyFolderName'

,   @reference_type = R



 --> Result: 79 (= reference_id)


EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name= N'Start Demoload Package',









                @os_run_priority=0, @subsystem=N'SSIS',

                @command=N'/ISSERVER "\"\SSISDB\Acc\MyProject\DemoUpload.dtsx\"" /ENVREFERENCE 79 /SERVER "\"env-ssis-asql\"" /Par "\"$Project::Compatibilitygroup\"";9616EE37E32A8D9 /Par "\"$Project::Country\"";ES /Par "\"$Project::DestinationDb\"";MyDB /Par "\"$Project::DestinationServer\"";"\"databasecalculator.sqldo.acc.intacc.conseur.org\"" /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E',




Still no success :-s .


Finally, calling the package with the “catalog.start_execution” Stored Procedure, and the reference_id:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution]


        @execution_id=@execution_id OUTPUT,






Select @execution_id

DECLARE @var0 sql_variant = N'9616EE37E32A8D9'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'Compatibilitygroup', @parameter_value=@var0

DECLARE @var1 sql_variant = N'IT'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'Country', @parameter_value=@var1

DECLARE @var2 sql_variant = N'MyDestinationDB'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'DestinationDb', @parameter_value=@var2

DECLARE @var3 sql_variant = N'MySQLServer.MyDomain.org'

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=20, @parameter_name=N'DestinationServer', @parameter_value=@var3


EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var5

EXEC [SSISDB].[catalog].[start_execution] @execution_id




Geen opmerkingen: