Am I missing something here

Question

Am I missing something here

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
July 28, 2010 at 4:57 pm

#223844

I have set up a SSIS Package Configuration within SQL. I followed this procedure:
http://toddchitt.wordpress.com/2008/06/27/ssis_config/
I am not sure how, or what I missed. But I was thinking this would allow me to store multiple, different database strings in the table, for different environments. The only one that is in the table is the one that allows me to connect to that database and the table where the config is stored. That does not make sense to me.
Can someone point me to a good solid page that tells and shows how to setup package configuration using SQL Server? I do like the sound of this one, but I feel it is not exactly what I want.
Thanks
Andrew

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sam Greene SSCertifiable Points: 5252 More actions · Answer 1

You should be storing one environment on one server. When you move to a new server, you get the new values. In order to use different sets you'll need to perhaps pick a different configuration database - have a config db for each environment you have.

http://msdn.microsoft.com/en-us/library/cc895212.aspx

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

That is not true, with SQL Server, I am able to store as many different connection strings in a single table, in a single database.

I am just not sure how to get SSIS to add another string to the existing table. Seems that it wants to truncate the table and then add a single new one.

Thanks

Andrew SQLDBA

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Rather than using configurations, we created our own table to store parameters / configurations for the package. Updating the table was a manual process to input all of the configurable values. We then just ran a tsql query at the beginning of the package to retrieve the values required for the rest of the package run.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 4

Yes, that is what I am after.

One is allowed to to store multiple values in the database?

We have a single SSIS server, and that is all that is on that box. We have a "maintenance" database on each of the servers, in all the environments. I have only one SSISConfig table, on one server, that I would store all the configs for all packages that would go out and touch different databases in each environment.

I am creating a "Package Data Source" in each package, that connects to an XML file that stores the DB Conn String to the SQL table that is storing all the configs that I have for all environments. Each package has a variable that stores an ID value to query the table to select the correct config.

I would assume that would be pretty simple. But I am running into some rather difficult challenges.

Andrew SQLDBA

Sam Greene SSCertifiable Points: 5252 More actions · Answer 5

AndrewSQLDBA (7/29/2010)
That is not true, with SQL Server, I am able to store as many different connection strings in a single table, in a single database.
I am just not sure how to get SSIS to add another string to the existing table. Seems that it wants to truncate the table and then add a single new one.
Thanks
Andrew SQLDBA

You can store many strings in the SSIS configuration table - that's what it's for. It's just normally meant to have a one package to one config set relationship.

What I suggested would be an easy way to switch between them.

I hopped in Visual Studio and poked around a bit to refresh my memory. You can also have multiple tables in the config database and use the Edit... button on the Package configuration organizer to pick the other table which should hold your other environment variables.

You could also use the same table and create filter names for each set of variables. Then organize them in the Package Organizer so that the one you want to take effect is at the bottom of the list. The dialog says "The configurations are listed in the order in which they will be loaded when the package runs".

This is one way to do it - I'm sure there are are others.