Using a SQL Server Alias for SSIS Package Configuration Database Connection String

By:   |   Comments (7)   |   Related: 1 | 2 | 3 | > Integration Services Configuration Options


Problem

I use the SQL Server package configuration option in my SQL Server Integration Services (SSIS) packages which allows me to use a SQL Server table to store and retrieve SSIS package parameters. I followed the steps in the tip SQL Server Integration Services SSIS Package Configuration and this works great. However, the tip uses an environment variable for storing the connection string for the configuration database and I can't do it this way in my shop. Do you have any ideas on a different way of doing this?

Solution

Using an environment variable package configuration for storing the connection string to the SSIS package configuration database is certainly not the most elegant solution as it may require you to restart things that are running like SQL Server Agent when the environment variable is set or changed. Since writing that tip that you referenced I have come up with a better solution - SQL Server Aliases. As the name implies, a SQL Server Alias is a name that you setup that "points" to a SQL Server instance. When you specify the Data Source in your connection string, you use the SQL Server Alias name rather than the server name. If you want to change the server for the SQL Server package configuration table, you simply change the SQL Server Alias.

In this tip I will walk through setting up the SQL Server Alias and using it with SQL Server package configuration in an SSIS package.

Scenario

I'm developing SSIS packages on my laptop and I'm using a SQL Server table (i.e. SQL Server package configuration) to store various package parameters like the connection string to my application database. The SQL Server package configuration table is in the SSIS_CONFIG database running on my laptop. The Data Source in the connection string for the SSIS_CONFIG database is localhost. When I deploy the SSIS packages to a QA or PROD server, the SSIS_CONFIG database may be setup differently; e.g. it may be on a different server, the SQL Server may be a named instance, and so on. The point is I need to be able to easily change the connection string for the SSIS_CONFIG database and without touching any of my SSIS packages.

By setting up a SQL Server Alias, I can set the Data Source property of the SSIS_CONFIG connection string to the SQL Server Alias name. On any machine that I deploy the SSIS packages, I just need to create the SQL Server Alias. If after deployment I change the location of the SSIS_CONFIG database, I just need to change the SQL Server Alias. I don't have to make any changes to the SSIS packages.

Setup the SQL Server Alias

There are two tools that you can use to setup a SQL Server Alias: SQL Server Configuration Manager and SQL Server Client Network Utility. The tip How to setup and use a SQL Server alias does a great job of showing how to use both of the tools. However, there is one subtle point that I will cover here. When you are running on a 64 bit machine, you have the option of setting up 64 bit and/or 32 bit SQL Server Aliases. There are two instances that I can think of where you need a 32 bit alias on a 64 bit machine:

  • SQL Server Management Studio (SSMS) is a 32 bit application; if you want to connect to a database using an alias you'll need to setup a 32 bit alias
  • You are using the 32 bit version of DTEXEC to run SSIS packages; e.g. you're using the 32 bit Jet Engine for Excel data sources

With SQL Server Configuration Manager running on 64 bit, you'll see the following:

sql server configuration manager

Note the two nodes: SQL Native Client 10.0 Configuration (32bit) and SQL Native Client 10.0 Configuration (this is the 64 bit one).

There are two versions of the SQL Server Client Network Utility; a 64 bit version and a 32 bit version. When you run on a 64 bit machine, the default is to load the 64 bit version of the utility which only allows you to configure 64 bit aliases. To configure 32 bit aliases you need to run the 32 bit version of the utility which you will find by default in C:\Windows\SysWOW64\CLICONFG.EXE.

To setup a SQL Server Alias, right click the Aliases node in SQL Server Configuration Manager and select New Alias. I setup the following alias for both 64 bit and 32 bit:

set up a sql server alias

Create an SSIS Package With SQL Server Package Configuration

Now I'm ready to show how to use the SQL Server Alias in an SSIS package. SQL Server package configuration requires an OLE DB Connection Manager that specifies the SQL Server database where the SSIS configuration table will live. I'm going to create a new database for SSIS package configuration tables; I will name the database SSIS_CONFIG. I'm doing this so that each SSIS "application" can have its own package configuration table and I'll store it in this database. The database name is deliberately the same as the SQL Server Alias; it's not a requirement but too many different names confuse things.

To complete the example we need to perform the following steps:

  • Create an OLE DB Connection Manager for the SQL Server package configuration database

  • Create an OLE DB Connection Manager for our application database; e.g.. the database where we will be loading data

  • Add a SQL Server package configuration to set the connection string for the application database

  • Add an Execute SQL task to test running the package

Create OLE DB Connection Manager for the SQL Server package configuration database

To begin open Business Intelligence Development Studio (BIDS) and create a new SSIS package. Right click in the Connection Managers area of the SSIS package designer and select New OLE DB Connection from the menu. Fill in the dialog as follows:

how to use the sql server alias in an ssis package

Note that for Server Name you enter the name of the SQL Server Alias created in the previous section. Select SSIS_CONFIG as the database and click Test Connection to make sure the connection is okay. Click OK to save the connection manager. By default the name of the connection manager is SSIS_CONFIG.SSIS_CONFIG (i.e. SERVERNAME.DATABASENAME). Right click on it and rename it to SSIS_CONFIG.

Create OLE DB Connection Manager for the application database

Create another OLE DB Connection Manager. Specify locahost as the Server Name and select a database; e.g. MSSQLTipsExamples. Name the connection manager APPLICATION_DATABASE.

Add SQL Server package configuration for the application database connection string

Right click in the SSIS package Control Flow area and select Package Configuration from the menu to display the Package Configurations Organizer as shown below:

in the ssis control flow area select package configuration

Click the Enable package configurations checkbox and click Add to launch the Package Configuration Wizard. Fill in the Select Configuration Type dialog as follows:

click add to launch the package configuration wizard

Notes on the above dialog:

  • Select SSIS_CONFIG as the Connection; this is the OLE DB Connection Manager created earlier that has the SQL Server Alias specified for the Data Source

  • Click New to create a SQL Server package configuration table; I named it RHOMBUSConfiguration where RHOMBUS is the hypothetical name of my application

  • After creating a SQL Server package configuration table, you can select it from the Configuration table dropdown when you setup other package configurations

  • Enter APPLICATION_DATABASE in the Configuration filter; you can select from the dropdown list when you reuse the configuration in another package

Click Next to proceed to the Select Properties to Export dialog. Select the APPLICATION_DATABASE ConnectionString as the property to be set by this package configuration as shown below:

select ssis _config as the connection

Click Next to proceed to the Completing the Wizard dialog. Enter APPLICATION_DATABASE as the Configuration name as shown below:

click new to create a sql server package configuration table

The last step is to add a task to the SSIS package so that we have something to test. Add an Execute SQL task and configure it as shown below:

add an execute sql task and configure it

The Execute SQL task will get the current date and time; this is just enough to verify that we have everything setup correctly. If we do the package will execute successfully; otherwise we'll get some sort of error. The package must connect to the SSIS_CONFIG database to get the package configuration, then connect to the APPLICATION_DATABASE using the connection string that it retrieves from the SSIS_CONFIG table.

Test the SSIS Package

Launch the SSIS package from BIDS. You will see the following message in the Debug output window:

Information: 0x40016040 at Package: The package is attempting to configure from SQL Server using the configuration string ""SSIS_CONFIG";"[dbo].[RHOMBUSConfiguration]";"APPLICATION_DATABASE";".

If there is an error retrieving the package configuration, you will see the details in the Debug output window. Note that an error retrieving a package configuration does not abort the package. The package will still execute and it will use the value in the package. However, the package may ultimately fail because the value in the package is expected to be set from the package configuration and it wasn't.

As a final step go to the SQL Server Configuration Manager or SQL Server Client Network Utility and change the Server value for the alias. In my case I have a virtual machine running an instance of SQL Server 2005 so I will fill in the IP address as the value for the Server. Since the SSIS_CONFIG OLE DB Connection Manager uses the alias, the package will now retrieve the package configuration for my APPLICATION_DATABASE from the virtual machine without me having to make any changes to my package.

Note that the SSIS_CONFIG database must exist on the machine specified by the SQL Server Alias and the RHOMBUSConfiguration table must be copied to that SSIS_CONFIG database. There are a number of ways to get the package configuration table setup on another server; you can do it manually by scripting the CREATE TABLE statement, issue a SELECT INTO statement if you have a linked server, and so on. Another way would be an SSIS package that conditionally creates the package configuration table on a target database then copies the rows from a source database. Yet another possibility would be to use BCP; it still comes in handy for quick and dirty tasks like copying a package configuration table from one server to another.

Next Steps
  • This is but one example of how you can setup SQL Server package configuration in SSIS so that you can easily change the database server for the package configuration table when you deploy.
  • One caveat with this approach is the only thing that needs to change in the SSIS_CONFIG OLE DB Connection Manager connection string is the Data Source. This is typically not an issue as the name of the package configuration database can always be the same, the name of the table that holds the package configuration can be based on the application's name, and you should be using integrated security.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, August 30, 2017 - 2:45:10 PM - pGwtech Back To Top (65615)

 Good article, useful -- can use it to map ip to ip when a subnet change occurs.

Monday, June 9, 2014 - 11:41:57 AM - Ray Barley Back To Top (32156)

Take a look at this tip to see how to use a view on the config table instead of the table name: http://www.mssqltips.com/sqlservertip/2745/how-to-support-multiple-sql-server-package-configurations-in-ssis/

 

Saturday, June 7, 2014 - 9:06:57 AM - Karthi Premakumari Back To Top (32132)

Ray, I am going to try this today. Thanks.

Scott, how do you use the view that returns the hostname to connect to the configuration table?  I understand how we can have a static connection to the config db using an alias in Ray's solution but I dont know how to use a view in that connection object which connects to the config table?

sorry if I sounded dumb, I am fairly new to SSIS.

Wednesday, July 18, 2012 - 4:03:01 PM - Ray Barley Back To Top (18616)

Open SQL Server Configuration Manager and make sure that the TCP protocol is enabled for the following (I'm looking at SQL Server 2008 R2): 

SQL Server Network Configuration - Protocols for MSSQLSERVER (this would be my first guess)

SQL Native Client 10.0 Configuration - Client Protocols

SQL Server Native Client 10.0 Configuration (32 bit) - Client Protocols

Tuesday, July 17, 2012 - 4:06:19 PM - Sam S. Back To Top (18587)

Thanks for great article!

I have setup everything according to your steps.

For some reason, my Connection manager does not recognize my Alias.

Any ideas?

 

Friday, November 4, 2011 - 8:59:58 AM - Scott C Back To Top (15020)

My solution was to create a base table for SQL configuration that allowed multiple values for each item, with an additional field that specifies whether the value is for production, stage, test, or development environments (or a global value for all environments).  There is a table of server names that specify which environment each server belongs to, and a view that uses the HOST_NAME() function to select the values for that environment.

So a package running on server P in the production environment gets the production configuration values, while the same package running on server T in the test environment gets test configuration values.  If anyone loads a package into the IDE on their laptop for debugging, they get development configuration and can be confident that they can test it without touching production data or sending embarassing emails to customers.

All packages are self-contained, there are no XML files, environment variables, aliases, or other artifacts to deploy.  All packages have a fixed, unconfiguered connection to the configuration database.

Wednesday, August 10, 2011 - 12:10:13 PM - Thom Back To Top (14361)

They we handle this in our shop is at the first of each SSIS package a task reads an XML file on the local server where the package is running to get the storage location of the SSIS configuration database/table.














get free sql tips
agree to terms