Hello All,
I have about 160 packages in my system, all using about 20 shared datasources.
When changing one of the datasource's connection (changing the server name), I need to open every one of my packages and press OK to allow the remapping to the new server. (this is something I don't understand: wasn't this the meaning of using a share datasource in the first place what's the advantages in that case )
So, I have heard about Package configuration, and how it supposed to support all of this, But I don't understand something: The configuration is set to a certain package. If I have 2 packages, using the same two (of four) Datasources. Using the configuration wizard from one of the packages, I mark the connection string from the datasource. I need to do the same in the other package will the configuration table hold only one value for that connection If I change the value of the connection string will it affect all the packages using that datasource or only the ones using configuration
Thanks,
Liran
SQL Configuration holding connections to DS
Selva
Thanks Donald, Finally I understand what value the Environment variable expects...
But that doesn't solve the problem I have with dynamically be able to change the server's location where the configuration table is (for changing environment cases).
I am really interested in your input to my following suggested solution:
Every package will hold 2 configurations:
The first is Environment Variable configuration type. The Environment variable will hold the connection string to the server where the configuration table resides, so the selected Target property for this configuration will be the connection string of one of the connection managers.
The second is SQL Server configuration type. The connection to the configuration table will be fixed and use the connection manager who's source is the Environment variable's value.
The configuration table will hold the connection strings to the rest of the connection managers used by the packages.
Of course, every package will use the same configuration table.
Thank you very much.
Liran
Steve Lacey
OK, Thanks, I will use SQL Configuration.
Only one problem, the configuration table's location should also be dynamic.
as I understand, you can achive this by using Environment variable to set the connection string to the table. I tried this, but it doesn't seem to work: I cannot select the properties to export...
Phil Clewes
Yes, but by using the filter property, as I understand, I'll need to :
It seems to be too hard to set and to maintain…
Liran
Edgar Zapata
So, you would have the environment variable config apply it's change to the connection manager's connection string first. Then the sql config would pick up it's data from the table where the new connection string points.
That should work - I'll need to try it myself!
Donald
EddieMu
Data sources in the project are used by connection managers in packages at design-time to get connection string properties. If you update a data source in the project, you must open each package in the project for it to refresh its connection string, then save the package again.
However, in your case, with many packages and many data sources, I expect that configurations are a better solution for you.
When you add the connection string property of a connection manager to a configuration, the current value of the property in the current package is saved to the configuration. In your case it is best to create a SQL configuration in a table - the values of properties will be save in the config table.
Other configurations for other packages can point to the same values in the SQL table.
Now when you change the values in the SQL table, any package configured from this location will use those values. Packages which do not have configurations pointing to the table will not be affected.
There is more information on configuration scenarios in this useful whitepaper:
www.microsoft.com/technet/prodtechnol/sql/2005/mgngssis.mspx
Donald Farmer
Leo Treggiari
>Why can't I have all the connections in a single table while each package using couple of them
I believe that should work - the filter property enables that scenario.
Donald
Mauro Mazzieri
The environment variable must contain a valid configuration string when you select it.
The config string has the structure "ConnectionName":"ConfigurationTableName":"ConfigurationFilter" - you can see the full configuration string in the Package Configurations Organizer after you have created a configuration. You can also see the values of these three elements on the last page of the config wizard when you create or edit a config.
In fact, to create this string, I typically create the configuration first normally. Then I create a copy of the config string and add it to an environment variable. Finally I edit the configuration and redirect it to use the enviroment variable.
Donald
BrianJones-Adroit
I Tried it. Looks like it works, but I have another problem:
I want to use the same configuration table for all the packages. when I configured each package, I set the SQL Server configuration type to Reuse the same table. In the table I inserted a record for every one of my data sources (with the same filter- "Connections").
The problem is that I'm getting errors in design time telling me that certain connections are not found, an error for every one of the connections that exists in the configuration table but not used by the package, but when I try to execute the package, no error occurs.
In my project, for achieving parallelism in loading all the dimensions, I use a single package with the whole 160 run package tasks. When deploying this package to the server and running this package, I get a failure and the reason is the same as I stated before.
Why can't I have all the connections in a single table while each package using couple of them
Thanks,
Liran