Where is the dtsconfig file being stored in the SQL server?

Question

I know I can find the packages (XML) under msdb.dbo.sysssispackages, but where is the dtsconfig file?

Thank you

---

Edited: I used BIDS to create the SSIS and the XML config. After I built the project, it created a manifest file under deploy/bin. After I clicked it, I selected the db server as the destination, then it deployed the SSIS and deployed the dtsconfig for me. I can find the SSIS under msdb.dbo.sysssispackages, but can't find the dtsconfig file.

It can't be still under my local file system, thats not quite a deployment, won't it?

Solution 1

OK, after a few testing, I think I have figured out how it works in the manifest deployment.

When you click the manifest file, it will ask for the deployment destination.

You can choose a remote SQL server, no problem, the SSIS packages will be deployed into the database (by default under master, msdb.dbo.sysssispackages, unless the MsDtsSrvr.ini.xml file in the remote SQL server is modified).

But if your SSIS packages use any dtsconfig file, the manifest deployment is kinda silly.

First, it tries to be smart by asking you a new system file path for it to copy your local dtsconfig file over. Yes, the dtsconfig file WILL NOT be stored together with your SSIS packages, even you chose to deploy your SSIS packages to the database. It MUST be stored under system file directory. My default is C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages[source folder name].

The manifest deployment is smart because be your SSIS packages configured to use a dtsconfig file from any path (local or remote), it can update your SSIS packages by replacing the old path to this new path you just specified. But it is not so smart because if you use the default (i.e. C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Packages\MySSISProject), this path means YOUR C:\, not the C:\ in the remote SQL server (even you had specified to deploy onto a remote SQL server in the first step). But at the same time, your SSIS packages deployed in the remote SQL server is being updated to this file path, which essentially point to the C:\ IN THE REMOTE SQL server...

At last resort, you will have to use remote desktop to actually copy your dtsconfig file over to the remote SQL server system file directory.

Solution 2

The configuration file (XML) is created by you by specifying to use an XML file for configuration. You can read an extensive article on how to set this up here: http://www.simple-talk.com/sql/ssis/xml-configuration-files-in-sql-server-integration-services/

So, in essence, the "where" for your dtsconfig file is where you want to store it, be it file system or a database somewhere.

Edit:

To be clear: Even if you deploy your package to an SQL Server destination, your package configuration will not necessarily be deployed in the same fashion. Check again when setting up package configuration, especially on the 13th step with "Select Configuration Type". Here you will specify if you want your configuration as an XML file, or as rows in a SSIS-Config-style table in a database somewhere. See here for instructions on how to set up using a database for configuration instead of files. http://www.mssqltips.com/sqlservertip/1405/sql-server-integration-services-ssis-package-configuration/

Comments

[user1589188]

Thank you, but thats not I want to know. May be I was not clear enough. Please check my updates.

[cairnz]

As there is many ways of using an XML Config file for your packages, you'll have to provide more details on what you actually did, how you set up the configuration file, and looking at the output from the deployment wizard for your package would be helpful.

[cairnz]

See my edited answer, and look at your package configuration wizard again. You dont have to use a file. Also you should check out BIDSHelper - this utility, in addition to other nice things, will help you in regards to deployment, both towards deploying to file systems and to SQL Server destination.

[user1589188]

You are talking about the config setup (to start with the DB), while I am asking about the config deploy (to start with file), which are totally different things. And for BIDSHelper, it is not so helping at all. See bidshelper.codeplex.com/…, at the very end Limitations: SSIS Package Configurations are not deployed. No dtsConfig files can be deployed using BIDS Helper.