How to configure DTS to use MS SQL Server as its repository

What do I need to do to have my Delivery Tier Services use MS SQL Server rather than Derby?

I can see that there are two “.sample” files under \Deployment\Server\conf\perc that are probably responsible for everything:

  • perc-datasource.xml

If I had to guess, I would:

  • create an empty database in SQL Server
  • put the relevant connection credentials into
  • not change anything in perc-datasource.xml
  • rename the two files to remove the .sample suffix (possibly move them up to conf, or the WEB-INF directory of my deployed site?)
  • restart CM1 (the DTS under the \Percussion root)
  • Publish, and hope that CM1 just does *stuff* in the empty database

I expecting I’m missing some pretty crucial steps here… :slight_smile:


You are correct on all counts … including the missing one critical step part (unfortunately the fault lies with us on this one). So, basically you create an empty database in SQL Server, follow the guidelines in the comments of the sample properties file so that your file looks something like this:

(Note that your DB password will become encrypted on server startup.)

Now, don’t move the files, but (and this is the one critical missing piece) along with removing the “.sample” portion of the filename, you will also need to add an “s” after “datasource” so that the filenames are:

This was an oversight in this release, and will be corrected in our next release. But yes, once this is all setup, simply stopping then starting CM1 (and in so doing, your DTS server) will cause your DTS server to connect to the database specified and create all of the needed tables, etc.

Let me know if you run into any issues setting this up.</db_password></db_username></database_name></host_name>

Some notes on this:

  1. I believe the jdbc string should have all the ‘:’ characters escaped with a \
  2. don’t put £-signs in your database user password because the DTS won’t be able to log in;
  3. The useful error messages in this case were in \Deployment\Server\logs\feeds.log

Otherwise. All good so far!

Oliver, great tips, thanks for sharing! Yes, the feeds log file is essential in troubleshooting this configuration – I was surprised to re-read my message and realize I hadn’t pointed that out.

Before I do this, I have a question.
During install I created a SQL db tied to the installation. Is this a separate animal altogether and do I continue to do what this post says? If it is separate, what’s the difference and usage?
And, yes, that was two questions. See how I am.

Hey Lloyd,

Yes you will need a separate database for the DTS Tomcat server. Because I know that you have everything (IIS, DTS, CM1, SQL Server) running on the same box, I imagine you will just want to create a new database on the same instance of SQL Server that houses your CM1 database, and point your DTS server to that.

Tables are not being created and the feeds log shows multiple errors including this one : ‘WARNING: Could not load properties from URL [file:C:/Percussion/Deployment/Server/conf/perc/]: C:\Percussion\Deployment\Server\conf\perc\ (The system cannot find the file specified)’.
The file is in the structure.
For reference: SQL Server is on a different box

Hey Lloyd,

In my response to Oliver, I mentioned a known issue where, in addition to removing the “.sample” portion of the file name, you also need to add an “s” before the file extension. Let me know if you’ve already done this. (Definite tripping point, I know.)


Yes I have. [No tripping this time.]


In that case I will reach out to you through our existing support ticket so we can take a closer look into what’s going wrong here. Please check your inbox shortly.

To tie off the loop here, we found that the cause of the original issue was that Lloyd’s server was using the default Windows Server configuration where it hides file extension names, so the when the properties file appeared to him as"", in reality “” was sitting on the filesystem. This, of course, led to this error:

WARNING: Could not load properties from URL [file:C:/Percussion/Deployment/Server/conf/perc/]: C:\Percussion\Deployment\Server\conf\perc\ (The system cannot find the file specified)'.

Correcting the file name resolved the first problem, but next we encountered this error:

At this point I asked Lloyd to run a command to see if his SQL Server instance was running on the default port 1433. Lloyd discovered that it was not, and so he updated his database connection URL in his with the correct port, and at that point everything started up fully.


I tried this on my setup and it’s not working. I’m getting a lot of “Completed with Failures” in percussion after making the change, restarting DTS and then doing a full site publish.

Hey Matt,

Please take a look in your feeds.log file located at {dts_root}\Deployment\Server\logs to see if the DTS Tomcat is successfully connecting to your specified database. Sounds like it may not be. Also, please View Details on these failed publishes and let me know what the exact error is, in case this may be something else. Thanks!

I’ve not been able to open the file because it says it’s too big for Notepad++, WordPad, or Notepad to open. Any suggestions???

When I looked at the items that are marked “Failed” - it shows nothing for an error message.

When I look at the items that are marked “Completed with failures” - it’s due to metadata which I deduced is from the DTS not working.


Try deleting the file and then stop then start your DTS instance. This will recreate the log file. I’m guessing the publish operation might have caused the log file to explode on you.

As to your metadata errors, can you share the full error? There are a number of causes for this type of error, so I want to know which variety we’re dealing with.

Error for metadataEndpoint: perc-metadata-services/indexer/entry, Item: 23,523,535,886,971, jobId: 12,006, location: /site/academics/graduateschool/faculty-staff, contentType: text/html;charset=UTF-8, length: 36,735 caused by unknown error: Unable to connect to delivery server at: https://XX.X.X.XX:8443.

Okay. Wow. I found out what the issue was. I left the checkbox checked for user must change their password on the account I created in MSSQL for the db. Oops. Thanks for pointing me to the log file.

Hah, that would do it! Nice work rooting that one out, Matt.

Nathaniel, that link goes to a 404 Page, "Oops!!’ page. Got a new link for it?

Hi Paul,

Here is the updated link:…