Excel Services: I Ticked "Trust Children," Why Doesn't It Work?

At work we have a BI system based on Microsoft technologies: SharePoint 2007/Excel Services as the front end and SQL Analysis Services as the back end.

A couple of colleges did most of the setup for the SharePoint server, including Excel Services. Based on their experience, they have always advised others that the "Trust Children" setting in Trusted File Locations did not work. Therefore, we have always been creating an individual entry for each document library we want to use with Excel Services.

Earlier this year we were setting up a QA environment for the BI system. I had the chance to test the Excel Services settings for myself. What I found was that the setting to "Trust Children" does work, it just doesn't always work right away. Sometimes it might take up to 30 seconds or so before it would take effect.

I guess this short delay explains why they thought the setting didn't work. But at least now we no longer need to create all those entries in Trusted File Locations.

Note: for security best practice, you should still consider carefully whether to enable this setting based on your deployment scenario. Refer to the guidelines in this article.

Running SSIS 2005 Packages Under SSIS 2008

Today I tried to run some SSIS 2005 packages on a machine with only SSIS 2008. I used dtexec.exe from the command line to run them. As it was starting with the first one, I could see some messages about the provider reference being changed from SQLNCLI.1 to SQLNCLI10.

This was quite normal, I thought, as SSIS 2008 was just performing on-the-fly upgrade of the packages to 2008 format before running it. However, as the actual execution started, it failed with an error about SQLNCLI.1 not being registered.

That was odd. Didn't SSIS (dtexec.exe) just tell me that it had upgrade the SQLNCLI.1 references to SQLNCLI10?

After a bit of head-scratching and fiddling, I realised that the problems was in my configuration files. I am using indirect XML configurations for these packages. The configuration files had the complete connection strings, including the Provider, which still referred to SQLNCLI.1.

Once I updated the configuration files to use SQLNCLI10.1, all the packages ran without problems.