Thursday, October 18, 2007

Custom Application on top of MOSS 2007

One of the old school method of building build custom web application on top of SharePoint Portal is to deploy the web application within SharePoint’s web application. Most oftenly, the _layouts folder is used. I do have to note that this might not be the best way to do it, but it’s the most straight forward way.

With CMS now part of SharePoint in MOSS 2007, there is an additional step required: the PublishingHttpModule need to be explicitly removed from the _layouts folder. If this is not done so, the custom application will not work properly. The web.config file in the wss virtual directory need to include the following settings within the <configuration> section:


<location path="_layouts">



<remove name="PublishingHttpModule" />





SideNote: I’ve almost forgotten about this setting until a colleague of mine bumped into this same issue, which already happened to me earlier this year when I haven’t started blogging yet. I would credit this finding to some resource on the web but unfortunately I don’t recall where I found this info from already. Anyway, belated thanx, whoever your are...

Cannot acquire connection to Excel when executing SSIS package via SQL Agent Job

My current work involves building ETL solution using Microsoft SQL Server SSIS. All has been working well in development until we moved the SSIS packages into production environment (don’t that always happen?).

Anyway, our SSIS packages that has Excel file as data source hits the following problem:

Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009

Eventually, it seems that the issue is due to account privilege. Our packages are executed via SQL Agent Job, in which the job step is configured to Run as a one of the designated AD account, that was setup as the proxy for the job. According to this Microsoft kb article , the particular AD account does not have rights to the Temp folder of SQL Server Agent proxy account. It would be logical to assume that this temp folder is used as a working folder by the SQL Agent job if required, such as putting the Excel file there for execution perhaps?

We solve this issue by using the default Run as account, which is SQL Agent Service Account. This Microsoft KB article has the detail: