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?
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.
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.
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.
|||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 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.
|||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. 
No comments:
Post a Comment