Wednesday, July 25, 2012

Formatting DateTime values from another timezone

For a while I've been battling with formatting DateTime values generated in another timezone using .NET's DateTime string formatting capabilities. For instance, I have an XSD datetime value of "2012-07-26T00:00:00+10:00" (generated in Australian Eastern Standard Time), but I'm in a different timezone (Australian Central Standard Time, +09:30). If I parse the XSD datetime value into a .NET DateTime, it automatically gets converted to my timezone: "2012-07-25T23:30:00+09:30". If I then wanted to get just the date value (in the original timezone), I'd actually get the wrong date!!!

Today I finally stumbled across the DateTimeOffset class, thanks to http://geekswithblogs.net/BlackRabbitCoder/archive/2012/03/08/c.net-little-wonders-ndash-the-datetimeoffset-struct.aspx.

Basically this struct preserves the datetime in its original timezone, including offset. So for example (from LINQPad):


So, if you want to work with dates in their original timezone, DateTimeOffset is your friend!! This can be particularly important when you're working with XSD datetimes as you commonly do in BizTalk solutions, and need to format datetime values, but without converting between timezones.

Error importing WSDL file into BizTalk solution

Today I was importing a WSDL file provided by a third party web service into a Visual Studio BizTalk solution using the "Metadata Files" option in the BizTalk WCF Service Consuming Wizard and ran into the following error:

"Unable to add metadata file ... (System.InvalidOperationException) There is an error in XML document (1, 40). (System.Xml.XmlException) Name cannot begine with the '.' character, hexadecimal value 0x00. Line 1, position 40."



I opened the file and took a look at line 1, position 40: looked perfectly fine to me. The only thing I noticed when I opened the file was that the file's encoding property (in the Visual Studio properties window) changed when the file was opened in Visual Studio: from Unicode, to UTF-8 (consistent with the encoding specified in the xml declaration).

I saved the modified file and re-ran the BizTalk WCF Service Consuming Wizard, and the file imported fine. I then re-ran the Wizard again with the original file, and the same error occurred. I also tried the wizard using the "Metadata Exchange" option (to browse directly to the live WSDL) and encountered the same error.

I can only conclude that the original WSDL file was generated in the Unicode encoding, which the BizTalk WCF Service Consuming Wizard didn't like, presumably as it wasn't consistent with the encoding specified in the XML declaration.

Anyway, hope this might help someone out there.

Sunday, June 10, 2012

Simple SharePoint 2010 Workflow Error Notifications


I've been working with SharePoint 2010 workflow for a while now, and I have to say I really enjoy the power it provides to automate a lot of otherwise tedious or error prone manual processes. And it's great to have the flexibility of authoring workflows in either SharePoint Designer or breaking out Visual Studio, depending on requirements and client preferences.

One of the things that's sadly lacking from SharePoint Designer workflows is the ability to satisfactorily handle errors when they occur during a running workflow. Ideally we'd have another container like the "step" and "impersonation step" sequence containers that supported a try-catch style block, where we could catch an error in the catch block and attempt to do something useful - like retrying an action, taking an alternative path in the workflow, or at the very least notifying someone that an error occurred. But we don't. Maybe in the next version.

What you do generally get when an error occurs though is an error log entry written to the nominated Workflow History list. In this case, the Event Type column indicates that the entry represents an error, and the Description column provides the details of the error. Given this, I wanted to see if it was possible to "listen" to the Workflow History list and send a notification email to a set of nominated recipients when an error occurred.

My first thought was to place an error-handling workflow on the Workflow History list itself, which would start whenever a new item was created, detect if the Event Type represented an error, and send an email... But hang on, don't we have Alerts built right into SharePoint?!

So why not create an Alert that's based on the Workflow History list, which sends an email whenever an error item is created? So that's just what I did, and here's how.

First step is to navigate to the Workflow History list in the browser. The easiest way I've found to do this is via the "Lists and Libraries" page in SharePoint Designer - just make sure you have the "Show catalog lists and system objects" option enabled in SPD Options.

Once you've browsed to the Workflow History list, open the "List Settings" page from the "List" tab and create a new calculated Yes/No column named something like Is Error, with a formula of "=OR([Event Type]=0,[Event Type]=10)". This column gives use a simple way to determine if the item represents an error or a normal log entry. Event Types 0 and 10 are the only 2 values I've encountered that seem to represent errors, although there could of course be more.


Save the new column, which returns you to the "List Settings" page.

Next step is to create a new view that provides us with a filtered list of only error items from the "Workflow History" list. Base the new view on the default "All Items" view, with a name like "Workflow Errors". You can add or remove whichever columns you like from the view, although the standard set is a pretty good starting point. The key though is to add a filter, where the Is Error column is equal to "Yes".











You might be wondering why we bothered to create a new column and use it in the view, rather than filtering directly by the Event Type column. Well, that's what I started with, but I found that I wasn't able to create an Alert based on a view that included a filter that used the Event Type column. Don't know why, and eventually I gave up worrying about it.

Save the new view, which again returns you to the "List Settings" page.

We're almost there. The final step is to create the Alert. Browse back to the "Workflow History" list itself, then select the "Alert Me - Set alert on this list" button from the "List" tab. Give the new Alert a meaningful name like "Workflow Errors" and select who will be notified and the delivery method (email or SMS). The most important parts though are to select the "Noew items are added" option for the "Change Type" setting, "Someone changes an item that appears in the following view" option and select your "Workflow Errors" view for the "Send Alerts for These Changes" setting, and the "Send notification immediately" for the "When to Send Alerts" setting.


Save the alert, and you should now be notified whenever an error occurs in your workflow! Of course there's more we could have done, like filtering by which workflow, or for specific errors, but it really depends on how you configure your Workflow History lists, but at least this is a start.

HTH!

Monday, May 28, 2012

SharePoint 2010 & Javascript Woes


A quick one today.

I recently ran into a bizarre issue with SharePoint 2010 lists, where even though I'd configured the list to display its display / edit form as a dialog, it was instead displaying it in a new page (ie, replacing the current page). This however only occurred when I included the list as a web part on another page - not on the main list pages.

I noticed that there was a script error being reported on the page when the link to display / edit the list item was clicked, with an error along the lines of "undefined is null or not an object" in either "core.js" or "inplview.js".

Before I dived into debugging javascript files, I decided to compare the web part properties for a list whose display / edit form was successfully being displayed in a dialog (on the same page) with the web part I was having issues with. The only difference: the web part encountering the javascript error had the following AJAX Options selected:
  • Enable Asynchronous Update
  • Show Manual Refresh Button
When I disabled these, the javascript error went away, and the display / edit form was displayed in a dialog as expected. Fortunately I didn't need either of these options

Tuesday, May 8, 2012

Hyperlinking to an InfoPath form using the XsltListView web part

Recently I was working on a "no-code" SharePoint / InfoPath solution where we had a requirement to create a number of SharePoint views where the link to open the InfoPath form instance wasn't based on the original Name column (which was set when the form was submitted using a combination of username and current date/time), but was instead based on the ID column (assigned by SharePoint). I considered two options:
  1. Utilise a workflow to rename the InfoPath form when it's submitted to the form library, so that its name is based on the ID column, for instance "Form-[ID].xml", where [ID] is the value of the ID column. The views could then remain using the Name column, but the actual value of the Name would be something more useful.
  2. Modify each of the views so that the Name column wasn't displayed, but the ID column was displayed and hyperlinked to the InfoPath form instance.
I evaluated each option, and eventually selected the first option because (for other reasons) we needed the form instance renamed. Implementing this option had its own challenges (workflow context metadata seems to take a while to be updated after you've renamed an item within the workflow) - but isn't the subject of this post. Instead I thought I'd share how I achieved the second option - for my own reference (in case I ever need to do something similar again) and in case it helps anyone else.

To start with I thought I could just remove the Name column and add the ID column to the view, then use SharePoint Designer to add the hyperlink using its "Common Tasks" menu. I opened the view in SharePoint Designer's "Split" view, hovered over the ID column in the "Design" pane until the "Common xsl:value-of Tasks" menu was displayed, and selected the option to "Show Link To Item". Saved the view, clicked the link and... Alas, the browser attempted to download the InfoPath XML file, not open it inside SharePoint. It seems as though the "Show Link To Item" option works OK with standard SharePoint lists, not so much when it's a form library and you want to open the InfoPath form using the SharePoint InfoPath form server. So back to the drawing board.

Next up I noticed that in addition to the standard "Name (linked to item with edit menu)" column there is also a "Name (linked to item)" column that doesn't generate the edit menu, but does include a hyperlink to (correctly) open the InfoPath form. I thought maybe I could copy the XSL stylesheet that is generated for the "Name (linked to item)" column and apply it to the ID column. Here's what I came up with:

<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal" xmlns:o="urn:schemas-microsoft-com:office:office">
  <xsl:include href="/_layouts/xsl/main.xsl"/>
  <xsl:include href="/_layouts/xsl/internal.xsl"/>

  <xsl:param name="AllRows" select="/dsQueryResponse/Rows/Row[$EntityName = '' or (position() >= $FirstRow and position() <= $LastRow)]"/>
  <xsl:param name="dvt_apos">'</xsl:param>

  <xsl:template name="FieldRef_body.ID" ddwrt:dvt_mode="body" match="FieldRef[@Name='ID']" mode="body" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">

    <xsl:param name="thisNode" select="."/>
    <a href="
{$thisNode/@FileRef}" onclick="return DispEx(this,event,'TRUE','FALSE','{$thisNode/@File_x0020_Type.url}','{$thisNode/@File_x0020_Type.progid}','{$XmlDefinition/List/@DefaultItemOpen}','{$thisNode/@HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon}','{$thisNode/@HTML_x0020_File_x0020_Type}','{$thisNode/@serverurl.progid}','{$thisNode/@CheckoutUser.id}','{$Userid}','{$XmlDefinition/List/@ForceCheckout}','{$thisNode/@IsCheckedoutToLocal}','{$thisNode/@PermMask}')" onfocus="OnLink(this)" onmousedown="return VerifyHref(this,event,'{$XmlDefinition/List/@DefaultItemOpen}','{$thisNode/@HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon}','{$thisNode/@serverurl.progid}')"><xsl:value-of select="$thisNode/@*[name()=current()/@Name]" /></a>

  </xsl:template>
</xsl:stylesheet>


The key element seemed to be applying the four attribute values highlighted above (href, onclick, onfocus, onmousedown). Again, I saved the view, clicked the link and... success! The InfoPath form opened correctly!

Great, I thought. Now just a bit of tidy-up, I removed the "Name (linked to item)" column from the view (leaving my nicely hyperlinked ID column) and I was done. Except that the link didn't work any more. Huh?

Long and annoying story short, it seems as though in order for the XSL template above to work, you need to include one of the "linked" columns in the view (such as "Name (linked to item with edit menu)" or "Name (linked to item)"). But... that kind of defeated the purpose!

Eventually however I managed to come upon a solution where the column is included in the view, but an XSL template is applied to the column to hide it. Hence, the complete XSL template becomes:

<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:d="http://schemas.microsoft.com/sharepoint/dsp" version="1.0" exclude-result-prefixes="xsl msxsl ddwrt" xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime" xmlns:asp="http://schemas.microsoft.com/ASPNET/20" xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:SharePoint="Microsoft.SharePoint.WebControls" xmlns:ddwrt2="urn:frontpage:internal" xmlns:o="urn:schemas-microsoft-com:office:office">
  <xsl:include href="/_layouts/xsl/main.xsl"/>
  <xsl:include href="/_layouts/xsl/internal.xsl"/>

  <xsl:param name="AllRows" select="/dsQueryResponse/Rows/Row[$EntityName = '' or (position() >= $FirstRow and position() <= $LastRow)]"/>
  <xsl:param name="dvt_apos">'</xsl:param>

  <xsl:template name="FieldRef_body.ID" ddwrt:dvt_mode="body" match="FieldRef[@Name='ID']" mode="body" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">

    <xsl:param name="thisNode" select="."/>
    <a href="
{$thisNode/@FileRef}" onclick="return DispEx(this,event,'TRUE','FALSE','{$thisNode/@File_x0020_Type.url}','{$thisNode/@File_x0020_Type.progid}','{$XmlDefinition/List/@DefaultItemOpen}','{$thisNode/@HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon}','{$thisNode/@HTML_x0020_File_x0020_Type}','{$thisNode/@serverurl.progid}','{$thisNode/@CheckoutUser.id}','{$Userid}','{$XmlDefinition/List/@ForceCheckout}','{$thisNode/@IsCheckedoutToLocal}','{$thisNode/@PermMask}')" onfocus="OnLink(this)" onmousedown="return VerifyHref(this,event,'{$XmlDefinition/List/@DefaultItemOpen}','{$thisNode/@HTML_x0020_File_x0020_Type.File_x0020_Type.mapcon}','{$thisNode/@serverurl.progid}')"><xsl:value-of select="$thisNode/@*[name()=current()/@Name]" /></a>

  </xsl:template>

  <xsl:template name="FieldRef_header.LinkFilenameNoMenu" ddwrt:dvt_mode="header" match="FieldRef[@Name='LinkFilenameNoMenu']" mode="header" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">
   
  </xsl:template>

  <xsl:template name="FieldRef_printTableCell_EcbAllowed.LinkFilenameNoMenu" match="FieldRef[@Name='LinkFilenameNoMenu']" mode="printTableCellEcbAllowed" ddwrt:dvt_mode="body" ddwrt:ghost="" xmlns:ddwrt2="urn:frontpage:internal">
    <xsl:param name="thisNode" select="."/>
    <xsl:param name="class" />
   
  </xsl:template>

</xsl:stylesheet>


The key here are the two empty templates for the FieldRef[@Name='LinkFilenameNoMenu'] match, which hide the "Name (linked to item)" column.

The final piece was making this stylesheet reusable across a number of views, rather than having to copy/paste it into each view. This is where the <XslLink> element of the XsltListView web part comes in. You simply set the value of this element to the URL of your XSLT file. In my case, I loaded the XSLT file into my Site Assets library, and then included a relative URL to the file: the important thing to note when using a relative URL is that the URL seems to be relative to the location of the view or page the XsltListView web part is in - ie, it's NOT site relative... so you may need a URL like "../SiteAssets/MyTemplate.xsl" if for instance your view or page is located in eg a "SitePages" library.

Anyway, thanks for listening, hope this helps someone!

Wednesday, April 18, 2012

SSIS: BIDS Helper

Another SSIS goodie: http://bidshelper.codeplex.com/. An add-in to Visual Studio (Business Intelligence Development Studio) that provides a host of great tools for working with analysis services, reporting services and SSIS. I've been using quite a few of the tools in my data warehousing work with SSIS such as the Expressions List and Variables Window Extension (much better than manually hacking the pkg file to change a variable's scope!)

HTH!

SSIS: Excel Source - Specifying a Worksheet & Range

Recently I've been spending some of my time working on a data warehousing project. We're building the data warehouse using the complete Microsoft stack: SQL Server 2008 R2 database engine, analysis services, reporting services and integration services for ETL (also throw in some SharePoint 2010 for presentation and BizTalk 2010 for some of the trickier to interact with data sources).

Although a few years back I spent the majority of my time in the BI space I've been concentrating on systems integration for the last few years, so it's been an interesting refresher on BI concepts and the tools available today.

One of the data sources we needed to load data from was an Excel spreadsheet consisting of several worksheets, each worksheet containing similar data for a different year. Unfortunately the top few rows of each worksheet had "header" data that we're not interested in, but which the default Excel data source in SSIS chokes on.

Fortunately, you can specify a worksheet and range using the OpenRowset property: Just specify it in the format [Sheet]$[Start-Cell]:[End-Cell]. So for instance if the worksheet was named "2006" and the data we're interested in was in the cell range A9 through to U300, we would specify a value for the OpenRowset property of "2006$A9:U300". Combine this with the capability to specify a variable-based expression for the OpenRowset property and a ForEach container, and we can loop over each worksheet in the spreadsheet and import the data. How cool is that?!