Thursday, May 29, 2008

SSIS Configuration to Configuration to Configuration Schema

I've gotten several requests to put down in writing the configuration schema that I use as the base of my SQL Server Integration Services framework. It contains a set of configurations: an indirect environment variable configuration, which points to an XML configuration file configuration, which points to a SQL Server configuration. I learned this configuration from the Project REAL Reference Implementation. If you're getting started with a BI implementation, I highly recommend that you download it for some great ideas and best practices.

Steps to implement:

1) Create an environment variable on your machine with the name of SSIS_CONFIG_FILE and the value of: C:\SSIS\Config\MasterConfigFile.dtsConfig.

2) Create an SSIS configuration file at C:\ SSIS\Config\MasterConfigFile.dtsConfig with the line:
<configuration valuetype="String" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" configuredtype="Property"><configuredvalue><configuration configuredtype="Property" path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" valuetype="String"><configuredvalue>Data Source=EnvironmentServer;Initial Catalog=SSIS;Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;</configuredvalue></configuration>
where “EnvironmentServer” is replaced with the appropriate server for that environment.

3) On the EnvironmentServers, create a table called SSIS.dbo.SSISConfigurations with all connection strings and values that you could want to use in your packages with the ConfigurationFilter "CommonConfigurations". You can create this table by opening up a package, creating a SQL Server configuration, and select the “New” button next to the Configuration Table option.

4) In your package, create an OLE DB connection manager named CONFIG_SERVER with dummy server and database information.

5) In your package, create an XML configuration file Package Configuration with the option selected “Configuration Location is stored in an environment variable” and select the environment variable SSIS_CONFIG_FILE. If the variable does not exist, close and reopen BIDS. Then (and make sure this is the second configuration entry), create a SQL Server Configuration that uses the CONFIG_SERVER connection manager and the “CommonConfigurations” filter.

6) For any new connection managers that you create, as long as you follow the naming convention of the connection managers that are already in the SSISConfigurations table, the connection string will automatically be loaded for use in your package!

All of the names and locations can of course be replaced by something more relevant to your servers. They can even be different on each server, just as long as the name of the environment variable is the same on all servers.

By using this schema, you reap many benefits. One perk is allowing you to move your packages through environments without having to change the package or the configuration file. You also are encapsulating all of your connection information in one database table, letting you secure private information. Finally, you are reducing the development time of your packages because by you don’t need to recreate the configurations to all servers; they will already be available for your package.

Version: SQL Server 2005 SP2

28 comments:

Chris said...

This is great. I have two questions however. When you go to create a new package and add the config server info etc and go to add a connection to be loaded from the config server, how do you make it read the config? It only reads at load time from my experience. Secondly, how do you keep it from overwriting the SQL server's info in the config table when you make a new connection and reload the package?! It requires you to export something, not just read config from the server.

Jessica M. Moss said...

Hi Chris,

Thanks for your comment! You are absolutely right that configurations are read at load time (and run time). You'll have to add your new connection manager, followed by saving, closing, and reopening the package for the connection string to be used in the package. One of the ways to implement this is to create a template package where all of your common configurations are already in the package, so you don't need to worry about adding new connection managers.

I'm not sure I fully understand your second question, but if you are talking about how to use the same configuration from a new package, you have to do this in a two step process. First, add a new Package Configuration, using the same server, database, table, and filter. Make sure that you select that you want to "Reuse Existing" configuration. Once you've completed that, you can continue onto the second step of Editing the Package Configuration and add your new configuration values.

Let me know if you have any further questions,
Jessica

Chris said...

Jessica,

I figured the second part out later on in the day. Apparently VS was having some sort of problem showing me the "Reuse existing" option. Thus, it kept overwriting my current config before loading it into the package. It was quite irritating. Anyway, thanks for the nice summary of the project real info and the quick reply.

P.S. I tried briefly to make a template package, however couldn't find the silly export as template option where it should have been. I'll have to give that a go again later this week.

Thanks again.

Anonymous said...

Hi Jessica,

Help, the XML seems to be formed incorrectly or I am off target on something. Can you point me in the right direction?

Thank you,
David

Jessica M. Moss said...

Hi David,

Sorry about that! Not sure what happened there.

Please try this XML:

<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo />
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="\Package.Connections[CONFIG_SERVER].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=EnvironmentServer;Initial Catalog=SSIS;Provider=SQLNCLI.1;Integrated Security=SSPI;AutoTranslate=False;</ConfiguredValue>
</Configuration>
</DTSConfiguration>

-Jessica

ez_dude said...

Hi Jessica, Is it possible to have encryption on the SSISConfig table. Since the configurations contain database connections it would be nice to have passwords encrypted.

Thanks.

ez_dude said...

Ok found this as a good approach for encryption.

ez_dude said...

http://curionorg.blogspot.com/2007/05/encrypted-sql-server-ssis.html

Hairless Monkey said...

One thing I noticed using this method, is that for any connections not in a particular package, Visual Studio will throw errors when loading the package. Furthermore, if I edit the configuration in that package, it will remove any connections settings not used in that package. That is unfortunate as, unless there is some other way, that means that I have to add every single connection I might use to every single package.

Jessica M. Moss said...

Hi Hairless Monkey,

SSIS 2008 changed so that this is the case. You can either include all connections, as you mentioned, or create an intermediary step, where you use configurations to load variables. You can set an expression on each configuration to load from its associated variable. That should get around the errors.

Good luck!
Jessica

gourav said...

hi there i have a question i have created a config file with sql connection string bt i dont understand how we link this connection string value with oledb destination connection string
How can we use the config file to get the connection string from there

BigAlSki said...

This is based on the some of the steps in the SSIS Management Framework Design chapter of the SSIS 2008 Problem-Design-Solution book.

Using SSIS 2008, I could never seem to get the Parent Package Configuration to work correct. I have variables in each child package that should be populated by the corresponding parent variables. So I gave up on that figuring that SQL Server based configurations made more sense, using the same variable names in both the parent and the child packages, I could run the child packages individually. When creating a new child package and using the existing SSIS Configurations table, sometimes (not always) this would result in the Configured Values being set to empty strings. Deleting the SQL Server Configuration file and starting over would usually fix the problem. Anyone else experience this? What could I be missing with Parent Package variables?

Jessica M. Moss said...

Hi gourav,

If you create the config file, create the connection manager, and enable package configurations, the package will read it in based on the name of the connection manager. If you're having other problems, you may want to look at: http://msdn.microsoft.com/en-us/library/ms140213.aspx

Best,
Jessica

Jessica M. Moss said...

Hi BigAlSki,

I wonder if the child package is loading the value from both the parent package configuration and the SSISconfigurations table, but not necessarily in the same order. This would be why it only sometimes works.

Try a few things to see if you can try to narrow down when the issue is having: Add the namespace to the user variables; Rename the child variable so that it will not be set from the SSISConfigurations table; Try removing the value from the SSISConfigurations table to see if it always loads.

The big thing is to reduce the amount of working pieces, and hopefully that will clarify it!
Jessica

BigAlSki said...

Regarding Parent Package Variables, I believe I read somewhere that the variables in the child packages are resolved at run time. So, when you load a project, and look at the variables for a child package they will be empty.

Elegor said...

This works great in BIDS, but I cannot get it to work when I deploy the packages to SQL Server Agent job. I have the environment variable on that server, I have the config file in the same location, but the job gets connection timeouts trying to connect to the CONFIG_SERVER (three times, once for each of my database connections in the package).

I've tried pointing the job directly to the config file, I've tried manually updating the connection string on the Data Sources tab, but I get the same results every time. I've been struggling with this for about a week and it's driving me nuts. Any ideas?

Elegor said...

It seems I figured out my problems. The first problem was a malformed XML file. It hadn't actually worked in BIDS, it was using saved DB connection information. Then I had Provider issues on the server. Thanks Jessica for this great article.

EvilDBA said...

I can see the portability of this, but why are we using the Environment Variable + Config file? Why not just point directly to the SQL Server table that contains the connection strings?

Jessica M. Moss said...

EvilDBA,

A middle configuration file allows to set multiple configurations if needed.

Jessica

Nitu said...

Hi Jessica,

Thanks for the very useful posting. I have a question though.

5) In your package, create an XML configuration file Package Configuration with the option selected “Configuration Location is stored in an environment variable” and select the environment variable SSIS_CONFIG_FILE. If the variable does not exist, close and reopen BIDS. Then (and make sure this is the second configuration entry), create a SQL Server Configuration that uses the CONFIG_SERVER connection manager and the “CommonConfigurations” filter.

a) as you mentioned that it should be second config entry. What should be the first?
b) what should be name of this xml config entry?

I will extremely appreciate sooner reply. Thanks!

Jessica M. Moss said...

Hi Nitu,

XML is first, SQL is second.

You can name it whatever you would like.

Best,
Jessica

Ruben De Oliveira said...

I was having the same problem
Hairless Monkey described: "...for any connections not in a particular package, Visual Studio will throw errors when loading the package... ...that means that I have to add every single connection I might use to every single package".
I resolved that by creating entries in the SSIS_Configurations with the connection name in the ConfigurationFilter column. Then, instead of creating a Common Package configuration, I created one for each connection I would need in that package, using its name as the filter. Then I created the corresponding Connection Managers. Worked Perfectly! email me for details. Thanks!
ruben.deoliveira@syniverse.com

xtoolmaker said...

How do you accomplish step #4?

4) In your package, create an OLE DB connection manager named CONFIG_SERVER with dummy server and database information.

What is meant by "dummy" server and db info. What happens when you try to advance to the next screen and it tries to verify the db server and connection and then times out?

Also, I have the book and in the book it says to create a variable which is missing from the steps posted here. Is the variable still important?

I don't know why I'm having so much trouble with this. I would be very grateful if a step by step guide to configure this was provided.

Very confused.

Thanks.

Jessica M. Moss said...

Hi xtoolmaker,

By dummy server and database information, I mean any database that is not your final one to ensure that the configuration is set up correctly. Setting up the variable is in step 1.

Keep in mind that this post is for SSIS 2005, so may not directly relate to what you are trying to do now.

Jessica

xtoolmaker said...

Thanks Jessica. I got it working. I appreciate the response and love the book.

Anonymous said...

Nice Post, thanks!
There is now an utility available in CodePlex which can edit SSIS configuration file paths without BIDS:
http://ssisconfigeditor.codeplex.com/

PJ Lewis said...

Jessica.

I am developing a package which is complete but static as to the connection.

In the connection manager, we defined an expression that uses variables for the connectstring property.

We have a script task that allows the user to enter a database name into the popup window.

The package then tries to redefine the connect string.

However, we are getting the following message:

"[Connection manager "GS_DB_Upgr"] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21."

Any ideas would be greatly appreciated.

Thank you

Jessica M. Moss said...

PJ,

There should be some additional error information being logged that is more descriptive in addition to the error you have listed. If you find that error, try posting your question to http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/threads, so that everyone can benefit from your question.

Jessica