Revealing content of encrypted SSISDB Environment variables – sp_SSISListEnvironment

You may come to a situation when you need to update a password or some other kind of sensitive information in the SSISDB catalog environment variables. Eg. password has changed etc. The easiest way to ensure that you change the password in all variables/environments would be listing all variables in the SSISDB catalog that have the original value.

SSISDB provides view [catalog].[environment_variables] where you can list all variables and their values. Unfortunately for values which Sensitive flag is set to True, the value is NULL. So this view will not help you.

In case you are db_owner in the SSISDB catalog, you can list the content of the [internal].[environment_variables] table. This table has two columns [value] for normal values and [sensitive_value] for variables which Sensitive flag is set to True. Unfortunately the content of the [sensitive_value] is encrypted.

Revealing sensitive_value

The content of the sensitive_value is encrypted by symmetric key, which is protected by certificate. For each environment in SSISDB there is generated an unique pair of symmetric key and certificate, which secures the content of sensitive variables.

As can be seen on the above image, the symmetric keys are named “MS_Enckey_Env_” + ID of the Environment. Similarly the Certificates are named “MS_Cert_Env_” + ID of the Environment.

As mentioned above. The symmetric key used to encrypt sensitive content is protected by corresponding certificate and each certificate in the SSIDB is protected by the database master key. In case you are db_owner of the SSISDB database, you have CONTROL permission on the Certificate and you can utilize it to decrypt the symmetric key and utilize the symmetric key to decrypt the content of sensitive variables.

Decryption of Sensitive values

To decrypt the sensitive values we can use DECRYPTBYKEY function. But to use that we need to call OPEN SYMMETRIC KEY for each unique symmetric key as the sensitive variables in different environments are protected by different symmetric keys.

The easiest way to decrypt the sensitive values is to utilize DECRYPTBYKEYAUTOCERT. This allows automatic decryption of encrypted data by providing only ID of the certificate which protects the symmetric key.

Thanks to that we can easily write:

SELECT
     [variable_id]
    ,[environment_id]
    ,[name]
    ,[type]
    ,[sensitive]
    ,[value]
    ,[sensitive_value]
    ,DECRYPTBYKEYAUTOCERT(CERT_ID(N'MS_Cert_Env_' + CONVERT(nvarchar(20),  [environment_id])), NULL, [sensitive_value]) AS DecryptedValue
    ,[base_data_type]
FROM [SSISDB].[internal].[environment_variables]

Result:
SSISDBDecrypted

With the above we can easily decrypt the [sensitive_value], however the decrypted content is not directly readable as the decrypted value is returned as binary data. To read the content we need to cast the binary data to appropriate data type. Normally we would need to case the content by the variable type, but SSISDB already has a function to get the content by its type. There is internal function in the catalog for that purposes: [internal].[get_value_by_data_type]. This simplifies revealing of the content.

SELECT
     [variable_id]
    ,[environment_id]
    ,[name]
    ,[type]
    ,[sensitive]
    ,[value]
    ,[sensitive_value]
    ,[internal].[get_value_by_data_type](DECRYPTBYKEYAUTOCERT(CERT_ID(N'MS_Cert_Env_' + CONVERT(nvarchar(20), [environment_id])), NULL, [sensitive_value]), [type]) AS DecryptedValue
    ,[base_data_type]
FROM [SSISDB].[internal].[environment_variables]

SSISDBDecryptedRevealed

sp_SSISListEnvironment

To simplify all the process you can utilize a sp_SSISListEnvironment stored procedure I have published on GitHub in my SQL Scripts repository.

The stored procedure allows listing environment variables and their values from multiple folders/environments and allows easy filtering based on folder/environment/variable names by utilizing wildcards as well it allows searching variables by the content (values).

By a simple call of the stored procedure we can easily receive similar results as above.

[sp_SSISListEnvironment]
    @folder = '%'
    ,@decryptSensitive = 1

Produces:

sp_SSISListEnvironment

 

For help and detailed usage you can invoke sp_SSISListEnvironment without any parameters.

The scripts grants execution of the stored procedure to the ssis_admin database role, so all ssis_admin members are able to decrypt the sensitive content. You should modify the GRANT in the script to meet your security requirements.

Advertisements

PP.SSIS Custom Components Updated on GitHub

An updated version of custom SSIS Data Flow and Control Flow components were published on GitHub

The update brings seamless Upgrade/Downgrade functionality in VS 2015 and VS 2017 by simple selection of Destination Platform (SSIS 2012 – SSIS 2017).

Data Flow Components

  • Hash Column Transformation
  • Columns To Xml Transformation
  • RegEx Extraction Transformation
  • Row Number Tranformation
  • History Lookup Transformation
  • Lookup Error Aggregation Transformation

Control Flow Components

  • Variables To Xml Task
  • Sleep Task
  • Wait For File Task
  • Wait For Signal Task
  • Wait For Time Task

To build the Components, run Visual Studio as Administrator, than it takes care about registering  the components in GAC as well as copying the dll  as well as Extensions and Upgrade Mappings xml files to appropriate directories.

If Visual Studio is not running as administrator, copying of the above files will fail and you have to manually register the components in GAC as well as you have to copy the Dll files as well as the Extensions and Upgrade Mappings to appropriate DTS directories.

Proper target directories you can find in the Posst Build events settings in individual projects.

Clone / Copy SSIS Catalog Project Configuration – sp_SSISCloneConfiguration GitHub

When you deploy a SSIS project to SSIS Catalog, you have the possibility to Configure the deployed SSIS project so you do not need to pass the parameter execution values each time you are executing the the project packages. You can configure the project to use explicitly provided value or you can use reference to Environment Variables.

This is very useful feature, however if you are using multiple environments, like DEV, TEST, QA, PROD, then you have to handle that configuration in each of the environments. To simplify the process of setting up the parameter, you can scrip the configuration.

The pitfall of scripting the configuration is, that you can do that automatically only during initial setup of the configuration. Once you confirm the configuration values in SSMS, there is no possibility to script-out any existing configuration.

sp_SSISCloneConfiguration

sp_SSISCloneConfiguration is a custom stored procedure which takes care about cloning/copying the configurations among different projects in the same SSISDB catalog as well as it provides functionality to generate reusable scripts to easily re-apply the configurations even among different SSIS Servers.

The stored procedure also takes care about sensitive values re-encryption when those are being cloned as SSISS is using different encryption keys for each project in the SSIS catalog, so simple copy of the encrypted values does not work among different projects.

The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys to allow scripting. The actual re-application of the configuration script is running in the context of the caller as the Internal SSIS catalog stored procedures for configuring projects needs to be executed in the context of windows credentials.

The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.

Be careful, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.

The complete source code for the sp_SSISCloneConfiguration is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneConfiguration
    @sourceFolder             = 'Source Folder Name'
   ,@sourceProject            = 'Source Project Name'
   ,@sourceObject             = 'Optional Source Object Name'
   ,@destinationFolder        = 'Optional Destination Folder Name'
   ,@destinationProject       = 'Optional Destination Project Name'
   ,@printScript              = 1 --Specifies whether only script should be printed
   ,@decryptSensitiveInScript = 1 --Specifies whether sensitive values should be decrypted in script

When the procedure copies the configuration, it applies the source configurations to target. If on the target are any other configuration, those are not touched by the cloning script. In order to have exact configuration, the configurations on target project should be reset.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and project. By simple modification of those variables the script will apply the configuration on different projects in different folders.

Parameters

The procedure has several parameters where some are required and some optional

The core parameters are @sourceFolder, @sourceProject . The @sourceObject@destinationFolder, @destinationProject, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source project is located

@sourceProject

Specifies the name of the source project from within source folder to be scripted

@sourceObject

Optional name of the Source Object. It specifies name of the object withing the project which configuration should be scripted. It can point to project name or to individual package names. When provided, then only configurations related to that object are cloned or scripted.

@destinationFolder

Name of the folder of destination project to which the configuration should be cloned. This parameter is optional and if not provided than the source folder name is being used.

@destinationProject

Name of the destination project to which the configuration should be coned. This parameter is optional and if not provided, then the name of the source project is used. If the destination is not provided or matches the source project (both folders and projects are the same), then the @printScript parameter is enforced to 1 and script is always generated.

@printScript

Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.

Default value = 0, this means that the script executes within the SSISDB catalog and automatically clones the configuration unless it is enforced when source matches destination or destination is not provided.

@decryptSensitiveInScript

Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, however they are marked sensitive and will be encrypted after applying the script.

If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values prior execution of the script.

Sample Output

Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1

RAISERROR(N'sp_SSISCloneConfiguration v0.10 (2016-12-18) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'=====================================================================', 0, 0) WITH NOWAIT;

DECLARE @destinationFolder nvarchar(128) = N'Test' --Specify Destination Folder Name
DECLARE @destinationProject nvarchar(128) = N'DataLoading' --Specify Destination Project Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
RAISERROR(N'Destination folder [%s] does not exists.', 15, 0, @destinationFolder) WITH NOWAIT;
RETURN;
END
--Checking for destination project existence
IF NOT EXISTS(SELECT 1
FROM [SSISDB].[catalog].[projects] p
INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = p.folder_id
WHERE f.name = @destinationFolder AND p.name = @destinationProject)
BEGIN
RAISERROR(N'Destination project [%s]\[%s] does not exists.', 15, 1, @destinationFolder, @destinationProject) WITH NOWAIT;
RETURN;
END

--Project parameters configuration
DECLARE @var sql_variant

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[SourceDB_ConnectionString]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'SourceDB_ConnectionString', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'R', @parameter_value = N'SourceDB_ConnectionString'

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[Interval]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(int, N'120');
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'Interval', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoading]\[SecurePassword]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'Pa$$w0rd'); --SENSITIVE
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=20, @parameter_name = N'SecurePassword', @object_name = @destinationProject, @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var

RAISERROR(N'Creating Configuration [SSISDB]\[%s]\[%s]\[DataLoad.dtsx]\[SourceTableName]', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'Person');
EXEC [SSISDB].[catalog].[set_object_parameter_value] @object_type=30, @parameter_name = N'SourceTableName', @object_name = N'DataLoad.dtsx', @folder_name = @destinationFolder, @project_name = @destinationProject, @value_type = 'V', @parameter_value = @var
RAISERROR(N'-----------------------------------------------------------------------------', 0, 0) WITH NOWAIT;
RAISERROR(N'There are configurations using Environment varaibles references.', 0, 0) WITH NOWAIT;
RAISERROR(N'DON''T FORGET TO SET ENVIRONMENT REFERENCES for project [%s]\[%s].', 0, 0, @destinationFolder, @destinationProject) WITH NOWAIT
RAISERROR(N'-----------------------------------------------------------------------------', 0, 0) WITH NOWAIT;

Clone / Copy SSIS Server Variables Among Environments – sp_SSISCloneEnvironment on GitHub

You have SSIS projects deployed in the SSISDB catalog. You are using environments and environment variables for passing parameter values to SSIS Executables. This is a very common scenario.

You come to a situation that you need transfer the SSIS projects, environments and varaibles among different SSIS environments (DEV/TEST/QA/PROD). You already have bunch of environment variables defined in one of your environment and you need to transfer those to other environment.

Unfortunately there is no easy way to clone, copy or script the varaibles to transfer them to different environment. If you do not script them during creation in the SSMS, there is no option in SSMS to script them out. If you do not want to type all the varaibles manually again, you have to script those in some way.

Anyway the proper scripting or clonning is not completely trivial in case sensitive varaible values are being used as SSISDB uses multiple symmetric keys and certificates for the encryption and each Environment in the SSISDB has its own key and corresponding certificate. When cloning such sensitive value, the value needs to be decrypted with the source environment symmetric key and re-encrypted by the destination environment symmetric key.

sp_SSISCloneEnvironment

sp_SSISCloneEnvironment is a custom stored procedure which takes care about cloning the SSSI server variables among different environments in the same SSISDB catalog as well as it allows generate a script, which can be re-applied on even oon other SSIS Instance.

The stored procedure takes care about sensitive values re-encryption by proper certificate when clonning. As mentioned above, SSIS generates a separate symmetric key and certificate for each environment. To ensure proper future functionality, the stored procedure takes care about decryption of the sensitive variables when reading from current environment and re-encryption by proper destination environment symmetric key.

The stored procedure executes in the context of the AllSchemaOwner database user in the SSISDB, which ensure access to all areas of SSISDB including the needed certificates and symmetric keys. Only in areas where it needs to create a folder or environment it switches the context to the caller of the stored procedure as the procedures which are taking care abut the folder and environment creation must be executed under windows credentials.

The create scripts grants EXECUTE permission to the sssis_admin database role in the SSISDB catalog so the SSSIS admins can utilize that stored procedure.

Be carefull, anyone with EXECUTE permission on the stored procedure is capable of seeing decrypted sensitive values when printing the script. The script by default grants that permission to ssis_admin database role. If you want to keep that functionality only to sysadmins, remove the GRANT at the end of the script.

The complete source code for the sp_SSISCloneEnvironment is available in my SQL-Scripts project on GitHub in the SSISDB folder.

The syntax of the stored procedure is as follow:

sp_SSISCloneEnvironment
   @sourceFolder                = 'Source Folder Name'
  ,@sourceEnvironment           = 'Source Environment Name'
  ,@destinationFolder           = 'Destination Folder Name'
  ,@destinationEnvironment      = 'Destination Environment Name'
  ,@autoCreate                  = 1 --Auto Create Destination
  ,@printScript                 = 1 --Specifies that Create Script should be output instead of actual execution
  ,@decryptSensitiveInScript    = 1 --Specifies that the sensitive values should be decrypted in the create script

Currently when cloning the environment variables the destination environment must be empty if script is not being generated and the procedure is running in the non-interactive mode.

The generated script is also easily reusable as there are two variables in the beginning for specification of the destination folder and environment. By simple modification of those variables the script will generate the variables different environments.

Parameters

The procedure has several parameters where some are required and some optional

The core parameters are @sourceFolder, @sourceEnvironment and @destinationFolder. The @destinationEnvornment, @autoCreate, @printScript and @decryptSensistiveInScript are optional.

@source Folder

Specifies the source folder where the source environment is located

@sourceEnvironment

Specifies the name of the source environment from within source folder to be scripted

@destinationFolder

Name of the destination folder to wich the environment varaibles should be clonned.

@destinationEnvironment

Name of the destination environment to which the varaibles should be conned. This parameter is optional and if not provided, then the name of the source environment is used.

@autoCreate

Specifies whether the destination foolde and destination environment should be automatically crated if they do not exist. If the script is being generated then a statement which checks existence of the folder and environment are included in the script and missing folder and/or environment is automatically created if needed by the script.

Default value is 1 = true

@printScript

Specifies whether a script should be printed into the output window. If yes, then you simply copy the script from the output window and you can apply it immediatelly on other SSIS Instance.

Default value = 0, this means that the script executes within the SSISDB catalog and automatically clnes the variables

@decryptSensitiveInScript

Specifeis whether sensitive values should be decrypted in the script.If yes, then the values are part of the script in plain-text form, howver they are marked sensitive and will be encrypted after applying the script.

If the parameter is 0 (Default), then the sensitive values are not scripted out and instead NULL value is being scripted out. User must then fill those missing values priorexecution of the script.

Sample Output

Below is sample script generated by the stored procedure when @printScript=1 and @decryptSensitiveInScript=1

RAISERROR(N'sp_SSISCloneEnvironment v0.20 (2016-10-11) (C) 2016 Pavel Pawlowski', 0, 0) WITH NOWAIT;
RAISERROR(N'===================================================================', 0, 0) WITH NOWAIT;

DECLARE @destinationFolder nvarchar(128) = N'TEST' --Specify Destination Folder Name
DECLARE @destinationEnvironment nvarchar(128) = N'ClonedEnvironment' --Specify Destination Environment Name

--Checking for destination folder existence
IF NOT EXISTS(SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE [name] = @destinationFolder)
BEGIN
    RAISERROR(N'Creating missing Folder [%s]', 0, 0, @destinationFolder) WITH NOWAIT;
    EXEC [SSISDB].[catalog].[create_folder] @folder_name = @destinationFolder
END

--Checking for destination environment existence
IF NOT EXISTS(
    SELECT 1
    FROM [SSISDB].[catalog].[environments] e
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE f.[name] = @destinationFolder AND e.[name] = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Creating missing Environment [%s]\[%s]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT
    EXEC [SSISDB].[catalog].[create_environment] @folder_name = @destinationFolder, @environment_name = @destinationEnvironment, @environment_description = N''
END

--Checking for variables existence in destination eivnironment
IF EXISTS (
    SELECT 1
    FROM [SSISDB].[catalog].[environment_variables] ev
    INNER JOIN [SSISDB].[catalog].[environments] e ON e.environment_id = ev.environment_id
    INNER JOIN [SSISDB].[catalog].[folders] f ON f.folder_id = e.folder_id
    WHERE
        f.name = @destinationFolder AND e.name = @destinationEnvironment
)
BEGIN
    RAISERROR(N'Destination Environment [%s]\[%s] is not empty. Clear all variables prior clonning environment.', 15, 2, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
    RETURN;
END

--Environment variables creation
DECLARE @var sql_variant

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V1]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bit, N'1');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V1', @data_type=N'Boolean', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V10]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is string parameters');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V10', @data_type=N'String', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V11]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(sql_variant, N'This is sensitive string'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V11', @data_type=N'String', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V12]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2010-12-31T00:00:00'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V12', @data_type=N'DateTime', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V13]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'99.990000000000000000'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V13', @data_type=N'Decimal', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V14]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'56'); --SENSITIVE
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V14', @data_type=N'Int32', @sensitive=True, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V2]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(tinyint, N'5');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V2', @data_type=N'Byte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V3]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(datetime, N'2016-01-01T00:00:00');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V3', @data_type=N'DateTime', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V4]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(decimal(28, 18), N'12.390000000000000000');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V4', @data_type=N'Decimal', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V5]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'156.987');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V5', @data_type=N'Double', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V6]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(int, N'123456');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V6', @data_type=N'Int32', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V7]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(bigint, N'987654321');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V7', @data_type=N'Int64', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V8]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(smallint, N'9');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V8', @data_type=N'SByte', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

RAISERROR(N'Creating variable [SSISDB]\[%s]\[%s]\[V9]', 0, 0, @destinationFolder, @destinationEnvironment) WITH NOWAIT;
SET @var = CONVERT(float, N'56');
EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N'V9', @data_type=N'Single', @sensitive=False, @folder_name=@destinationFolder, @environment_name=@destinationEnvironment, @value=@var, @description=N''

Parallel execution of child SQL Server Integration Services Packages using For Loop Containers

On mssqltips.com you can find my latest post Parallel execution of child SQL Server Integration Services Packages using For Loop Containers.

The post describes a very simple way how to parallelize execution of child packages in SSIS. There exists third party components for parallel execution, but sometimes they are costly and some time they do not work exactly as expected. The for loop containers allow us to implement it using purely using the building components and at no costs with exactly defined behavior.

Reading SharePoint Surveys and Lists using a SQL Server Integration Services Package – Updated

On www.mssqltips.com (Reading SharePoint Surveys and Lists using a SQL Server Integration Services SSIS Package) you can find an updated version of my original post Reading SharePoint List and Survey data using Integration Services package.

The updated version on mssqltips describes an easier way of reading the Surveys and Lists data with simplified use of URL protocol an more elegant way of final data extraction using the T-SQL XML methods.

Post also contains complete demo solution.

Manipulating SSIS packages using PowerShell

Probably you have come to situation when you have a SSIS project with higher count of packages and you need to add for example a parameter or variable to all or most of the packages.  Normally you have to open each package and add variable/parameter to all packages one by one and specify all the properties in each package.

Using the PowerShell will save you a lot of manual work and tons of clicks inside the SQL Server Data Tools (SSDT) or  Business Intelligence Development Studio (BIDS) as PowerShell will allow you to automate the manual work. It’s very easy to manipulate the packages by PowerShell as SSIS packages are pure XML Files and PowerShell has a good XML support.

For writing, testing and running PowerShell script I suggest you download and install PowerGUI which has great IntelliSense support and is probably best PowerShell editor available. Also I suggest you to install the Run AddOn which allows you to execute only selected part of script.

Also before you start executing the scripts, ensure that you have properly set ExecutionPolicy to allow script execution. You have to run it with elevated Administrator privileges.

Enable Local not signed Script Execution
Set-ExecutionPolicy RemoteSigned

Example how to manipulate SSIS using PowerShell

In this example we will manipulate SSIS 2012 packages and we will add a new parameter to the packages.

First thing you have to do is to retrieve list of SSIS package to be processed. This can be easily done by the Get-ChildItem cmdlet.

$files = Get-ChildItem "C:MySSISProjects*.dtsx"

Once we have the list of file to be processed we can start processing file by file using the foreach structure. In this example we will add a SSISTaskID package parameter.

Get List of all relevat SSIS Packages
$files = Get-ChildItem "C:MyProjectsMySSISProject*.dtsx"

#process file by file
foreach($file in $files)
{
    #get the content of SSIS package as XML
    $dts = [xml](Get-Content $file.FullName)

    #create XmlNamespaceManager
    $mng = [System.Xml.XmlNamespaceManager]($dts.NameTable)
    #add a DTS namespace to the XmlNamespaceManager
    $mng.AddNamespace("DTS", "www.microsoft.com/SqlServer/Dts")

    #use XPath query to get DTS:PackageParameters node
    $params = $dts.SelectSingleNode("/DTS:Executable/DTS:PackageParameters[1]", $mng)

    #use XPath query to get eventual existing SSISTaskID parameter
    $param = $dts.SelectSingleNode("/DTS:Executable/DTS:PackageParameters/DTS:PackageParameter[@DTS:ObjectName='SSISTaskID'][1]", $mng)

    if ($param -eq $null) #parameter does not exists
    {
        #Create New Package parameter with prefix DTS and correct namespace uri
        $param = $dts.CreateElement("DTS", "PackageParameter", "www.microsoft.com/SqlServer/Dts")

        $attr = $dts.CreateAttribute("DTS", "CreationName", "www.microsoft.com/SqlServer/Dts")
        $param.Attributes.Append($attr)

        #Parameter Data Type
        $attr = $dts.CreateAttribute("DTS", "DataType", "www.microsoft.com/SqlServer/Dts")
        $attr.Value = "3" #DataType = 3 is equal to integer
        $param.Attributes.Append($attr)

        ##Description
        $attr = $dts.CreateAttribute("DTS", "Description", "www.microsoft.com/SqlServer/Dts")
        $attr.Value = "This is a TaskID"
        $param.Attributes.Append($attr)

        #DTSID - unique GUID for created parameter
        $attr = $dts.CreateAttribute("DTS", "DTSID", "www.microsoft.com/SqlServer/Dts")
        $attr.Value = "{" + [System.Guid]::NewGuid().toString().ToUpper() + "}" #Each parameter has unique GUID in SSIS package, so let's generate one
        $param.Attributes.Append($attr)

        #parameter name
        $attr = $dts.CreateAttribute("DTS", "ObjectName", "www.microsoft.com/SqlServer/Dts")
        $attr.Value = "SSISTaskID"
        $param.Attributes.Append($attr)

        #Parameter Value property - setting the parameter value
        $paramValue = $dts.CreateElement("DTS", "Property", "www.microsoft.com/SqlServer/Dts");

        #setting attributes of Parameter Value Property
        $attr = $dts.CreateAttribute("DTS", "DataType", "www.microsoft.com/SqlServer/Dts")
        $attr.Value = "3"
        $paramValue.Attributes.Append($attr)

        $attr = $dts.CreateAttribute("DTS", "Name", "www.microsoft.com/SqlServer/Dts")
        $attr.Value = "ParameterValue"
        $paramValue.Attributes.Append($attr)

        #Set the value of the ParameterValue property
        $paramValue.InnerText = "0"

        #add the property to the Parameter
        $param.AppendChild($paramValue)     

        #Add the Parameter to the Params collection
        $params.AppendChild($param)
    }       

    ################################################################################################
    #  Once the Parameter is created it is time to write the package back to disk
    ################################################################################################

    #Create XmlWriterSettings as we want to format the SSIS package properly
    [System.Xml.XmlWriterSettings] $settings = New-Object -TypeName System.Xml.XmlWriterSettings

    $settings.Indent = $true #Indent child nodes
    $settings.NewLineOnAttributes = $true #put each attribude on separate line

    #Create XmlWriter with appropriate settings and path to overwrite the original package
    [System.Xml.XmlWriter]$writer = [System.Xml.XmlWriter]::Create($file.FullName, $settings)

    #Save the package using the XmlWriter
    $dts.Save($writer)

    #Close the writer
    $writer.Close()
}
In the script we are using XPath query to get relevant Parameter Nodes. This can be easily used for locating whatever node in the XML source is necessary. For more details related to XPath you can take a look  XPath Reference on MSDN. I our example we take first DTS:PackageParameters node under the DTS:Executable node. For correct querying we have to use XmlNameSpaceManager as all the nodes in SSIS Package are prefixed with DTS name space. For checking whether the parameter we would like to add exists or not we again use XPath with appropriate filters to find it by Name. Then if the parameter doesn't exists, we create a new one. What attributes and child nodes are necessary for such parameter you find out easy. Create the parameter using BIDS or SSDT in one of the SSIS packages, then take a look into the source code of the package and you will be able to find all the information necessary. For example in our case the parameter node in the source XML looks as below:
<DTS:PackageParameter
    DTS:CreationName=""
    DTS:DataType="3"
    DTS:Description="This is a TaskID"
    DTS:DTSID="{3252C760-114A-4E8F-86EB-D3E6BAACA0E4}"
    DTS:ObjectName="SSISTaskID">
    <DTS:Property
    DTS:DataType="3"
    DTS:Name="ParameterValue">0</DTS:Property>
</DTS:PackageParameter>

So we had to create a PackageParameterNode with CreationName, DataType, Description, DTSID and ObjectName attributes and also with child node Property, which represents default value of the parameter.

As each parameter, variable or whatever part in SSIS has it's own unique DTSID property, which is GUID, we have used the [System.Guid]::NewGuid() method to generate unique DTSID for our parameter.

Finally once the changes into the SSIS package are applied, it is necessary to write it back to the file system. By default the source of the SSIS package is indented and and each attribute is written on separate line. SSIS packages are written in that way to be more human readable and allow an easy processing and comparison by versioning systems.

To achieve the same formatting we have to create and use instance of the XmlWriter class with appropriate XmlWriterSettings. As you can see from the script we set Indent and NewLineOnAttributes properties to true.

Once the writer is constructed, we can write the package back to the file system. It is also important to close the writer once the saving is done otherwise you will keep open handles to the processed files.

In the example we are also overwriting the original package as we specify the $file.FullName as path for the XmlWriter class. If we want, we can provide whatever name for the package and save it under different name. In case we would like to add a "_modified" suffix to the file name, we can use:

System.IO.Path]::Combine($file.DirectoryName, $file.BaseName + "_modified") + $file.Extension

Conclusion

As we can see from the example, the SSIS manipulation using PowerShell is quite easy and after a small training with writing this modification also very quick and will save you a lot of manual work if you have to modify a higher count of packages. You can alter the above script slightly to do nearly whatever modification is necessary.

This example was done on SSIS 2012 package source, but can be relatively easy applied also on SSIS 2005 and SSI 2005 although the XML source of those packages are not so easy readable as the source of SSIS 2012.