This blog post outlines the implementation approach to use SSIS package configurations as a development and deployment best practice. I have written it as a step by step approach as requested by some of my customers for purposes of setting up and configuring Package Configurations for SSIS. Most companies are using Package configuration, however, are not encrypting the tables in SQL Server. This is not wise as the password in the connection strings are stored in plain text and can be easily read with some level of thought and execution within the environment. For fun I used an older version of SQL to create this. I highly recommend the latest. I did this to show that it can be used by all versions but SQL Server 2000.
Create Configuration Database
The connection strings and other configuration information for SSIS packages are stored in the Configuration database. Each environment will need to have its own configuration database with its own set of connection strings.
In this example the database name is SSISConfigDB.
Create the Database SSISConfigDB
CREATE DATABASE [SSISConfigDB] ON PRIMARY
( NAME = N’PrimaryFileName’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\SSISConfigDB.mdf’ , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’PrimaryLogFileName’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\SSISConfigDB_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’SSISConfigDB’, @new_cmptlevel=100
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [SSISConfigDB].[dbo].[sp_fulltext_database] @action = ‘enable’
end
GO
ALTER DATABASE [SSISConfigDB] SET ANSI_NULL_DEFAULT ON
GO
ALTER DATABASE [SSISConfigDB] SET ANSI_NULLS ON
GO
ALTER DATABASE [SSISConfigDB] SET ANSI_PADDING ON
GO
ALTER DATABASE [SSISConfigDB] SET ANSI_WARNINGS ON
GO
ALTER DATABASE [SSISConfigDB] SET ARITHABORT ON
GO
ALTER DATABASE [SSISConfigDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SSISConfigDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SSISConfigDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SSISConfigDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SSISConfigDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SSISConfigDB] SET CURSOR_DEFAULT LOCAL
GO
ALTER DATABASE [SSISConfigDB] SET CONCAT_NULL_YIELDS_NULL ON
GO
ALTER DATABASE [SSISConfigDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SSISConfigDB] SET QUOTED_IDENTIFIER ON
GO
ALTER DATABASE [SSISConfigDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SSISConfigDB] SET ENABLE_BROKER
GO
ALTER DATABASE [SSISConfigDB] SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
ALTER DATABASE [SSISConfigDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SSISConfigDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SSISConfigDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SSISConfigDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SSISConfigDB] SET READ_WRITE
GO
ALTER DATABASE [SSISConfigDB] SET RECOVERY FULL
GO
ALTER DATABASE [SSISConfigDB] SET MULTI_USER
GO
ALTER DATABASE [SSISConfigDB] SET PAGE_VERIFY NONE
GO
ALTER DATABASE [SSISConfigDB] SET DB_CHAINING OFF
Within the SSISConfigDB database create a table to hold the encrypted connection information.
USE [SSISConfigDB]
GO
CREATE TABLE [SSISConfig].[SSISConfigurationsEncrypted](
[ssisConfigurationId] [int] IDENTITY(1,1) NOT NULL,
[ConfigurationFilter] [varchar](255) NOT NULL,
[ConfiguredValue] [varbinary](512) NULL,
[PackagePath] [nvarchar](255) NOT NULL,
[ConfiguredValueType] [nvarchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[ssisConfigurationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Next, create the view that will be responsible for un-encrypting the configured values. For this example our Pass Phrase is !@Password1!. It is strongly recommended to change this to another strong password. Use a strong password at all times and the production password should be held by production DBA’s.
USE [SSISConfigDB]
GO
CREATE VIEW [SSISConfig].[SSISConfigurations]
WITH ENCRYPTION
AS
SELECT [ssisConfigurationId]
, [ConfigurationFilter]
, CAST(DecryptByPassPhrase(N’!@Password1!’, [ConfiguredValue]) AS NVARCHAR(255)) AS [ConfiguredValue]
, [PackagePath]
, [ConfiguredValueType]
FROM [SSISConfig].[SSISConfigurationsEncrypted]
GO
Create Triggers for Interactions
The triggers are created on the view to handle the interactions with the SSISConfigurationsEncrypted table processes. These triggers will also be used by BI Development Studio (BIDS) during the Package Configurations setup in package development.
CREATE TRIGGER [SSISConfig].[SSISConfigurations_Insert]
ON [SSISConfig].[SSISConfigurations]
WITH ENCRYPTION
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO [SSISConfig].[SSISConfigurationsEncrypted] (
[ConfigurationFilter]
, [ConfiguredValue]
, [PackagePath]
, [ConfiguredValueType]
)
SELECT
[ConfigurationFilter]
, EncryptByPassphrase( ‘!@Password1!’, [ConfiguredValue] )
, [PackagePath]
, [ConfiguredValueType]
FROM inserted
End
GO
CREATE TRIGGER SSISConfigurations_Update
ON [SSISConfig].[SSISConfigurations]
WITH ENCRYPTION
INSTEAD OF UPDATE
AS
BEGIN
UPDATE [SSISConfig].[SSISConfigurationsEncrypted]
SET [ConfigurationFilter] = B.[ConfigurationFilter]
, [ConfiguredValue] = EncryptByPassphrase(‘!@Password1!’, CAST(B.[ConfiguredValue] as nvarchar(255)) )
, [PackagePath] = B.[PackagePath]
, [ConfiguredValueType] = B.[ConfiguredValueType]
FROM [SSISConfig].[SSISConfigurationsEncrypted] A
INNER JOIN inserted B
ON A.[ssisConfigurationId] = B.[ssisConfigurationId]
END
GO
CREATE TRIGGER SSISConfigurations_Delete
ON [SSISConfig].[SSISConfigurations]
WITH ENCRYPTION
INSTEAD OF DELETE
AS
BEGIN
DELETE FROM [SSISConfig].[SSISConfigurationsEncrypted]
FROM [SSISConfig].[SSISConfigurationsEncrypted] A
WHERE EXISTS (SELECT 1
FROM deleted B
WHERE A.[ssisConfigurationId] = B.[ssisConfigurationId]
)
END
GO
Add a SQL user to access the configurations
In this section the steps for creating a user and granting permissions will be shown. It is important that when this is set up in the different environments, Development, QA, Production that different logins and passwords are used. They do not have to be environment matched for Package Configurations to work. That is the brilliance of Package configurations in this implementation. Three separate environments of access and security to appropriately manage the potential differences between the physical setup of each.
Create user login.
CREATE LOGIN [SSISConfig] WITH PASSWORD=N’$$1$Config’, DEFAULT_DATABASE=[SSISConfigDB], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
Add user to the SSISConfigDB database.
USE [SSISConfigDB]
GO
CREATE USER [SSISConfig] FOR LOGIN [SSISConfigDB] WITH DEFAULT_SCHEMA=[SSISConfig]
—Assign permissions to the [SSIS Configurations] view.
use [SSISConfigDB]
GO
GRANT DELETE ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]
GO
use [SSISConfigDB]
GO
GRANT INSERT ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]
GO
use [SSISConfigDB]
GO
GRANT SELECT ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]
GO
use [SSISConfigDB]
GO
GRANT UPDATE ON [SSISConfig].[SSISConfigurations] TO [SSISConfig]
GO
Set up Environment Variable for Configuration Connection
For each machine that will be running the SSIS packages, an environmental system variable will be created to store the Configurations connection string.
Variable name: ConfigurationConnectionString
Variable value: Provider=SQLOLEDB.1;Password=$$1$Config;Persist Security Info=True;User ID=SSISConfig;Initial Catalog=SSISConfigDB;Data Source=.\SQL2008
Configure Connections
The next step is to configure a connection manager within the package to point to our new configuration table to retrieve configurations for the other connections.
This connection will modified using a package configuration.
Choose Package Configurations from the SSIS menu within Bi Development Studio (BIDS) and check Enable package configurations.
Select Add.
Click Next.
Choose Environment variable for the Configuration type and select the Environment Variable we created. Each environment from development to production will point to corresponding configuration database for that environment. At run-time, SSIS will configure the connections based upon the values stored in the configurations table. This allows the deployment options to be changed easily through T-SQL.
Note: When setting up the different environments the Environmental Variable for the Package Configuration database it is important to name the Environmental variable the same. If not the packages will need to be edited for each environment.
Choose the ConnectionString property of the connection manager and name the Configuration.
Click Finish.
Create Configurations
Now the configurations for additional connection managers can be created.
Select Add from the Package Configurations.
Select SQL Server as the Configuration type.
Choose the connection manager that will connect to the configurations database.
Select the [SSIS Configurations] table from the drop down.
Name the Configuration filter for the property of the connection to be set.
Select the corresponding property of the connection to be configured. The default values of the connection will be inserted into the configuration table. These can be overwritten or changed using T-SQL against the [SSIS Configurations] table.
Finally, name the configuration within the table.
Click Finish.
You will see the configurations being accessed within the output window at runtime.
Additional Recommendation
The configuration database will need to be set up for each environment with the appropriate connections. Additionally, environment variables will need to be created for each node within a cluster. Again it is extremely important to name the Environmental Variable the same for each node.