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.

Scenario

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.

Case

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)

 

Error

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',

                @step_id=1,

                @cmdexec_success_code=0,

                @on_success_action=1,

                @on_success_step_id=0,

                @on_fail_action=2,

                @on_fail_step_id=0,

                @retry_attempts=0,

                @retry_interval=0,

                @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',

                @database_name=N'master',

                @flags=0

 

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.

Solution

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")

$environment.Alter()

...

 

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:

Code:

USE SSISDB

GO

 

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

SELECT

    @reference_id

 --> Result: 79 (= reference_id)

 

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

                @step_id=1,

                @cmdexec_success_code=0,

                @on_success_action=1,

                @on_success_step_id=0,

                @on_fail_action=2,

                @on_fail_step_id=0,

                @retry_attempts=0,

                @retry_interval=0,

                @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',

                @database_name=N'master',

                @flags=0

 

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]

        @package_name=N'DemoUpload.dtsx',

        @execution_id=@execution_id OUTPUT,

        @folder_name=N'ACC',

        @project_name=N'DemoUpload',

        @use32bitruntime=False,

        @reference_id=79

 

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

GO

 

Succeeded!!!!

Getting all (calendar)days within a period

Goal

Sometimes you need a list with all days within a specific period, for example to outer join with other tables to have a complete calendar overview. Here is a simple way to get those days.

Solution

Create the Table-Valued Function:

 

CREATE FUNCTION fnGetCalendarDaysInPeriod

(

       @StartDate DATE,

       @EndDate DATE

)

RETURNS TABLE

AS

RETURN

(

       WITH mycte AS

       (

         SELECT CAST(@StartDate AS DATE) DateValue

         UNION ALL

         SELECT  DATEADD(dd, 1, DateValue)

         FROM    mycte  

         WHERE   DATEADD(dd, 1, DateValue) < @EndDate

       )

 

       SELECT  DateValue

       FROM    mycte

)

 

The Function CALL:

DECLARE @StartDate DATE

DECLARE @EndDate DATE

 

SET @StartDate = DATEADD(yy, -2, GETDATE())

SET @EndDate = GETDATE()

 

 

SELECT *

FROM fnGetCalendarDaysInPeriod (@StartDate,@EndDate)

OPTION (MAXRECURSION 0)

Note

Use the Option “MAXRECURSION 0”, otherwise you will exceed the limit of maximum 100 recursions (Default Value).  The “OPTION” clause can be used only at the statement level, so you can’t put this in a “RETURN”.

 

Workaround MAXRECURSION problem:

Return a Table Variable so you can put the “OPTION(MAXRECURSION)” in the “INSERT INTO” statement

CREATE FUNCTION [dbo].[fnGetCalendarDaysInPeriod]

(    

      @StartDate              DATETIME,

      @EndDate                DATETIME

)

RETURNS 

@SelectedRange    TABLE

(IndividualDate DATETIME)

AS

BEGIN

       WITH mycte AS

       (

         SELECT CAST(@StartDate AS DATE) DateValue

         UNION ALL

         SELECT  DATEADD(dd, 1, DateValue)

         FROM    mycte  

         WHERE   DATEADD(dd, 1, DateValue) < @EndDate

       )

 

       INSERT INTO @SelectedRange

       SELECT  DateValue

       FROM    mycte

       OPTION (MAXRECURSION 0);

 

      RETURN

END

GO

 

This will make life much easier for the Developers; they don’t have to care about the “MAXRECURSION” anymore.

Note 2

Using a table valued function can be bad for performance due to the fact that it's treated like a table except there are no statistics available for SQL Server to base a good execution plan on. SQLServer will estimate the function as returning a very small number of rows. If it returns a larger number of rows, then therefore the plan generated could be a lot less than optimal.