MOSS 2007 Sizing Estimation Tool

This might be old news, but HP has released their MOSS 2007 Sizing estimation tool. It’s a quick way to estimate the hardware required to roll out MOSS 2007. Tools can be downloaded at http://h71019.www7.hp.com/ActiveAnswers/cache/548230-0-0-0-121.html. Additional SharePoint tools can be found at  http://h71019.www7.hp.com/ActiveAnswers/cache/70677-0-0-0-121.html#300. Note that a simple registration is required to get to these pages.

Things to take note about SharePoint master pages

Stumbled across this page that note down some useful information about master page in MOSS 2007. Definitely will refer to it once a while when I’m doing customization on MOSS pages.

http://jopx.blogspot.com/2007/09/ten-things-you-should-now-about.html

Custom page in "_layouts" that switches master page depending on site context

Ok, that’s a long title. Let me explain what I’m trying to do here. I have a custom ASP.NET 2.0 pages deployed on the “_layouts” folder. What I want to do is have the master page setting of these custom pages to change according to the SharePoint site context.

For example, viewing http://server/SiteA/_layouts/custom.aspx will use materpage from http://server/SiteA/_catalogs/masterpage/default.master, while viewing http://server/SiteB/_layouts/custom.aspx will use materpage from http://server/SiteB/_catalogs/masterpage/default.master.

Lots of information are already on the web, such as http://www.sharepointblogs.com/dwise/archive/2007/01/08/one-master-to-rule-them-all-two-actually.aspx, http://weblogs.asp.net/soever/archive/2006/11/14/SharePoint-2007_3A00_-using-the-masterpage-from-your-site-in-custom-_5F00_layouts-pages.aspx and http://blogs.msdn.com/bgeoffro/archive/2007/11/12/branding-a-moss-corporate-intranet-portal-part-3-layouts-pages.aspx. The most promising method mentioned are by building a custom httpModule class which can be applied to the whole MOSS web application.

Below is my simplistic take that fits my scenario:

CustomPage.aspx

<%@ Page Language="C#" MasterPageFile="~/_layouts/default.master" AutoEventWireup="true" CodeBehind="CustomPage.aspx.cs" Inherits="CustomMaster. CustomPage" %>

<asp:Content ContentPlaceHolderId="PlaceHolderPageTitle" runat="server">

</asp:Content>

<asp:Content ContentPlaceHolderId="PlaceHolderPageTitleInTitleArea" runat="server">

</asp:Content>

<asp:Content contentplaceholderid="PlaceHolderAdditionalPageHead" runat="server">

</asp:Content>

<asp:Content ContentPlaceHolderId="PlaceHolderMain" runat="server">

</asp:Content>

CustomPage.aspx.cs

public partial class CustomPage : System.Web.UI.Page

{

protected void Page_PreInit(object sender, EventArgs e)

{

Page page = sender as Page;

if (page != null)

{

if (page.MasterPageFile != null)

{

SPWeb mySite = SPContext.Current.Web;

page.MasterPageFile = mySite.CustomMasterUrl.ToString();

}

}

}

}

Now, the master page of my CustomPage will change dynamically depending on the site context. All I need to do now is to make this a base class for my custom application to inherit, and all the pages will have this behavior.

Sorting project items under SSIS projects

For some reason, SSIS projects in VS.NET 2005 will not sort project files, such as dtsx in alphabetical order. As far as I know, there are no built-in function in VS.NET 2005 to sort the items.

Therefore, the workaround is to open the SSIS project file and sort it manually. The SSIS project file is a valid xml document file. This means I can manipulate it in Excel, such as sorting it. However, I’ve opened the project file in notepad and copied part of the xml to a separate xml file to open in Excel, just in case Excel did more than what it should. Make sure the created xml document conform to xml standard.

After opening the xml file in Excel as xml table, perform sorting of the document in whichever order desired (usually A to Z). Once done, save the file, open the file in notepad and copy the changes and replace the project item section of the project file. Now the project items should be sorted accordingly. Do make sure backup is done before attempting this change.

Detecting long running SQL Agent jobs

Typically, you would like SQL Agent jobs to send error notification if it failed for some reason. If there’s no email, the job “should” be running ok, right?

 

Actually, one of the SQL Agent job that I’ve observed actually seems to be stucked half way through in one of the step that executes multiple SSIS packages. One of the package are extracting data from another SQL server via link server. For some reason, the job will not fail even if there’s some connection issue with the link server. The job does not fail in this case, thus no email notification will be sent.

 

Therefore, I have to check which SQL Agent job has run for too long. Fortunately, this has been done by many others. The trick is to make use of some SQL server built-in stored proc to query for the job status and its start time. Compare the start time with current time, coupled with the execution status yields you the long running jobs. The following are the reference that I used to built my own long running job detector.

 

http://www.databasejournal.com/features/mssql/article.php/3500276

http://www.databasejournal.com/features/mssql/article.php/10894_3491201_2

 

To test out the stroed proc, I’ve created a job that executes the sql statement “waitfor delay '1:00:00'” to simulate a job that runs forever.

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:

<configuration>

<location path="_layouts">

<system.web>

<httpModules>

<remove name="PublishingHttpModule" />

</httpModules>

</system.web>

</location>

</configuration>

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: http://support.microsoft.com/kb/933835/