SSIS 2012 Configuration Guide – Part 2: Scenarios, Setup and Migration

This post is the second part of the SSIS 2012 configuration Guide. The first part gave a general introduction about the new deployment models, parameters, environments and the SSIS catalog.

In the second part of the guide I will describe the configuration scenario in general, recommended default configurations, how to setup parameters and environments and migration options for existing SQL Server based configurations.

Configuration Scenario

As mentioned in my first post in previous versions of SSIS 2012 we had several possibilities how to configure our SSIS packages. With new concept of parameters and environments in SSIS 2012 the way to use configurations is now standardized, as shown in the picture below.

image

If you follow the design of master and child packages all configurable parameters are now stored in one environment, that is stored in the SSIS catalog. A SQL Server agent job, which triggers the master package of your ETL process, has an environment assigned, which will be used for package execution. All child packages can then use the parameters of the assigned environment.

This also means that the old concept of assigning different configurations also to child packages is not possible anymore. Shared configuration parameters used by several packages should be defined on a project level. If you want to share parameters across projects you need to stored them redundant in different environments and manage it on your own. I know that this is a small loose of flexibility but it is currently the only way to go.

Default Configurations

The following list contains recommended SSIS properties which should be made dynamic configurable by default in every SSIS project / package:

  • Connection Strings for all Connection Managers
  • BLOBTempStoragePath
  • BufferTempStoragePath
  • DefaultBufferMaxRows of each dataflow (optional on package level)
  • DefaultBufferSize of each dataflow (optional on package level)
  • EngineThread of each dataflow (optional on package level)
  • Delta or Inital Load flag (optional on package level)
  • Transaction Handling (optional on package level)For a better understanding I added two screenshots to visualize the default configuration on project level…imageand on package level:

    image

    As the temporary storage paths usually are the same for all packages within a project they can be created as parameters at project scope. The default buffer properties and the engine threads can differ between the packages and therefore should be created at package scope.

    If packages are designed to handle both, delta and initial loads, it is recommended to configure the execution mode in order to switch between them during runtime. Therefore this table can be used to automatically create new environments. How this can be done will be explained later.

    For configuration purposes all used connection managers are created on project level, so they are available for all packages. This is not mandatory, but makes it later easier to configure needed connection managers, at least when they are used in more than one package.

    Deployment

    Before you can use Environments you need to deploy your project to the SSIS Catalog. If you only want to test your parameters on your developer box, you can use the parameter default values that you can define in Visual Studio.

  • In order to deploy your project you just right-click on your project and press click “Deploy”, which will open the deployment wizard where you need to specify the SSIS server name and the target path, like in my sample below.image

    After you are done with the wizard you should see your project and all containing SSIS packages in the SSIS catalog.

    image

    Environment Setup

    If you look carefully to your “Environments” folder you will recognize that it’s empty. In order to configure your project you need to create an environment first. Typically you will then add all defined parameters to your project environment. In larger projects this can take some time, if you do it manually. In order to make it a little bit more comfortable you can also use this stored procedure that analyzes all defined parameters of a project in the SSIS catalog (from the “internal.object_parameters” table in the SSISDB) and adds them to your new environment.

    USE [SSISDB]
    GO
    
    DECLARE @RC int
    DECLARE @environment nvarchar(128)
    DECLARE @folder nvarchar(128)
    DECLARE @project nvarchar(128)
    
    -- TODO: Set parameter values here.
    
    EXECUTE @RC = [dbo].[CreateEnvironmentFromParameters] 
       'MyEnvironmnent' -- name of the environment
      ,'Test' -- project folder 
      ,'Configuration_SQL2012' -- project name
    GO

    This and all following stored procedures can be found here:

    https://skydrive.live.com/redir?resid=EDD20A747F7B8FDE!32835&authkey=!AFvRzY5jzfphR_Y

    CreateEnvironmentFromParameters_SP.sql

    The stored procedure has four input parameters, each one is mandatory:

  • environment: Determines the environment name for which the variables will be created. The name can be existing or new. If it is new it will be created. Otherwise the current variables will be deleted and the new configuration will be loaded.
  • folder_project: Determines the SSIS catalog folder name in which the project is stored. The folder has to be existent, if a non-existing name is provided, the stored procedure will raise an error.
  • folder_environment: Determines the SSIS catalog folder name in which the environment is stored or should be created. It can be existing or new. If it is new it will be created. Otherwise the folder will be created and the environment will be added.
  • project: Determines the project name, holding the parameter values which should be used for the environmental variables. The project has to be existent, as it is the reference used to create the variables. If a non-existing name is provided, the stored procedure will raise an error.The stored procedure creates for all used connection managers and parameters within the project and all packages a variable in the defined environment. The value assigned to each variable is the design default value currently stored in the “internal.object_parameters” table. If another value should be assigned, this has to be changed manually.

    Naming Conventions

    As mentioned before with SSIS 2012 it is not possible to assign more than one environment during runtime. This means that all parameters, on project and on package level, for the whole ETL process that the master package triggers, need to be stored in on environment. In order to be able to differentiate between the different parameters within each package, we need to agree on a certain naming convention. I will make just a suggestion how it could look like, but feel free to take your own specific one.

    So the naming convention I use has the following syntax:

    General + _ + ParameterName
    PackageName + _ + ParameterName

    All project level parameters have the prefix “General_” and all package level parameters have the prefix “<PackageName>_”.

    As an example I added a screenshot for my suggested default parameters.

    image

    Map environment variables to project parameters

    After the environments and variables have been created, they have to be mapped to the according project and parameters. This can be done manually by defining the references between projects and environments in the “Configuration” window and by mapping the environmental variables to each parameter and connection string in the project and its’ packages.

    As this can get very time consuming, especially for bigger projects containing many packages and parameters, it is recommended to use the following stored procedure in order to create the references and variable mappings.

    CreateVariableParameterMapping_SP.sql

    (Important: The stored procedure can only be used, if the creation of the environment and its variables has been done by the stored procedure “CreateEnvironmentFromParameter_SP.sql”)

    The stored procedure has four input parameters, each one is mandatory:

  • environment: Determines the environment which holds the variables to be mapped. The environment has to be existent, if a non-existing name is provided, the stored procedure will raise an error.
  • folder_project: Determines the SSIS catalog folder name in which the project is stored. The folder has to be existent, if a non-existing name is provided, the stored procedure will raise an error.
  • folder_environment: Determines the SSIS catalog folder name in which the environment is stored. The folder has to be existent, if a non-existing name is provided, the stored procedure will raise an error.
  • project: Determines the project name, holding the parameter values, which should be mapped. The project has to be existent, if a non-existing name is provided, the stored procedure will raise an error.The stored procedure checks each of the parameters for existence and raises an error, if only one is missing. It also checks, if between the declared project and environment already a reference is defined. If not, it will be created automatically by the stored procedure. Afterwards the mapping between environmental variables and project parameters will be performed.

    Migration of old SSIS configurations

    In order to migrate configurations from projects using prior versions of SSIS 2012, the following stored procedure can be used to migrate existing SQL Server configurations. If you use XML configuration files you can transform them to SQL Server configuration and then apply the script:

    CreateEnvironmentFromConfiguration_SP.sql

    The stored procedure has four input parameters:

  • environment: Determines the environment to which the configuration values should by migrated. If the specified environment does not exist, it will be created.
  • folder: Determines the SSIS catalog folder name in which the environment is stored. If the specified folder does not exist, it will be created.
  • database: Determines the database storing the configuration values of the prior SQL Server version. The database has to be existent, otherwise an error will be raised.
  • configuration filter: This parameter is optional. If it is not specified, the whole configuration table will be loaded into the specified environment. If a value is provided for this parameter, only the corresponding values will be added to the environment.The stored procedure also offers the possibility to migrate certain configurations to one environment. Therefore it has to be executed several times with different configuration filter settings. So all defined configuration filters will be loaded into the same environment.If a non-existing configuration filter is provided, only an empty environment will be created.

    Conclusion

    SSIS package configurations deliver a common way to implement ETL packages in a highly dynamic manner and to reduce direct adaptions after implementation has been finished and the packages have been rolled out to an environment. In combination with the new deployment model, the SSIS catalog and the also newly introduced environments configuration management becomes more comfortable and standardized.

    Normally the creation and setup of the new configuration method would take a lot of time, at least for bigger projects, but with the delivered guidelines and stored procedures you can easily create and migrate new environments, variables and parameter references. Therefore it is recommended to migrate prior versions to the new deployment and configuration model.

    If you have further questions, recommendations or tips please let me know.

22 thoughts on “SSIS 2012 Configuration Guide – Part 2: Scenarios, Setup and Migration

  1. Zaim Raza says:

    Is there anyway we can control Message type(OnPostExecute,OnInformation ….).
    For example i want to see only the OnPostExecute and OnError Only to be log in SSISDB Catlog…

    Thanks in advnace,
    Zaim Raza.

  2. Sweety says:

    Thank you!
    Your guideline really helped to understand new SSIS deployment model deeper and to set up my SSIS environment

  3. MSBIS says:

    I am currently working in a project with a pre defined specification. I was asked to create master and child packages for the same.
    Master file has its own configuration file and child file also. During development it was working fine.

    As per the specification , I have to create a .bat file and .bat file call the master packages and master package call several child packages.

    I have used config file for master and child packages.

    When I try to create .bat file , I am able to provide the config file location for the master package only. However I am unable to provide the config file path for the child package .

    Any hints / suggestion would be highly appreciated.

    • Hi Sanjay,
      yes that is true, you can only dynamically assign a configuration file to you master package. SQL Server configuration is a little bit more flexible, but if you want to stick to XML config files put all configuration to one config file and derive the settings to your child packages by using parent package configurations in you child package.
      Cheers…

  4. Carl says:

    I have followed the steps listed to create and assign environment variables to my package via configurations, but still my package is using the internal Project.param contents for connection strings. I deployed my backage not by “Deploy”, but rather by building it, and handing off the *.ispac file to a dba where they then deploy the package. Would this be causing the configuration/environments not to be used?

    • Hi Carl,

      no the deployment should be fine. Did your dba deployed to the SSIS Catalog? Within your SSIS Catalog you need to assign Environments to your SSIS Project and when executing your package, you need to define the one you want to use during execution. Connection Strings are defined on the project Level, that’s why they reside in the Project.param. But you can still have them dynamically by using Environment variables.

      Cheers

  5. pauste1 says:

    Thanks for a great article. A lot of similar articles advocate using the Environments to configure parameters for DEV, TEST, PROD but I notice you don’t mention this in the Environment section, but have configured the Catalog folder in a DEV, TEST, PROD configuration.

    Is there a specific reason for this?

    I am investigating how to deploy multiple versions of the same project in the IS Server Catalog in different environments. eg. Dev 3.0, Test 2.0, Prod 1.4, and there appears to be scant articles on this topic-instead focussing on using the Environments to configure the different DEV, TEST, PROD. The folder approach appears the only approach to support multiple versions apart from multiple IS servers. What do you think?

  6. Kurt says:

    Regarding the following…

    If you want to share parameters across projects you need to stored them redundant in different environments and manage it on your own

    We will have many projects that will need to share the same connection strings. Many sources are being extracted into a staging DB. Then we have many TL flows (separate projects again) using the staging DB as their source. The connection info for the staging DB is of course the same across all these projects. It would be ideal to store the connection info in one place… meaning 1 Environment.

    When referencing an environment from a project you choose an environment from any folder within the catalog… So we are thinking of a ‘common’ folder which contains this single, master environment.

    do you see any issues with this approach? Thanks so much

    • Hi Kurt,
      yes you are right. Currently there are no possebilities to share variables between environments or to use more than 1 environment in an execution. What you can do is, that you write an own stored procedure, which you call in order to change parameter values. The SP ensures that all variables with the same name in different environments are changed to the new value. You could also build a small GUI around. In general these are all just workarounds.

      Cheers,
      DWJunkie

  7. Mark says:

    How do these new settings affect connections which cannot use Windows authentication? My understanding is that in prior SSIS versions the ‘best practice’ was to set protection level to “dont save sensitive”, and then have passwords in a secured configuration.

    I started trying to set things up in SSIS 2012 to have pw in a sensitive parameter, so that when deployed to the catalog, that parameter would be mapped to an environment. But when doing that with “dont save sensitive”, the connection doesnt work when debugging (as the compiled code does not include the parameter).

    Easy to do if I use the protection levels instead, but they have their own drawbacks. …

  8. Kokoss says:

    Hi,

    Thx a lot for sharing this.
    I’ve just figured out what could be a bug. When setting parameter on childs package level as mandatory and mapping it to environment variables in SSIS catalog, master packages failed to validate / execute and says that a required parameter is missing in the subjob… weird…
    Workaround set parameter as non mandatory (not required) in subjobs …

    Br

  9. Juan says:

    Hello,

    This is exactly how we have been doing configuration management and deployment of integration services 2012 in our organization. There is one challenge though that we have come across. We haven’t found a good way to reuse the same ssis package while passing different parameters from different jobs and still be able to keep the configured parameters for each job documented in SSIDB; if you configure each job to pass different parameter values to the same package, the parameters are actually saved under the the job step’s command. The challenge is that there isn’t a clean way to retrieve those parameters from msdb, unless someone out there has been able to? We need to retrieve the configured values to be able to build a report on all jobs and packages which contain all configured values. This makes our jobs easier when troubleshooting a problem.
    The only work around we have found is to simply create two different packages, one for each job. I have a problem with not being able to reuse a package from a conceptual stand point. I am sure there is someone out there that has come up with a good solution. Can you please share it?

    thanks,

    Juan.

  10. m60freeman says:

    Great set of articles! I’m creating my first SSIS 2012 package and am having a bit of difficulty. I’m hoping that you might have some advice with regard to dynamic environment variables.

    I have a project containing a single package with a “ConnectionString” in Project.params. I have Solution Configurations set up for Development, Test, and Production. When I change the Configuration, the ConnectionString changes. I parameterized the ConnectionString in the database’s Connection Manager using $Project::ConnectionString.

    I built the project and ran the resulting .ipac file it on the target development server. From SSMS, I then EXEC:

    create_environment (to set up the Development environment), then
    create_environment_reference (to link the package to the environment), then
    create_environment_variable (ConnectionString), and then
    set_object_parameter_value (the connection string for our development server).

    When I SELECT from the catalog views, everything seems populated the way I want it (except that the environment_folder_name is NULL even though I specified @folder_name=N’Packages’ in the procedure calls). I created a job to run the package and when I run the job, I get:

    “Environment reference Id: NULL. Description: In order to execute this package, you need to specify values for the required parameters.”

    What might I have missed or done incorrectly?

    Thanks,
    Mark

    • Did you try to create the environment via SSMS? Did you also map the Variable from your environment to your Project parameter? Btw. project connection strings can be configured directly with an environment variable. There is no need to use a Project parameter for the connecting string.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s