Package Configurations with BIML

Case
I want to use SSIS Package configurations in my BIML script. How do I do that?

Solution
Here are a couple of examples of the most used package configurations. Screens are from SSIS 2012 package deployment, but it works the same in SSIS 2008.

Environment Variable Config
I have one Connection Manager named Meta and I added package configuration to get its connectionstring from a Windows Environment Variable. That variable already exists and contains a connectionstring. The screens are what the BIML script below will produce.

Environment Variable Config

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 
 <Connections>
  <!-- My Connection Manager to the Meta database containing a config table and other tables-->
  <OleDbConnection
   Name="Meta"
   ConnectionString="Data Source=.;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>
 
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- Environment Variable Configuration -->
    <!-- The Environment Variable should already contain a value -->

    <!-- The name of the configuration shown in the Package Configurations Organizer window -->
    <PackageConfiguration Name="SSISMeta">
     <!-- The name of the environment variable -->
     <EnvironmentVariableInput EnvironmentVariable="SSISMeta" />
     <ConfigurationValues>
      <!-- PropertyPath contains the name of the connection manager -->
      <!-- You can leave the value property empty -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectrionStringMeta"
       PropertyPath="\Package.Connections[Meta].Properties[ConnectionString]"
       Value="" />
     </ConfigurationValues>
    </PackageConfiguration>

   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Task with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy"
     ConnectionName="Meta"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
    
   </Tasks>
  </Package>
 </Packages>
</Biml>

Download

SQL Server Configuration
I have a second Connection Manager named Source and I added package configuration to get its value from a SQL Server configuration table. This configuration table is stored in the Meta database. Note: the configurations should already exist in that table

SQL Server Config

<Biml xmlns="http://schemas.varigence.com/biml.xsd">

 <Connections>
  <!-- My Connection Manager to the Meta database containing a config table and other tables-->
  <OleDbConnection
   Name="Meta"
   ConnectionString="Data Source=.;Initial Catalog=Meta;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
  <!-- My Connection Manager to a source database -->
  <OleDbConnection
   Name="Source"
   ConnectionString="Data Source=.;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>

 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- Environment Variable Configuration -->
    <!-- The Environment Variable should already contain a value -->

    <!-- The name of the configuration shown in the Package Configurations Organizer window -->
    <PackageConfiguration Name="SSISMeta">
     <!-- The name of the environment variable -->
     <EnvironmentVariableInput EnvironmentVariable="SSISMeta" />
     <ConfigurationValues>
      <!-- PropertyPath contains the name of the connection manager -->
      <!-- You can leave the value property empty -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectrionStringMeta"
       PropertyPath="\Package.Connections[Meta].Properties[ConnectionString]"
       Value="" />
     </ConfigurationValues>
    </PackageConfiguration>

    <!-- SQL Server Configuration -->
    <!-- The configuration table should already contain values -->

    <!-- ConnectionName is the name of the connection manager containing the configuration table -->
    <!-- Name is for both the Configuration Filter in the database table and the name in the Package Configurations Organizer window -->
    <PackageConfiguration
     ConnectionName="Meta"
     Name="SourceConfiguration">
     <!-- Table contains the name of the configuration table -->
     <ExternalTableInput Table="[dbo].[SSIS Configurations]" />
    </PackageConfiguration>
    
   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Tasks with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy 1"
     ConnectionName="Meta"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
    <ExecuteSQL
     Name="SQL - Dummy 2"
     ConnectionName="Source"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
   </Tasks>
  </Package>
 </Packages>
</Biml>

Download
The combination of these two configuration types is often used in a DTAP street.

Parent Package Variable Configuration
I have a variable that is filled by a variable from the parent package. This is done with Parent Package Variable Configuration. In BIML script you will find this in the variable tag and not in the configurations tag!

Parent Package Variable Config

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <!-- Parent Package Variable Configuration -->
   <!-- Note: this is not in the Configurations tag, but in the variable tag -->

   <!-- InheritFromPackageParentConfigurationString is for both the name of the parent package variable -->
   <!-- and the name in the Package Configurations Organizer window-->
   <Variables>
    <Variable
     DataType="String"
     Name="MyChildPackageVariable"
     InheritFromPackageParentConfigurationString="MyParentPackageVariable"
     Namespace="User">SSISJoost</Variable>
   </Variables>

  </Package>
 </Packages>
</Biml>

Download

XML Configuration File
I have a Connection Manager and I have an XML configuration file to configure its connectionstring. The xml/dtsConfig file already exists with the correct values otherwise the package won't work.

XML Configuration File

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Connections>
  <OleDbConnection
   Name="Destination"
   ConnectionString="Data Source=.;Initial Catalog=Staging;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
  </OleDbConnection>
 </Connections>
 <Packages>
  <Package Name="Child01" ConstraintMode="Linear">

   <PackageConfigurations>
    <!-- XML Configuration File -->

    <!-- The name is for the name in the Package Configurations Organizer window-->
    <PackageConfiguration Name="Destination Configuration">
     <!-- ExternalFilePath is the path of the config file  -->
     <ExternalFileInput
      ExternalFilePath="D:\DestinationConfigurations.dtsConfig">
     </ExternalFileInput>
     <ConfigurationValues>
      <!-- You can leave the value property empty -->
      <!-- The value of the PropertyPath should also be in the DtsConfig file -->
      <ConfigurationValue
       DataType="String"
       Name="ConnectionStringDestination"
       PropertyPath="\Package.Connections[Destination].Properties[ConnectionString]"
       Value=""
       >
      </ConfigurationValue>
     </ConfigurationValues>
    </PackageConfiguration>

   </PackageConfigurations>

   <Tasks>
    <!-- Dummy Tasks with connection to make sure the connection manager is added to the package -->
    <ExecuteSQL
     Name="SQL - Dummy"
     ConnectionName="Destination"
     ResultSet="None">
     <DirectInput>
      SELECT @@VERSION AS 'SQL Server Version'
     </DirectInput>
    </ExecuteSQL>
   </Tasks>
  </Package>
 </Packages>
</Biml>

Download
This XML configuration type can also be used in a DTAP street.

Deploying SSIS 2012 Environments

Case
Parameters and environments are a handy new feature in SSIS 2012, but creating environments with variables and referencing the environment to the project and the variables to the parameters is a lot of effort. No problem if you have to do it once, but if you deploy your packages through the DTAP servers you have to redo it on every single server. Is there a way to deploy environments?


No export option for Environments

Solution
Although it is easy to deploy packages through DTAP servers. Deploying environments is not  possible. That could get a little tiresome especially if you have a lot of parameters in your project.

I have created a script/stored procedure that uses the project identifier as input, loops through the tables of the SSISDB and prints SQL-statements to execute SSISDB Stored Procedures.

How to get the project identifier

For every environment, variable and reference to this project it will generate creation scripts. You can copy these stored procedure calls, adjust the values where needed and execute them on the next environment.

Execute script then copy and execute output

Release notes

1. There are no checks or validations in this version. So it doesn't check whether objects already exist before calling the SSISDB Stored Procedures.
2. First deploy the SSIS project before executing the stored procedure calls on the next server.
3. Make sure the folder name is equal on the new server or change it in the stored procedure calls.
4. Make sure to check sensitive variables values (you can't get the value from the tables).

Here is how you call the stored procedure to generate the scripts. If you don't want to add the new stored procedure then you could just use the script inside the stored procedure.

exec catalog.deploy_environment 11

Add the following stored procedure to the SSISDB or use the TSQL code inside.

USE SSISDB;
GO

-- USE AT OWN RISK! This stored procedure was created on the SSISDB on SQL Server version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--      Dec 28 2012 20:23:12 
--      Copyright (c) Microsoft Corporation
--      Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)


-- Drop any previous versions of this stored procedure
IF OBJECT_ID ( 'catalog.deploy_environment', 'P' ) IS NOT NULL 
    DROP PROCEDURE catalog.deploy_environment;
GO

-- project_id is the identifier in the properties of a project
CREATE PROCEDURE catalog.deploy_environment 
    @project_id bigint 
AS 

 -- Internal variables used within the cursor
 Declare @environment_name as nvarchar(128);
 Declare @project_name as nvarchar(128);
 Declare @folder_name as nvarchar(128);
 Declare @environment_folder_name as nvarchar(128);
 Declare @reference_type as char(1);
 Declare @folder_id as bigint;
 Declare @environment_description as nvarchar(1024);
 Declare @environment_id as bigint;


 DECLARE ref_environment_cursor CURSOR FOR 
  -- Loop through all in the project referenced Environments
  SELECT  r.environment_name
  ,   p.name as project_name
  ,   ISNULL(r.environment_folder_name, f.name) as folder_name
  ,   ISNULL(r.environment_folder_name, f.name) as environment_folder_name -- for @reference_type = A
  ,   r.reference_type as reference_type
  ,   f.folder_id
  ,   e.description as environment_description
  ,   e.environment_id
  FROM  [SSISDB].[internal].environment_references as r
  INNER JOIN [SSISDB].[internal].projects as p
     on r.project_id = p.project_id
  INNER JOIN [SSISDB].[internal].folders as f
     on p.folder_id = f.folder_id
  INNER JOIN [SSISDB].[internal].environments as e
     on e.folder_id = f.folder_id
     and e.environment_name = r.environment_name
  WHERE  r.project_id = @project_id

 OPEN ref_environment_cursor

 FETCH NEXT FROM ref_environment_cursor 
 INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;

 Print '-- Create scripts for deploying enviroments'
 Print '-- Project ID: ' + CAST(@project_id as varchar(5)) + ' - Project name: ' + @project_name
 Print ''

 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- Create environment
  Print '-- Create environment: ' + @environment_name
  Print 'EXEC  [SSISDB].[catalog].[create_environment]'
  Print '   @environment_name=N''' + @environment_name + ''''
  Print ',   @environment_description=N''' + @environment_description + ''''
  Print ',   @folder_name=N''' + @folder_name + ''''
  Print 'GO'
  Print ''

  -- Create reference from environment to project. Relative or Absolute
  Print '-- Reference environment ' + @environment_name + ' to project ' + @project_name
  IF @reference_type = 'R'
  BEGIN
   -- Reference Relative
   Print 'Declare @reference_id bigint'
   Print 'EXEC  [SSISDB].[catalog].[create_environment_reference]'
   Print '   @environment_name=N''' + @environment_name + ''''
   Print ',   @reference_id=@reference_id OUTPUT'
   Print ',   @project_name=N''' + @project_name + ''''
   Print ',   @folder_name=N''' + @folder_name + ''''
   Print ',   @reference_type=R'
   Print 'GO'
   Print ''
  END
  ELSE
  BEGIN
   -- Reference Absolute
   Print 'Declare @reference_id bigint'
   Print 'EXEC  [SSISDB].[catalog].[create_environment_reference]'
   Print '    @environment_name=N''' + @environment_name + ''''
   Print ',   @environment_folder_name=N''' + @environment_folder_name + ''''
   Print ',   @reference_id=@reference_id OUTPUT'
   Print ',   @project_name=N''' + @project_name + ''''
   Print ',   @folder_name=N''' + @folder_name + ''''
   Print ',   @reference_type=A'
   Print 'GO'
   Print ''
  END

  
  -- Internal variables used within the cursor 
  Declare @environment_value as sql_variant--nvarchar(max); -- SQL_VARIANT
  Declare @variable_name as nvarchar(128);
  Declare @sensitive as bit;
  Declare @variable_description as nvarchar(1024);
  Declare @variable_type as nvarchar(128);

  DECLARE environment_var_cursor CURSOR FOR 
   -- Loop through all in the variables of the active environment
   SELECT  CAST(ev.value as varchar(255)) as environment_value
   ,   ev.name as variable_name
   ,   ev.sensitive
   ,   ev.description as variable_description
   ,   ev.type as variable_type
   FROM  [SSISDB].[catalog].environment_variables as ev
   WHERE  environment_id = @environment_id

  OPEN environment_var_cursor

  FETCH NEXT FROM environment_var_cursor 
  INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;

  WHILE @@FETCH_STATUS = 0
  BEGIN
   -- Environments variables
   Print '-- Create variables for environment: ' + @environment_name + ' - ' + @variable_name

   -- Variable declaration depending on the type within the environment
   IF @variable_type = 'Boolean'
   BEGIN
    Print 'DECLARE @var bit = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Byte'
   BEGIN
    Print 'DECLARE @var tinyint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'DateTime'
   BEGIN
    Print 'DECLARE @var datetime = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Decimal'
   BEGIN
    Print 'DECLARE @var decimal(38,18) = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Double'
   BEGIN
    Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Int16'
   BEGIN
    Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Int32'
   BEGIN
    Print 'DECLARE @var int = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Int64'
   BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'SByte'
   BEGIN
    Print 'DECLARE @var smallint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'Single'
   BEGIN
    Print 'DECLARE @var float = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'String'
   BEGIN
    Print 'DECLARE @var sql_variant = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'UInt32'
   BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END
   ELSE IF @variable_type = 'UInt64'
   BEGIN
    Print 'DECLARE @var bigint = N''' + ISNULL(CAST(@environment_value as nvarchar(max)),'*S*E*N*S*I*T*I*V*E*') + ''''
   END

   Print 'EXEC  [SSISDB].[catalog].[create_environment_variable]'
   Print '   @variable_name=N''' + @variable_name + ''''
   IF @sensitive = 0
   BEGIN
    Print ',   @sensitive=False'
   END
   ELSE
   BEGIN
    Print ',   @sensitive=True'
   END
    Print ',   @description=N''' + @variable_description +  ''''
   Print ',   @environment_name=N''' + @environment_name + ''''
   Print ',   @folder_name=N''' + @folder_name + ''''
   Print ',   @value=@var'
   Print ',   @data_type=N''' + @variable_type + ''''
   Print 'GO'
   Print ''

   FETCH NEXT FROM environment_var_cursor 
   INTO @environment_value, @variable_name, @sensitive, @variable_description, @variable_type;
  END
  CLOSE environment_var_cursor;
  DEALLOCATE environment_var_cursor;
  -- End Environments variables

  -- Parameter - Variable mapping
  Declare @object_type as smallint
  Declare @parameter_name as nvarchar(128);
  Declare @object_name as nvarchar(260);
  Declare @folder_name2 as nvarchar(128);
  Declare @project_name2 as nvarchar(128);
  Declare @value_type as char(1)
  Declare @parameter_value as nvarchar(128);

  DECLARE parameter_var_cursor CURSOR FOR 
   -- Loop through variables referenced to a parameter
   SELECT  op.object_type
   ,   parameter_name
   ,   [object_name]
   ,   f.name as folder_name
   ,   p.name as project_name
   ,   value_type
   ,   referenced_variable_name as parameter_value
   FROM  [SSISDB].[internal].object_parameters as op
   INNER JOIN [SSISDB].[internal].projects as p
      on p.project_id = op.project_id
   INNER JOIN [SSISDB].[internal].folders as f
      on p.folder_id = f.folder_id
   WHERE  op.project_id = @project_id
   AND   referenced_variable_name is not null

  OPEN parameter_var_cursor

  FETCH NEXT FROM parameter_var_cursor 
  INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;

  WHILE @@FETCH_STATUS = 0
  BEGIN
   -- Reference variables
   Print '-- Reference variable ' + @parameter_value + ' to parameter ' + @parameter_name
   Print 'EXEC  [SSISDB].[catalog].[set_object_parameter_value]' 
   Print '   @object_type=' + CAST(@object_type as varchar(5))
   Print ',   @parameter_name=N''' + @parameter_name + ''''
   Print ',   @object_name=N''' + @object_name + ''''
   Print ',   @folder_name=N''' + @folder_name2 + '''' ----
   Print ',   @project_name=N''' + @project_name2 +  '''' ---
   Print ',   @value_type=' + @value_type
   Print ',   @parameter_value=N''' + @parameter_value + ''''
   Print 'GO'
   Print ''

   FETCH NEXT FROM parameter_var_cursor 
   INTO @object_type, @parameter_name, @object_name, @folder_name2, @project_name2, @value_type, @parameter_value;
  END
  CLOSE parameter_var_cursor;
  DEALLOCATE parameter_var_cursor;
  -- End Parameter - Variable mapping

  FETCH NEXT FROM ref_environment_cursor 
  INTO @environment_name, @project_name, @folder_name, @environment_folder_name, @reference_type, @folder_id, @environment_description, @environment_id;
 END
 CLOSE ref_environment_cursor;
 DEALLOCATE ref_environment_cursor;
GO

Download as SQL file

NOTE: Please use at own risk and let me know it things could be improved!

Create and Map Server Environments

Case
How do you use configurations in the new Project Deployment Model of SSIS 2012? The package configuration menu item is not available in the SSIS menu.

Solution
The package configurations are hidden because configuring a package is a little different in the 2012 Project Deployment Model. But if you open a package and click on the properties of the package, then you will find a Configurations property under miscellanies (see image below). For this example we are not using the old package configurations, but we are using parameters and server environments. I will create two configurations (=environments). One for running with a small dataset and one for a large dataset. Same can be done for a Development Environment and a Test Environment on the same machine.

Old package configurations

1) Starting point
For this example I created a very basic staging package with a Flat File source and an OLE DB destination. I have created a package parameter and used it to parameterize the connectionstring of the Flat File Connection Manager. (Other items in this example are ignored for configuration)

Basic package with a parameterized Flat File Connectionmanager

2) Deploy to SSISDB catalog
I have deployed my project (and its package) to the SSISDB by right clicking the project in the Solution Explorer and selecting Deploy.

Deploying the project

The result in Management Studio:

The project in SSISDB catalog

3) Create environment
We need to create an environment. You can compare an environment to a configuration file. First I will create an environment for the small dataset. Right click Environments and choose Create Environment. Fill in a name and description.

New environment

4) Environment variables
In the environment we need to create a variable to store the path of the source file. Right click the newly created environment and select Properties. Go to the Variables pane and add a String variable for the filepath and enter a value.

Environment variables

5) Connect environment to package
We now need to reference the newly created environment to the package. Right click the package and choose Configure. Then go to the references pane and add the environment to the package. You can either choose a relative path or an absolute path.

Referencing environment to package

6) Connect variable to parameter
Next step is to reference the environment variable to the package parameter. This can be done in the Parameters pane of the same configuration screen.

Referencing variable to parameter

7) Second environment
Repeat steps 3 to 6 for a second environment called Large dataset. Or use my Copy Environments stored procedure.

8) Select environment and execute package
Now you can choose between your two environments when executing a package. For this you need to check the Environment checkbox at the bottom of the Execute Package windows. You will see both environments in the list.

Choose environment and execute package

Multiple Configurations in SSDT

Case
In the SSIS Catalog we have the ability to configure for several environments by creating multiple environments (one for development, one for test), but how do we easily switch environments in SSDT while developing?

Multiple environments

Solution
In SQL Server Data Tools (SSDT) we have the Configuration Manager that can do something similar.


1) Start
Lets start with a basic package which loads a flat file into a database table. The two connection managers should be changed if we want to load data from the Test environment instead of data from the Development environment.

My basic package

2) Package Parameter
We have to create a Package Parameter for the Flat File Connection Manager. (This Connection Manager is for this package only.) Right click the 'Clients' Connection Manager and click Parameterize. Select the ConnectionString as the property. Create a new parameter and set the scope to Package.

Parameterize Connection Manager

3) Project Parameter
Do the same for the OLE DB Connection Manager that connects to SQL Server. Because this is a Project Connection Manager, the Scope is automatically set to Project

Parameterize Connection Manager

4) Add Configuration
Next step is to add a Configuration in addition to the existing Development Configuration. In the Standard toolbar you see a drop down called Solution Configurations. Open it and select Configuration Manager. A new window will open.
In the Active solution configuration drop down click on <New...>. Now you can enter a new name and optional copy settings from the existing Development Configuration.

Adding a Configuration

5) Add Parameters to Configuration - Package Parameter
Now we have two Configurations (Developement and Test) and we can give the parameters different values for each Configuration. First the Package Parameter: Go to the Parameter tab in the package and click on 'Add Parameters to Configuration'.
Next, click on the Add button in the new window to select a Parameter to configure. In the next new window you can select that variable.

Add Parameters to Configuration

Now you have added the Parameter to the Configurations, you can give it a different value for each Configuration.

Change Parameter value for Test Configuration

6) Add Parameters to Configuration - Project Parameter
Now do the same for the Project Parameter. In the Solution Explorer (default upper right corner) you can find the Project Parameters. This will open a new document. The rest is the same as the previous step.

Add Project Parameters to Configuration

Change the parameter value for the Test Configuration.


Change Project Parameter value for Test Configuration

7) Test
Now you can run the package with different Configurations within SSDT. In this example a small file for Development and a large file for Test.

Running with different Configurations

Note 1: These configurations won't be deployed to the SSISDB.
Note 2: You can also change other project properties per Configurations, like the deployment path or the 32/64bit property.

Configure other project properties

SSIS 2012 Copy Environments

Case
I created a new environment in the Integration Services Catalogs, but I can't copy it. If I want a duplicate / clone the enviroment then I have to create it from scratch with all the variables. Where is the copy option?

Where is the copy environment option?

Solution
Well there isn't one yet (please see/vote this Microsoft Connect suggestion).  So I copied the create_environment stored procedure and altered it to a copy_environment stored procedure. You have to know the environment id from the environment you want to copy. Double click the existing environment and get the number from the Identifier property.

Get identifier of existing environment

Add the new stored procedure and execute it like:

EXEC [catalog].[copy_environment] 3, 'Test', 'Test environment';

The result: a new environment with
same variables as 'Development'

PS you still have to reference the enviroment to your project manually:
Update: I adjusted the stored procedure and now it also links the new environment to the same project as the existing environment.

Reference the enviroment to your project

The new stored procedure (use at own risk!):

USE [SSISDB]
GO

-- USE AT OWN RISK! This stored procedure is altered from create_environment that was shipped with:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) 
--      Oct 19 2012 13:38:57 
--      Copyright (c) Microsoft Corporation
--      Developer Edition (64-bit) on Windows NT 6.1 <x64> (Build 7601: Service Pack 1)

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [catalog].[copy_environment]
        --@folder_name        nvarchar(128),         -- Changed from parameter to variable
        @current_environment_id bigint,              -- Added to store the ID of the environment you want to copy
        @new_environment_name   nvarchar(128),         -- Renamed (added new_)
        @new_environment_description    nvarchar(1024)= NULL    -- Renamed (added new_)
WITH EXECUTE AS 'AllSchemaOwner'
AS
    SET NOCOUNT ON
    
    DECLARE @folder_id bigint
    DECLARE @folder_name        nvarchar(128)     -- Added (moved from parameter)
    DECLARE @new_environment_id bigint        -- Renamed (added new_)
    DECLARE @result bit
    
    
    DECLARE @caller_id     int
    DECLARE @caller_name   [internal].[adt_sname]
    DECLARE @caller_sid    [internal].[adt_sid]
    DECLARE @suser_name    [internal].[adt_sname]
    DECLARE @suser_sid     [internal].[adt_sid]
    
    EXECUTE AS CALLER
        EXEC [internal].[get_user_info]
            @caller_name OUTPUT,
            @caller_sid OUTPUT,
            @suser_name OUTPUT,
            @suser_sid OUTPUT,
            @caller_id OUTPUT;
          
          
        IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )
        BEGIN
            RAISERROR(27123, 16, 1) WITH NOWAIT
            RETURN 1
        END
    REVERT
    
    IF(
            EXISTS(SELECT [name]
                    FROM sys.server_principals
                    WHERE [sid] = @suser_sid AND [type] = 'S')  
            OR
            EXISTS(SELECT [name]
                    FROM sys.database_principals
                    WHERE ([sid] = @caller_sid AND [type] = 'S')) 
            )
    BEGIN
            RAISERROR(27123, 16, 1) WITH NOWAIT
            RETURN 1
    END
    
    --IF (@folder_name IS NULL OR @environment_name IS NULL)  -- Changed, not checking the folder_name any more. Renamed @environment_name to @new_environment_name
 IF (@new_environment_name IS NULL)
    BEGIN
        RAISERROR(27138, 16 , 6) WITH NOWAIT 
        RETURN 1 
    END
    
    IF [internal].[is_valid_name](@new_environment_name) = 0
    BEGIN
        RAISERROR(27142, 16, 1, @new_environment_name ) WITH NOWAIT
        RETURN 1
    END
    
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    
    
    
    DECLARE @tran_count INT = @@TRANCOUNT;
    DECLARE @savepoint_name NCHAR(32);
    IF @tran_count > 0
    BEGIN
        SET @savepoint_name = REPLACE(CONVERT(NCHAR(36), NEWID()), N'-', N'');
        SAVE TRANSACTION @savepoint_name;
    END
    ELSE
        BEGIN TRANSACTION;                                                                                          
    BEGIN TRY
    
        EXECUTE AS CALLER
            --SET @folder_id = (SELECT [folder_id] FROM [catalog].[folders] WHERE [name] = @folder_name)  -- Changed, getting the folder_id via the environment_id
   SET @folder_id = (SELECT [folder_id] FROM [internal].[environments] WHERE [environment_id] = @current_environment_id)
   SET @folder_name = (SELECT [name] FROM [catalog].[folders] WHERE [folder_id] = @folder_id)    -- Added, filling the folder_name which was a parameter
        REVERT
        
        IF @folder_id IS NULL
        BEGIN
            RAISERROR(27104 , 16 , 1, @folder_name) WITH NOWAIT
        END
        
        EXECUTE AS CALLER   
            SET @result =  [internal].[check_permission] 
                (
                    1,
                    @folder_id,
                    100
                 ) 
        REVERT
        
        IF @result = 0
        BEGIN
            RAISERROR(27209 , 16 , 1, @folder_name) WITH NOWAIT    
        END
         
        IF EXISTS(SELECT env.[environment_name] 
                      FROM [internal].[folders] fld INNER JOIN [internal].[environments] env
                      ON fld.[folder_id] = env.[folder_id] AND 
                      fld.[name] = @folder_name   AND 
                      env.[environment_name] = @new_environment_name)
        BEGIN
            RAISERROR(27157 , 16 , 1, @new_environment_name) WITH NOWAIT
        END
    
  -- CHANGED, old Insert query
        --INSERT INTO [internal].[environments] 
        --    VALUES (@environment_name, @folder_id, @environment_description, @caller_sid, @caller_name, SYSDATETIMEOFFSET())
        --    
        --SET @environment_id = SCOPE_IDENTITY()
       
        ------------------------------------------------------------------------------------------------------------
  -- NEW INSERT INTO QUERIES
  ------------------------------------------------------------------------------------------------------------
  -- Copy the environment
  INSERT INTO [internal].[environments]
  (
    [environment_name]
  ,  [folder_id]
  ,  [description]
  ,  [created_by_sid]
  ,  [created_by_name]
  ,  [created_time]
  )
  SELECT  @new_environment_name as [environment_name]  -- My new environment name from the parameter
  ,  [folder_id]
  ,  @new_environment_description as [description] -- My new environment description from the parameter
  ,  [created_by_sid]
  ,  [created_by_name]
  ,  SYSDATETIMEOFFSET() as [created_time]
  FROM [internal].[environments]
  WHERE [environment_id] = @current_environment_id   -- My existing environment from the parameter
  
  
  -- Get ID from the new environment
  SET @new_environment_id = SCOPE_IDENTITY()
  
  -- Copy the variables from the existing environment to the new environment
  INSERT INTO [internal].[environment_variables]
  (
    [environment_id]
  ,  [name]
  ,  [description]
  ,  [type]
  ,  [sensitive]
  ,  [value]
  ,  [sensitive_value]
  ,  [base_data_type]
  )
  SELECT @new_environment_id as [environment_id]   -- Id from the new environment
  ,  [name]
  ,  [description]
  ,  [type]
  ,  [sensitive]
  ,  [value]
  ,  [sensitive_value]
  ,  [base_data_type]
  FROM [internal].[environment_variables]
  WHERE [environment_id] = @current_environment_id   -- My existing environment from the parameter

  -- Reference this new environment to the same project
  INSERT INTO [internal].[environment_references]
  (
     [project_id]
  ,   [reference_type]
  ,   [environment_folder_name]
  ,   [environment_name]
  ,   [validation_status]
  ,   [last_validation_time]
  )
  SELECT  p.project_id
  ,   r.reference_type
  ,   r.environment_folder_name
  ,   @new_environment_name as environment_name -- My new environment name from the parameter
  ,   r.validation_status
  ,   r.last_validation_time
  FROM  [internal].[environment_references] as r
  INNER JOIN [internal].[projects] as p
     on r.project_id = p.project_id
  INNER JOIN [internal].[folders] as f
     on p.folder_id = f.folder_id
  INNER JOIN [internal].[environments] as e
     on e.folder_id = f.folder_id
     and e.environment_name = r.environment_name
  WHERE  e.environment_id = @current_environment_id -- My existing environment id from the parameter
  
  ------------------------------------------------------------------------------------------------------------
  -- END NEW INSERT INTO QUERIES
  ------------------------------------------------------------------------------------------------------------
        
        DECLARE @sqlString    nvarchar(1024)
        DECLARE @key_name               [internal].[adt_name]
        DECLARE @certificate_name       [internal].[adt_name]
        DECLARE @encryption_algorithm   nvarchar(255)
        
        SET @encryption_algorithm = (SELECT [internal].[get_encryption_algorithm]())
        
        IF @encryption_algorithm IS NULL
        BEGIN
            RAISERROR(27156, 16, 1, 'ENCRYPTION_ALGORITHM') WITH NOWAIT
        END
        
        
        SET @key_name = 'MS_Enckey_Env_'+CONVERT(varchar,@new_environment_id)
        SET @certificate_name = 'MS_Cert_Env_'+CONVERT(varchar,@new_environment_id)
        
        SET @sqlString = 'CREATE CERTIFICATE ' + @certificate_name + ' WITH SUBJECT = ''ISServerCertificate'''
        
        IF  NOT EXISTS (SELECT [name] FROM [sys].[certificates] WHERE [name] = @certificate_name)
            EXECUTE sp_executesql @sqlString 
        
        SET @sqlString = 'CREATE SYMMETRIC KEY ' + @key_name +' WITH ALGORITHM = ' 
                            + @encryption_algorithm + ' ENCRYPTION BY CERTIFICATE ' + @certificate_name
        
        IF  NOT EXISTS (SELECT [name] FROM [sys].[symmetric_keys] WHERE [name] = @key_name)
            EXECUTE sp_executesql @sqlString 
        
        
        DECLARE @retval int
        EXECUTE AS CALLER
            EXEC @retval = [internal].[init_object_permissions] 3, @new_environment_id, @caller_id
        REVERT
        IF @retval <> 0
        BEGIN
            
            RAISERROR(27153, 16, 1) WITH NOWAIT
        END    
         
        
        
        IF @tran_count = 0
            COMMIT TRANSACTION;                                                                                 
    END TRY

    BEGIN CATCH
        
        IF @tran_count = 0 
            ROLLBACK TRANSACTION;
        
        ELSE IF XACT_STATE() <> -1
            ROLLBACK TRANSACTION @savepoint_name;                                                                           
        
        THROW 

    END CATCH

    RETURN 0 


GO

Download as SQL file

NOTE: Please use at own risk and let me know it things could be improved!

DTAP package configurations - part 2

Case
I have a DTAP (Development, Test, Acceptance, Production). How do I use SSIS configurations over these multiple environments?

Solutions
The most commonly used solutions for configurating multiple environments are:
a) XML Configuration File
b) SQL Server Configuration (in combination with environment variable)
c) SSIS 2012 (in combination with the new project deployment)

In this post I will eleborate SQL Server Configuration solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.

Simple Scenario

1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.

Enable package configurations

2) Configuration Type
Select SQL Server in the Configuration type selectbox. Create a new Connection Managers that links to the database with your configuration table (1). Create a new Configuration table or select an existing one (2). Enter a name that will describe the configurations (3). After that click Next.

Configuration Type

3) Select Properties to Export
In this step you select all the properties you want to configure in the config table. In our case we select the ConnectionString property of both Connection Managers (but not the one from the new connection manager that you created in the previous step!). After that click next.

Select the two ConnectionString properties

4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations.

Configuration name

5) Add Environment variable
We now have a third Connection Manager, but its connectionstring will be different on all servers in your dtap environment. So we need to configure that new Connection Manager as well. We will store its connectionstring in a Windows Environment Variable.
This step will vary for each Operating System. The screenshots are from my Windows 7 laptop. Go to the properties of "My Computer", then to Advanced system settings, then to the Advanced tab and then hit he Environment Variables button. Now add a new System variable with the name SsisConfigurationDatabase and the value must me the connectionstring of that new Connection Manager. Repeat this step for each ssis server in your dtap environment.

New System Variable

6) Add second configuration
Before we add a second configuration, you probably need to restart Visual Studio because it doesn't know your new Windows Environment variable. Add a new configuration and select Environment variable as Configuration type. Then select your new variable named SsisConfigurationDatabase. And click Next.

Configuration type

7) Select Target Property
Now select the connectionstring of the new connection manager and click Next.

Select the ConnectionString

8) Configuration name and move up
Give your configuration a suitable name and move it upwards so that it will be the first configuration. After that close the window.

Move it upwards!

9) Copy configuration database
Now copy the new configuration database table to all database servers in your dtap enviroment and make sure its values are adjusted to the server. Also make sure the environment variable is available on each server. Now you can deploy your package to each server and it will use its own configuration.

DTAP package configurations - part 1

Case
I have a DTAP (Development, Test, Acceptance, Production). How do I use SSIS configurations over these multiple environments?

Solutions
The most commonly used solutions for configurating multiple environments are:
a) XML Configuration File
b) SQL Server Configuration (in combination with environment variable)
c) SSIS 2012 (in combination with the new project deployment)

In this post I will eleborate the XML Config file solution. For this scenario I have a simple package with one Data Flow Task that has one CSV source and one SQL destination. Let's start by adding configurations for the two Connection Managers.

Simple Scenario

1) Add configurations
Go to the SSIS menu and select Package Configurations... and then Enable package configurations. After that hit the Add button.

Enable package configurations

2) Configuration Type
Select XML configuration file (default value) in the Configuration type select box and enter a filepath for your config file. After that click Next.

Configuration Type

3) Select Properties to Export
In this step you select all the properties you want to configure in the config file. In our case we select the ConnectionString property of both Connection Managers. After that click next.

Select both ConnectionString properties

4) Configuration Name
Give your configuration a suitable name that will explain its purpose if you have multiple configurations. After that hit the Finish button and close the configuration window.

Configuration name

5) View the file
If you browse to your config file and open it in for example Internet Explorer then you can see what's in it. Now you can just copy the xml config file to all environments. The path to the config file is embedded in the packages. So if you keep the file path the same, then you can just copy the packages and config files between environments. You can edit the config file in notepad to change the values for each environment.

6) Deployment Manifest
If you use different paths to store the config files then you should use the Deployment Manifest to change the embedded file path of the config file. Go to the properties of your project and go to the Deployment Utility page and select True at the CreateDeploymentUtility.

CreateDeploymentUtility

7) Build to create the Deployment Utility
Build your project (right click project in Solution Explorer and select Build). This will create the 3 files in the bin folder of your project:


Folder: \ProjectName\bin\Deployment\

8) Deployment
Now you can give these three files to the server administrator and let him/her double click on PackageConfig.SSISDeploymentManifest to deploy the package(s) and determine the config file folder. It's even possible to change the config value while deploying.