Package Configuration in SQL

I have an OLEDB SQL Connection that has a connection string that is set as and Expression using a User Variable (@.[User::server_name]). I pass the server name as a command line option. The Package Configuration table is being accessed using the setting for User::server_name in the package and not using the parameter entry for it.
The command being issued is similar to:

dtexec /FILE "packagename.dtsx" /SET "\package.variables[server_name]";dbserver

I am wondering if this by design? I would think that all parameter variables would be set prior to any other configuration entries are processed.

Thanks,
MikeHi Mike,

Do you get any warnings or errors with the command line you specified? Is there more than one variable called server_name with a different namespace or at different scope? You can also add the /rep v option and see if there is any other information that might be useful to determine what is happening.

Thanks,
Matt|||Here is the error I was getting:

Started: 11:27:03 AM
Warning: 2005-12-13 11:27:06.54
Code: 0x8002F304
Source: packagename
Description: A problem occurred with the following messages: "Invalid object
name 'dbo.SSISConfigurations'.".
End Warning
Warning: 2005-12-13 11:27:06.54
Code: 0x80012059
Source: packagename
Description: Failed to load at least one of the configuration entries for the
package. Check configurations entries and previous warnings to see descriptions
of which configuration failed.
End Warning

The table dbo.SSISConfigurations does not exist on the server I had set in the package, but does on the server I am passing as a parameter. This was done specifically to test for this type of issue, where when deployed to a client that does not have the server configured in the package.

Thanks,
Mike|||It looks to me that you try to apply configuration from a server that you want to be specified at runtime.

The problem is that configuration happens at load time and before any set from the command line.

The way I see it is to have define din the package a configuration that would set the desired server name in your variable and this configuration is applied before any other configuration.

HTH,
Ovidiu Burlacu|||Thanks for the feedback. You confirmed what I suspected about the order of evaluation between Configuration and Parameters.

I have attempted to run dtexec with /ConfigFile parameter but it errors with unable to locate the configuration file. I put the full path of the configuration file and it still errors:

dtexec /FILE "C:\SSIS\Package.dtsx" /CONFIGFILE "C:\SSIS\TestConfig.dtsConfig" /MAXCONCURRENT 1 /CHECKPOINTING OFF

I am running on IA64 system, so I tried both 32bit and 64bit dtexec programs and they error the same. So not sure where to go from here.

Thanks,
Mike|||The solution you are trying will not work and the reason is the sequence of configurations:
1. Configurations defined in the package
2. Configurations specified in the command line
You need to open the package in the designer and specify the configuration for your variable in the package and make sure that this is the first configuration in the list.

HTH,
Ovidiu|||

I have tried in the designer to set the configuration file, but it has a fully qualified path to the configuration file. So when deployed, it will not match correctly on a that system.

I would think that the system should process all command line parameters prior to loading any configurations. Maybe I should submit this as a wish list item. I just don't see how to override on a deployed system. This seems a lot like the Chicken and the Egg...

Thanks,
Mike

|||Since you can have multiple configurations in a you should have one configuration (the first one) that loads the server name from something that can be setup on each computer that will run packages (this could be an environment variable or a configuration file that will always be in a fixed location). Then the second configuration would be able to work because the first one would be able to already configure the server name.

What you want is problematic because it implies interaction in loading configurations from the utility that loads the package. If this was done then anyone could easily circumvent configuration loading by not requesting the package to load up its configurations. While you could argue the same thing is happening now just by the package not being able to find the configuration it is actually different because in the first case there would be no warning informing you that the configuration was not applies, whereas in the second there is always a warning and therefore you are aware of the problem as opposed to the package just not running and you having no idea why. All this being said please feel free to submit this as a wish list item, I was just trying to give you insight as to why it is the way it is.

Thanks,
Matt