Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more
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?
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.
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:
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:
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:
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:
Click the Enable package configurations checkbox and click Add to launch the Package Configuration Wizard. Fill in the Select Configuration Type dialog as follows:
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:
Click Next to proceed to the Completing the Wizard dialog. Enter APPLICATION_DATABASE as the Configuration name as shown below:
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:
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.
- 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.
Last Update: 2011-08-10
About the author
View all my tips