Hi!

Am I to understand that the only way to specify a path for an xml configuration file for an SSIS package after deployment, is to use an environment variable? This seems silly and strange since there is acutally a page in the ExecutePackageUtility where it seems possible to specify which configuration files to use... Or am I missing something?

If you execute the package using DTEXECUI, there is a Configurations page where you can specify the XML configuration file(s).  If you execute the package using DTEXEC, you can specify like this: /CONFIGFILE "C:\demo\SSIS-XMLConfig\SSIS-XMLConfig.dtsConfig".

I used the UI, and did specify my updated config file, but it did not use this file anyway... I just learned about the deployment utility option when building my package. Could it be that since I did not have a deployment version of the package, it ignored the settings I provided?

Edit:

I just looked at my first post, realised I did say 'after deployment', but by this I actually was referring to my copying the necessary files to the destination server and trying to use them there. Not deploying as in building with the deployment utility activated.

When you execute the package with DTEXECUI select Reporting in the list box on the left, then click the Verbose checkbox.  This gives quite a bit of output when you run the package.  Maybe it will uncover something.

Seems that I now am passed the configuration issue. Selecting to create a deployment manifest, and then using that on the server, allowed me to specify where settings where supposed to be read from, as well as supplying the server settings. Next problem is that apparently we have not got the oledb engine for automating excel installed on servers, so now I have to battle the operations team to get that sorted :P.

Should anyone else be in need of a smiliar solution, this is what I did:

1. Created a xml-configuration with the values I wanted (I choose one config file for each SSIS package).
Tip: If the file does not exist, it is created by the wizard, so all you have to provide in the name of it, and path will be set to your SSIS project folder!

2. Activated the option Create deployment utility, which is in the projet property page under Deployment Utility.
Tip: do not keep this active during development, it can build and increment your manifests and when it has done so 4 or 5 times, it starts to get bugged... (information from a more experienced colleague)

3. Built the project

4. Copied manifest, dtsx files and dependencies (config and excel files in my case) to the server

5. Double-clicked the manifest to run it, followed the guide to install and configure my packages.
Tip: If you have more than one configuration file, pay attention to the drop-down box where the name of the configuration file is displayed - you need to choose each file you want to configure! (I almost misstook this for a regular text field and thought next config file would appear as I stepped to next step in the wizard).

Voilá! All done!

Hi All

 I am  having my configuration in sql server SSIS Configurations table how can i add this to  SQL Server Integration Services (SSIS) Packages

Regards

Alex

Take a look at this tip: http://www.mssqltips.com/tip.asp?tip=1405  To add to existing packages, open the package and go through the same steps as described in the tip.   When specifying that you want a particular property value to be set from the SQL Server configuration you will get a prompt whether to reuse the existing configuration - answer yes. 

Hi raybarley

     i have alredy done this my actual problem is i have created the package , added sql config and then i have imported the package to integration service  msdb folder.here i have to add configuration to run the package  but it accepts only xml config .tell me how to add sql config to imported package in msdb

Regards

ALEX

Sorry I don't understand your question.  I typically deploy packages to SQL Server which means they wind up being stored in the msdb database, in particular in the table [msdb].[dbo].[sysdtspackages90].  You do not need any other configuration to make this work although you could.  When you use the SQL Server table for configuration, you have to make sure that when you deploy your packages (wherever you deploy them) when you run the package it can find the SQL Server database where you have the configuration table.  There is a connection manager in your package that specifies the connection string to the SQL Server database that has the configuration table.

In the tip I pointed you to in my earlier reply, I use the environment variable configuration to specify the connection string to the SQL Server database where the configuration table is.  So all I have to do to deploy is make sure the environment variable gets set properly and anything that's currently running (like SQL Server agent) gets restarted so it can see the environment variable that's been added.  This is a bit of a pain but you only have to do it once.

let me check this

thanks for your lightning replay

Thanks for the post!

Also, there is an utility now available in CodePlex which can modify SSIS Package Configuration file paths without BIDS:
http://ssisconfigeditor.codeplex.com/