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
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
Comments
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
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.
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
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
Thanks.
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
How can we use the config file to get the connection string from there
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?
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
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
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?
A middle configuration file allows to set multiple configurations if needed.
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!
XML is first, SQL is second.
You can name it whatever you would like.
Best,
Jessica
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
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.
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
There is now an utility available in CodePlex which can edit SSIS configuration file paths without BIDS:
http://ssisconfigeditor.codeplex.com/
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
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
I am just reviewing an old SSIS package. It uses indirect configuration, but only 2 levels (the .xml file and the SQL Server tables). In moving through environments, this will get muddy, won't it, as the same .xml file will control all environments?
Thanks...Chris