Indirect Configuration with ConfigType 'Indirect SQL Server' fails

Hi,

I have a package that uses a Configuration of type SQL Server where the property values are held.

This runs successfully using this direct configurations.

When I use an Indirect configuration using an environment variable to point to this SQL Server configuration type the package won't even validate.

The Indirect Configuration is:

[EHC-SQLD-01.].[SSISConfigsDEV];[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

which follows the standard of : db connections, config table, filter

This works by the way on my client but on the dev server the error is:

Error: The connection "[EHC-SQLD-01].SSISConfigsDEV" is not found. This error is thrown by Connections collection when the specific connection element is not found.

I've tried every combination for the env variable using quotes, full computer name etc (I thought it was the hyphens in the name), but can't seem to get it to work.

I've also tried as both user and system env variables but made no difference (which one should we use for SSIS anyway as BOL doesn't state this ?)

Appreciate any help. I'm trying to deploy this on the Dev server for testing.

Thanks

P R W.

P R W,

I have never used the Indirect method for a SQL Server based configuration; but I use a similar approach that involves an env variable and the direct method:

1. Set the SQL server based configuration using the direct method using a connection manager called, Let's say 'Configuration'

2. Create an Env variable based configuration to set the connection string of 'Configuration' connection manager and place it at the very top of the configuration organizer, so it happens before the SQL server based one.

Make sure you create the Env variable wit the proper connection string for ‘Configuration’; and that you close and re-open BIDS so you can see the Env variable in thedropdow list for step 2.

I started using this method a while before knowing about indirect configurations and it has worked fine; maybe that is why I have not looked into the indirect ones.

|||

I'm not sure why you would want to do that.

If you deploy the package to different environments e.g. from dev to producution, you must still go into the package to change the Env variable to amend the connection string as eg. the package will now sit on a different server.

Also, you env variable sits first so the configs used will actually be the configs that run last i.e. the SQL Server based one, so what's the point of having two configs that do the same thing ?

The point of indirect configs is that you don't have to open the package. All you do is amend the Environment variable defined on the OS. This should make package deployment easier and more secure.

The actual problem lies in the fact that the Package does not recognise the Indirect config for the environment variable. Although I have successfully set this up on my client, it is not working for the Dev server.

|||

P R W wrote:

I'm not sure why you would want to do that.

If you deploy the package to different environments e.g. from dev to producution, you must still go into the package to change the Env variable to amend the connection string as eg. the package will now sit on a different server.

I guess I was not clear enough. You DO NOT have to open the package and change the Env Variable name; you have to make sure all servers you want to deploy the solution to have the same env variable (same name); then you need to make sure the connection string in the Env variable is right. That's exactly how the same way the Indirect configurations works; you reference a Env variable and then you just change its value, right?

P R W wrote:

Also, you env variable sits first so the configs used will actually be the configs that run last i.e. the SQL Server based one, so what's the point of having two configs that do the same thing ?

Ok let's try again. First, I don't have 2 configurations doing the same thing. Let me give you an scenario: My package has about 10 component/properties that need to be configured at run time; so I create a SQL Server configuration table with all those entries and define same number of SQL server configurations on every of those properties using the direct method. Since I am using the direct method I am asked to provide connection information to get access to the configuration table; is at that point where I use the 'configuration' connection manager. So far, I have create my 10 configurations based on a SQL Server table; to get access to that configuration table I am using 'Configuration' connection manager; the problem with that is that the connection string of 'configuration' needs to be changed at run time depending on which server/Environment is the package being executed. It is at this point when I create an Env. variable to set the connection string of 'configuration' and place it at the very top. That way on each run SSIS will go to the Env variable; will take the connection string, set it to 'configuration' and then all other sql server based configurations will use the right connection to get access to the table.

Notice that an Env variable with the same name needs to exists on every server your are deploying the solution to. But the same constraint exists using the indirect method. I can tell you more; using the indirect method you are constraint to use only Env variable; with this approach you could use alternatively an XML configuration file; that comes handy when you hit the wall when some weird IT policies that prevent you using Env variables

If you don't believe just give it a try; it will take just a couple of minutes to run a test.

P R W wrote:

The point of indirect configs is that you don't have to open the package. All you do is amend the Environment variable defined on the OS. This should make package deployment easier and more secure.

That is exactly what I am doing; just change a value in a Env variable.

P R W wrote:

The actual problem lies in the fact that the Package does not recognise the Indirect config for the environment variable. Although I have successfully set this up on my client, it is not working for the Dev server.

That is why I started my first post saying that I have not used the indirect method; and that I was offering an alternative approach that has worked for me.

|||

I still don't seem to follow what you are saying, as I'm confused by the terminology you're using.

If I have followed it correctly (and I must admit I had to read it several times through - maybe cos its a Fri and everyone else has gone home )., this is the scenario I have:

This is what I have set in Package Configs (this is my direct SQL Server Config):

Configuration Name : Configuration1

Configuration Type: SQL Server

Configuration String : EHC-SQLD-01.SSISConfigsDEV;[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

(The table [SSIS Configurations DEV] holds all the necessary properties and values).

So you are now saying, create another Package Configuration using Config Type of Environment Variable. ?

Type in a name - any name (lets say 'EnvVarTest') and set the Property of that variable to the Connection string of Configuration1 ?

If I've read that correct, when setting up the Env Variable on the OS, the Env variable name is X and Value 'EnvVarTest'. ?

I'm not sure that is what you are saying though, because you would still need to edit the package when deploying from one machine to another ?

Regards,

P R W.

|||

P R W wrote:

I still don't seem to follow what you are saying, as I'm confused by the terminology you're using.

If I have followed it correctly (and I must admit I had to read it several times through - maybe cos its a Fri and everyone else has gone home )., this is the scenario I have:

This is what I have set in Package Configs (this is my direct SQL Server Config):

Configuration Name : Configuration1

Configuration Type: SQL Server

Configuration String : EHC-SQLD-01.SSISConfigsDEV;[dbo].[SSIS Configurations DEV];pkgLRD CED Import;

(The table [SSIS Configurations DEV] holds all the necessary properties and values).

So you are now saying, create another Package Configuration using Config Type of Environment Variable. ?

Type in a name - any name (lets say 'EnvVarTest') and set the Property of that variable to the Connection string of Configuration1 ?

If I've read that correct, when setting up the Env Variable on the OS, the Env variable name is X and Value 'EnvVarTest'. ?

I'm not sure that is what you are saying though, because you would still need to edit the package when deploying from one machine to another ?

Regards,

P R W.

Sorry if it sounded too confussing.

See if this clarifies it:

http://rafael-salas.blogspot.com/2007/01/ssis-package-configurations-using-sql.html

Otherwise, I will give up

|||

OK, now I followed that. That was useful and solved the problem by using Direct config rather than Indirect Configuration.

I wasn't setting up the Environment Variable value correctly on the OS.

Appreciate the time you've taken to help.

(You really weren't thinking of giving up though were you ).

Still unsure as to why the Indirect Configuration failed to validate when it worked correctly on local machine. Tried all sorts of combinations for the Configuration. Anyway that's for another day.

Thanks.

P R W.

|||

PRW,

Glad you found it helpful. I also took the time to research about the indirect configurations, and I think I understood how the work, thanks to a blog post I found. Basically, the Env variable has to contain the SSIS connection manager to be used, the configuration table name, and the configuration filter. Saying that, it looks like you have to create an Env. variable for every property you want to override at run time; which make me wonder if using plain Env variable based configuration would not give the same results in a more simpler way.

The workaround I described in my blog requires only one Env variable only; then all configuration values are stored in the table.

Here is the link, in case you want to look into that other post hat talks about SQL Server Indirect configurations:

http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx

|||

You don't have to have an Env variable for every property required to by dynamic.

Basically it works in a similar way as you have used Direct configs eg. you setup a SQL Server Config type with table containing all properties required to be dynamic and their values. Then you set this up as Indirect config in Package configs. Then setup an env variable on the OS, that makes a connection to the SQL Server Config type Package config with value:

Servername.DBname;Tablename;Filtervalue;

so as you see very similar to the methodology you have used.

I have actually used it successfully as I said in my local environment and it works well. You don't need to reopen the package and all configs can be set in any of the other config types such as SQL, XML.

|||

P R W wrote:

You don't have to have an Env variable for every property required to by dynamic.

Basically it works in a similar way as you have used Direct configs eg. you setup a SQL Server Config type with table containing all properties required to be dynamic and their values. Then you set this up as Indirect config in Package configs. Then setup an env variable on the OS, that makes a connection to the SQL Server Config type Package config with value:

Servername.DBname;Tablename;Filtervalue;

so as you see very similar to the methodology you have used.

I have actually used it successfully as I said in my local environment and it works well. You don't need to reopen the package and all configs can be set in any of the other config types such as SQL, XML.

I think I am missing something here. If the Environment variable requires 'FilterValue' as part of its value; how can you set multiple values/properties at run time using only one Env variable? Can you provided a list of values for the 'Filtervalue' part?

|||

The Filter Value you provide in the OS Env variable, is used to determine the value held in the column 'ConfigurationFilter' of the SQL Server table that has been defined as the SSIS Configuration.

This table also has cols of :

ConfiguredValue - the actual property/variable value etc.

Package Path - the actual property/variable etc.

Configured Value Type.

The ConfigurationFilter is just a value I believe that determines what SSIS Configuration that the values in the table belong too. eg. I would have a table as below containing my SSIS Configs for a package named 'pkgLRD CED Import'

pkgLRD CED Import Void \Package.Variables[User::VarUnencryptionArguments].Properties[Value] String
pkgLRD CED Import C:Test\ \Package.Variables[User::VarSourceConnection].Properties[Value] String
pkgLRD CED Import C:\pkgLRD CED Import.chk \Package.Properties[CheckpointFileName] String

It made sense to me to use the Package name (since this ideally should be unique) though you could actually use any value. Thus you could hold all the Config values for all Package configs in one central table. The Filter value (in this case package name) therefore determines which configs the package should pick up.

So the OS Env variable would look like:

Servername.Dbname;Configtablename;ConfigFilter(i.e.packagename);

This worked for me although I must admit I haven't tested it for multiple packages yet, only one package.

The problem is as you have sort of stated that you would need one OS Env Variable for every package (though not every property).

This could possibly become a bit of a mess.

You could just have one OS Env variable and not define the Config Filter (haven't tested this though), but then I believe you would have to have a separate table for each package. This maybe a requirement or maybe not.

This is why I actually prefer your solution using the Direct method that you have since explained. It allows me to have one single OS env variable on each Server, and one central table for all SSIS package configs.

Regards,

P R W.

BTW I'm still learning SSIS (5 months in) so don't consider myself any sort of expert.

Indirect Configuration problem

I'm having a problem with indirect configurations on my client's machine.

I've created a package that uses an xml configuration file (.dtsconfig file) that is pointed to by an environment variable. I have this package running on my machine using Windows XP and on my test machine that uses Windows Server 2003. When I set it up on my client's test machine also running Windows Server 2003 the package doesn't recognize the configuration and tries to use the values that it was originally programmed with on my development machine.

I installed the packages the same on both test servers including adding the appropriate environment variables. Is it possible there is something wrong with the environment? I haven't checked the various service pack levels on the two machines. I know that I keep my test machine up to date. I'm not sure about my client. Are there any known issues like this under specific service packs? Or am I just probably missing something glaringly obvious?

Thanks,

Jeff

Having an xml config file point to an environment variable is somewhat redundant, you could just have the environment varialbe as a configuration. Regardless, I've had issues with packages not recognizing new environment variables before. I'd try restarting the machine first.

|||

That was it. Thank you.

Jeff

sql

Indirect Configuration of connection manager

Greetings All;
I am having trouble getting an indirect configuration to work. Can someone offer a hand?

Here's the deal....
I have a package the currently has an OLE DB Connection manager. I have an XML configuration file set up to acquire the connection string. That works fine. I have a package variable that contains the absolute path to the .dtsconfig file.

How can I get the connection manager to read the package variable (which has the path to the *.dtsconfig file) instead of the hard coded value?

Thanks in advance.
I don't think you can use a variable to set a configuration path. Indirect configurations refer to the use of environment variables to hold the path to the configuration.|||Perhap Indirect Configuration is not what I need then. What I am trying to do is set up my package such that the path to the dtsconfig file is dynamic. Ideally I'd like to not have to open up the package up in the designer and modify the path if I need to run the package on another machine (assuming the other machine directory structure is different).

How can I do this?

Thanks.
|||Use the /CONFIG switch on DTEXEC. That lets you override the configuration paths at runtime.|||Thanks for your suggestion. I got the indirection to work by specifying the path to the XML config file in the ENV variable instead of the package variable. My intention was to only have one ENV variable and it would only point to the root directory. All other directories, files, etc. I wanted to create on package startup relative to that root dir. Now I have more ENV variables to babysit. Not very pretty.

Looking at Kirk Haselden's book titled "Microsoft SQL Server 2005 - Integration Services", in chapter 14, page 303 he introduces 10 Configuration Type Identifiers when discussing the Package Configuration Object. One of which is of type IConfigFile which is described as an Indirect XML file configuration.

Can someone explain to me what this is for and how it may be used?

Thanks.
|||I think that is what you have implemented - an env variable that holds the path to the config file.|||Now I'm really confused. If I understand the table of Configuration Type Identifiers referenced in the book, ConfigurationType 2 is described as a ENVVARIABLE. Which is what I thought I implemented. See code below.

What I'm curious about is the ConfigurationType 5, ICONFIGFILE.

Code Snippet

<DTS:Configuration><DTS:Property DTS:Name="ConfigurationType">2</DTS:Property><DTS:Property DTS:Name="ConfigurationString">MIGRATOR_SOURCEDBCONFIG</DTS:Property><DTS:Property DTS:Name="ConfigurationVariable">\Package.Connections[SourceConn].Properties[ConnectionString]</DTS:Property><DTS:Property DTS:Name="ObjectName">SourceConnection</DTS:Property><DTS:Property DTS:Name="DTSID">{3B4BEC8B-04C1-44BA-809A-B734CAB0FD6C}</DTS:Property><DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property></DTS:Configuration>

|||Configurations can come directly from an environment variable, or they can come indirectly from an environment variable. If you want to see examples of each, create a new package in BIDS, right-click in the designer form, and choose configurations. Create a few different configurations (using both direct and indirect setups) and take a look at the XML created in the package.

Indirect Configuration in SSIS

Hi,

I need to configure my package using SSIS Configurations. When I opened SSIS Configuration wizard, press ADD and selected Xml Configuration, I found two options.

One being : Specify configurations directly.

next : config location is stored in env variable.

How to use the second option?[ env variable]. Say I want to configure my connection string for my destination database [ OLEDB Destination Component]. How to do that using second option.

Thanks

Kishan

You first need to create your configuration file so go for option 1.

Once you've created it, create an environment variable whose value is the path and filename of the config file.

Go back into your configuration and edit it to use the environment variable rather than the config file.

And that's it!

In case it helps: SSIS: Indirect configurations ROCK!

-Jamie

|||this link might also help: http://dotnetjunkies.com/WebLog/appeng/archive/2006/05/30/indirectconfigpackagessis.aspx