SSIS Manifest Deployment with Package Configs

Question

SSIS Manifest Deployment with Package Configs

nataliehinnen

SSC Veteran

Points: 262
More actions
February 2, 2010 at 2:06 pm

#141075

I have an SSIS package which uses package configurations. My configuration settings for package configs resides in a SQL Server database. I've heard that the manifest deployment utility will allow you to modify these config settings during deployment. It seems it is only possible when the configs reside in an xml config file. I cannot figure out how to do this with configs that reside in a SQL Server database. Does anyone here know if this is possible?
Thanks in advance,
Natalie

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

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

wschampheleer SSCertifiable Points: 5504 More actions · Answer 1

Can you explain in more detail what it is you are trying to do?

Personally, I never use the deployment manifest. I also store my Package Configurations in SQL Server. This is how I deploy to another server:

I copy the table (including its data) with the configurations (e.g. dbo.SSIS Configurations) from the old server to the new one. Then I edit/update this table on the new server and modify the configurations that need to be changed (e.g. connection strings, email subjects etc.). Done.

Regards,

Willem
http://wschampheleer.wordpress.com[/url]

nataliehinnen SSC Veteran Points: 262 More actions · Answer 2

In my organization, we as the developers don't do the deployments to production. Instead we automate them and hand them off to the DBA. I like using the manifest and have always scripted the insert/update statements for the config table. I noticed that the manifest wizard walks you through editing configurations if they exist in the config file, but not if they exist in a sql server table. I was just wondering if anyone has looked into that. It seems it's not possible to edit them in a table through the manifest wizard.

Natalie

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

You don't edit them they any wizard. you modify them using T-SQL Statements. All you are doing is storing the data in a table. you then use a variable to store info about there to locate the info. Like a connection string to the database box.

That way you never have to change anything, other than the environment variable that is in the package, and you can do that at anytime using a text editor, it is located in the XML of the package.

Andrew SQLDBA

ragie Mr or Mrs. 500 Points: 511 More actions · Answer 4

http://www.nigelrivett.net/DTS/SSISConfigurationFile.html

I found this link very useful. It will show you how to make an XML configuration file.

nataliehinnen SSC Veteran Points: 262 More actions · Answer 5

I don't think you understand. I don't want to make an xml configuration file. I am storing my configs in a database. I'm just looking for a more automated way to deploy those config settings inside of a setup step.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 6

Nat,

Not sure what you're aiming for here? 'Generally' (yes, a big generalization) you'd use the DB config as a central repository of configuration/s so that all packages, on any given server, when accessing the config value for (say) DataSource1 use the same connection string etc. So it's a little unclear as to how you'd see the deployment changing the config? In particular, if you changed DataSource1 to point to a 'production' server then all development packages looking at that config would then use the production box not a dev box.

One thing you may want to look at is the Filter that exists on the DB configuration/s, which, if you changed which filter set the package looks at, would allow you to have say Production and Dev filter sets.

Steve.