SQL Server vs. XML Configurations

I have been experimenting with both XML and SQL Server Configurations. Management would really like to go with the SQL Server Configurations, but I seem to be having problems with them.

The main goal is to be able to create a configuration for each connection and have every package refer to its specific configuration, therefore in the future we could change say our SA password and only have to update this in a few places rather than for every package.

In order to experiment with the problem I am having I developed 2 identical packages, except one uses SQL Server Configurations and the other uses XML. The configurations save the exact same properties. With the XML package I can go into the configuration file and change say the password and initial catalogue properties and have these changes go into effect. With the SQL Server package these changes do not seem to take place. So I guess what seems to be happening is with the XML package the connectionstring seems to be generated with the other properties that have been saved in the configuration, but in the SQL Server package none of this seems to take place and the connectionstring just stays exactly as it is saved in the configuration file.

I was wondering if I just seem to be missing something or if anybody else has experienced this problem and found a solution.

Any help is much appreciated,

Sam

Thanks Phil for your reply,

I do have SQL Server configurations and only one configuration defined. Also they all have a different GUID.

The main thing i notice is that the package will not even make the connection to the server. I receive this error message upon executing:

I have 2 connections in the package. One is development.sa and the other is development.ssisconfigurations.sa. I have the configuration set to define the development.sa properties, which I want to use for a number of the packages i create.

My guess is that I am just missing something obvious, but it does not seem like I am.

Sam

Sam,

Configurations based on a SQL Server table work just fine. But there is a slight difference when compared against XML files. As you are telling the package to go to a SQL server table and 'grab' some configuration values; you also would need to provide the connection information to get to that table right . That is why, when creating a SQL Server based configuration the wizard ask you to provide the configuration settings directly (using an existing connection manager in the package) or indirectly (using an Environment variable). It seems to me like you are using the direct method; if so, you need to make sure that connection manager is properly configured at run time (basically it can get connected).

BTW, the password needs to be specified in either of SQL Server or XML file configurations; so yes, you need to add it after creating the configuration.

Rafael,

Thanks for the reply.

I either am not understanding, or I am not communicating very well. Either way I'll give it another shot.

I have the configuration file holding all the properties for my connection to the development server, which is the one I will be using for most of my packages.

The package is able to access the configurations. I know this because when i first set up the configurations and run the package it fails to connect to the server, but then i go into the table and edit the [ConnectionString] property to include "pwd=thepassword" the package is then able to connect to the server and execute successfully. The thing is that I do not want to have to include the "pwd=thepassword" part, I just want to be able to enter the correct password in the [Password] property and have it run successfully.

The reason i want to do it this way is that in the future I want to use a single package and set the [InitialCatalogue] property at the command line in the job step to be able to execute the that package against a number of databases.

I have managed to get this to work just fine using XML configurations and editing those individual properties, but I for some reason am having to edit the [ConnectionString] property with SQL Server configurations to get this to work.

To me it seems that with the XML configurations the [ConnectionString] is generated by pulling the info from the individual properties, but that does not seem to be happening with my SQL Server connection.

I will keep working with it and let you know if I come up with a solution. Also i know how to set up the environment variable for XML files and was curious about how to set it to reference the SQL Server configurations.

Thanks for your replies,

Sam

Sam,

Sorry if it sounded confussing. Meanwhile make sure to read whatis said is some of these links:

sqljunkies.com/WebLog/knight_reign/archive/2004/12/07/5445.aspx

http://support.microsoft.com/kb/918760

Unfortunately I am not going to have a chance to work on this again until Monday. I will probably have to post another question, unless somehow it all of a sudden works.

But anyway, thanks again Rafael and Phil for your input,

Sam

Still haven't found a solution to this problem. It does not seem like anyone has encountered this problem, so I'm wondering if there is some sort of property I do not have set correctly.

Using SQL Server Configurations:

What is happening is that the connectionstring in not being built by the other individual properties in the configuration. So, say i cannot change the [initialcatalog] property and get the package to execute successfully. But when i go in and change the intial catalog in the [connectionstringproperty] to the correct database the package executes successfully.

Using XML Configurations:

Everything takes place correctly and how I want. The connectionstring is being built from the other individual properties so I can change the [InitialCatalog] property, but not change the [connectionstring] property and have the change take effect.

Still boggled, any possible suggestion is appreciated,

Sam

Sam,

Let's try to divide the problem and focus on one piece at the time. If using XML configuration is working fine, let¨s focus on how to use configuration based on a SQL Server table. Could you describe step-by-step how you are using the configuration wizard for that

How are you setting the connection for that configuration Directly or from an environment variable I have the impression that your problem may be on that step.

Thanks for another reply Rafael,

Name of connection I want to set up configurations for is Development.sa. Also I am specifying the configuration settings directly.

Process of setting up configurations:

Select Type: SQL Server

Add new connection to the database where i would like to store the configurations. Connection name is Development.SSISConfigurations.sa.

Configuration Table: [dbo].[SSIS Configurations]

Configuration Filter: dvlp

Next I click the checkbox under Connection Managers with the name Development.sa and just specifying that I want all the properties for that specific connection. These are the only properties for the configuration.

I then assign the configuration a name and click finish.

From here i run the package and it fails to connect so I go into the configuration and change only the [Password] property, but this change will not take effect.

Thanks again,

Sam

Phil,

I am changing the value of [Password] in the database to the correct password. The connectionstring does not contain password in it. When i setup xml configurations the connectionstring also does not contain a password in it, but when i change the [Password] property everything works.

Thanks for your reply,

Sam