softlogger Latest Articles ::BizTalk

patterns and practices Complete Catalog

J.D. Meiers Blog — 2008-03-04T00:00:00

What's the full patterns & practices catalog? I created a quick index of the patterns & practices catalog since I've needed to hunt down a few things. I figured this might be useful to share.

Views

Blocks

Enterprise Library

Enterprise Library

Factories

Guides

Patterns

Tools

Interactive BizTalk Capabilities Poster

Jon Flanders Blog — 2008-02-19T00:00:00

Check it out - http://www.microsoft.com/biztalk/capabilities/reader/ Built with Silverlight (by little ol me ;-)). I think its the first time I've built something that went up on microsoft.com for sure.

Check out my BizTalk R2 Training.

BizTalk 2006, WCF (basicHttpBinding), Adding Web Reference will go in end less loop.

Saravana Kumar in Biztalk — 2008-01-05T00:00:00

When I tried to add a "Web Reference" to a WCF service (basicHttpBinding), I encountered following behaviors

1. Either it will throw an exception showing "Failed to add Web Reference" and no more clue anywhere, OR

2. Message saying "This page is accessing information that is not under its control. This poses a security risk. Do you want to continue?" it wont wait for your reply, it will go on endless loop until you click "NO" (then you need to close some 50 or windows), OR

3. It will throw an exception message saying, "Could not generate BizTalk files. Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index"

I attached following screen shots for reference:

(OR)

Tweaking the WCF config file with different setting didn't give me any luck. At last I figured out the issue is due to the namespace declaration for the WCF service. Just removing the namespace declaration and defaulting it to use "http://tempuri.org/" (which is not the best practice) solved the problem.

Before:

[ServiceContract(Name = "BackOfficeServices", Namespace = http://companyurl/Service/BackOffice)]

After:

[ServiceContract(Name = "BackOfficeServices"]

I can't really justify the reason, but I guess it will help someone.

Nandri!

Saravana

Detect CLR version under which your BizTalk service is running.

Saravana Kumar in Biztalk — 2008-01-05T00:00:00

It's becoming more and more eminent to understand under which CLR version (1.0, 1.1 or 2.0) the BizTalk runtime, In-Process host is running. I've explained in my previous post the various factors that influence loading appropriate CLR versions and also how you can make use of the config file to force a specific version of CLR is always loaded.

There is one more query raised, what happens if you install .NET Framework 3.0 on your BizTalk server (both 2004 and 2006)? The effect of installing .NET 3.0 doesn't introduce that much behavior difference when compared to installing .NET 2.0 on a BizTalk 2004 machine (built using .NET 1.1 with CLR 1.0). Because .NET 3.0 is still based on .NET 2.0 and shares the same compilers and Common Language Runtime (CLR 2.0). See the following posts from Somasegar and Jason Zanders (comments are more interesting on the both the posts)to see the effect of .NET 3.0.

With all this confusions around different versions of .NET and different versions of BizTalk and different Service Packs, there will certainly be a situation where developers and administrators need to figure out the version of CLR under which the BizTalk Runtime is running. This is where Process Explorer comes to our rescue.

Process explorer's top window shows a list of currently active processes, and the bottom windows shows either the handles opened by the selected process or it shows the DLL's and memory mapped files that the selected process has loaded. You need to set the appropriate mode from the menu item "View-> Lower Pane View". In our case we need to figure out the version of Common Language Runtime core dll (mscorwks.dll) loaded into the BizTalk runtime process (BtsNtSvc.exe). So, we'll set the "Lower Pane View" to DLL's (or Ctrl + D).

Also, make sure the following columns are visible Description, Image Path, and Version. To do so, click on "View" menu and select "Select Columns" and in the "Process Image" tab select appropriate columns.

The following screen shot is taken from a machine running BizTalk 2006. You can see clearly from the bottom pane the version of CLR running is 2.0 and physical dll is loaded from the location "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\mscorwks.dll". BTW, .NET 3.0 is installed in this particular machine, which also proves .NET 3.0 still uses CLR 2.0. In BizTalk 2006 box its quite straight forward it's all .NET 2.0. Things will become more interesting in BizTalk 2004 scenario.

Nandri!

Saravana

Implement Caching for your BizTalk applications using "static" classes and methods.

Saravana Kumar in Biztalk — 2008-01-05T00:00:00

UPDATED: 24th August 2007, to reflect Richard Seroter's comment

There is no necessity to explain the importance of caching in any server based developments like BizTalk, ASP .NET etc. You need to plan early in your development cycle to cache resources in order to make most out of your limited server resources.

In BizTalk applications, it's quite common to lookup a data store to pickup a value, at different stages like custom Adapters, Pipelines, Orchestration, etc. The data store could be a custom SQL database/table, a XML File, Active directory etc etc. Whatever the data store is, if you are doing the lookup for every message you process without any caching, its going to be an expensive and useless operation.

Due to the nature of BizTalk architecture and behavior of .NET run-time, its very easy to implement a caching logic just with a static class and a static method as shown below:

public static class CacheHelper
    {
        private static Dictionary<string, string> _authorIds = null;
        public static string GetAuthorName(string authorId)
        {
            if (_authorIds == null)
            {
                Debug.WriteLine("Cache is empty. So populating the cache...");
                _authorIds = new Dictionary<string, string>();
                lock (_authorIds)
                {
                    _authorIds.Add("2FC0CF1D-E107", "Matthew Johnstone");
                    _authorIds.Add("71F5C860-80FA", "Carl Reynolds");
                    _authorIds.Add("158FF294-1A89", "Robert Perkins");
                    _authorIds.Add("A71AE681-9C39", "Michael Killey");
                    _authorIds.Add("58794661-A9A3", "Saravana Kumar");
                }
            }
            else
            {
                Debug.WriteLine("Cache list is pre-populated. Value is going to be taken from the cache.");
            }
            string authName = _authorIds[authorId];
            if (authName != string.Empty)
                return authName;
            else
                throw new Exception("Author cannot be found with id :" + authorId);
        }
    }

In .NET static variables are maintained per Common Language Runtime (CLR) "AppDomain". We can just exploit this behavior of .NET for our caching needs.

Inside the BizTalk server host instances, several subservices will be running. The exact services can be found in the adm_HostInstance_SubServices table in the BizTalk Server Management database. The services are listed here for reference:

Service	Description
Caching	Used internally by BizTalk Server to cache configuration information for the other services
Endpoint Manager	Responsible for hosting receive and send ports including adapters and pipelines
Tracking	Responsible for moving data out of the MessageBox database and into the BAM or Tracking databases as appropriate
XLANG/s	Host engine for BizTalk Server orchestrations
MSMQT	MSMQT adapter service; serves as a replacement for the MSMQ protocol when interacting with BizTalk Server

The BizTalk host instance simply acts as a container to host these other services. When the service is started, each of these subservices is started as well. They handle all of the processing related to the messaging and orchestration engines in BizTalk Server. For isolated hosts, the Endpoint Manager is the only service that is loaded into the process. Isolated hosts are only intended to be used for hosting adapters that send and/or receive messages.

So, inside the BizTalk host instance by default there will be one .NET "AppDomain" created per each BizTalk SubService (EPM, XLANG, etc). Majority of our custom code will be running under the "AppDomains" either created for EPM (if you are doing any messaging related stuff, example custom pipeline components and any assemblies you reference) or XLANG (any custom orchestration and any assemblies you reference). Both the "AppDomains" will be hosted inside the BizTalk host instance.

As I mentioned earlier, in .NET static variables are maintained per Common Language Runtime (CLR) "AppDomain". Any static data you hold lives inside the "AppDomain" for the life time of the "AppDomain", in BizTalk term that means from the time BizTalk Host Instance is started, corresponding DLL's (pipeline components, pipelines, orchestrations, in general any custom assembly like schemas, maps, helper classes) are loaded (assemblies get loaded into "AppDomain" on demand) into the "AppDomains" (EPM, XLANG, etc) to the time BizTalk host instance is restarted.

We can utilize this behavior of .NET run-time, to create a pattern to cache values, which doesn't change very often. Lets see the behavior with an example.

Here is an example using Orchestration:

In the above code snippet (showed earlier), I just added few Name-Value pair items to "authorsIds" collection, in real world scenarios we might need to populate them from a data store like SQL database. If we are doing that database lookup for every message, then its going to be unnecessary roundtrip's to the database and wastage of server resource.

This utility class can now be used in any of your custom BizTalk solution like Adapters, Pipelines, Orchestrations etc. The following Orchestration shows its usage.

The message assignment shape got the following lines of code.

MSG_AUTHOR_OUT = MSG_AUTHOR_IN;
MSG_AUTHOR_OUT.Name = Utility.CacheHelper.GetAuthorName(MSG_AUTHOR_IN.Id);

When I Build, Deploy and run the sample, for the first message the DebugView showed the following output:

For subsequent messages (I posted 4), DebugView showed the following output:

You can make sure the cache is repopulating by restarting the BizTalk host instance (NT Service).

NOTE: If you are using isolated adapters like HTTP/SOAP the data will be cached under the IIS worker process, so to reset the cache you need to reset IIS.

This technique is simple to implement and easy to use. If you want more advanced caching like cache expiration, dependency etc then you can consider using Enterprise Library caching application block.

Download the full sample here.

Nandri!

Saravana

Orchestration - Handle SOAP Exception and deal with Suspended (resumable) messages

Saravana Kumar in Biztalk — 2008-01-05T00:00:00

The main reason for this blog post is due to this newsgroup post

Download Sample from here.

Step #1: Catching SOAP Exception:

Whenever you call a Web service it's a good practice to handle the SOAP exception specifically rather than just leaving the orchestration to throw an un-handled exception which will result in orchestration service instance and all the referenced message instances being suspended. As shown in the following Orchestration diagram, handling a SOAP exception is straight forward. Place your "Send" shape inside a "Scope" and add an Exception Handler with "Exception Object Type" configured to System.Web.Services.Protocols.SoapException (You need to add reference to "System.Web.Services"). The "Message Assignment" shape inside the catch block got this simple statement

SOAP_EXCEPTION_MESSAGE = soapException.Detail.InnerXml;

Where SOAP_EXCEPTION_MESSAGE is an Orchestration message of type System.String and soapException is the "Exception object name" we assigned for the exception handler. So, with the following setup, whenever there is an exception a message similar to the one shown below will be generated and send across the wire with configured adapter (FILE, SMTP etc).

<string><ns0:NACK Type="NACK" xmlns:ns0="http://schema.microsoft.com/BizTalk/2003/NACKMessage.xsd"><NAckID>{2DA185D7-40E7-4279-9F6E-ADCEB6EF599B}</NAckID><ErrorCode>0xc0c01f07</ErrorCode><ErrorCategory>0</ErrorCategory><ErrorDescription>SoapException: System.Web.Services.Protocols.SoapException: Purposely raised this exception for testing at CustomerProcessing.Get500InternalSoapException(String yourName) </ErrorDescription></ns0:NACK></string>

Step #2: suspended(resumable) messages:

Even though you handled the SOAP exception using an exception handler in STEP #1, it's only going to complete the orchestration gracefully but it's going to leave the original request message you posted to the web services in a suspended(resumable) state. Bear in mind the SOAP exception will be raised inside the orchestration only after the send port has exhausted all the retries. During this period the messaging instance and orchestration service instance will be in the dehydrated state as shown below.

After all the retries, the SOAP exception will be raised inside the orchestration and corresponding SOAP request message will be suspended with resumable state as shown below. This will help the administrator to either resume or terminate the message later.

But at some scenario we don't wont to leave the suspended (resumable) messages in the messagebox, instead move them to a different location (either to file system location or a remote server via HTTP etc, etc). Before BizTalk 2006 the only way to archive this behavior is by writing some WMI scripts, which is OK but doesn't really fit with the message flow infrastructure. But with BizTalk 2006 we can take advantage of "Failed Message routing" capability and make this task seamless.

All you have to do is, on your SOAP send port check the check box "Enable routing for failed messages" (as shown in the following figure) and also you can set optionally the "Retry Count" value to "0" if you want to raise the SOAP exception straight away.

Then configure a "Send Port" (or an orchestration, if you want to do more business logic with the suspended messages) with following filter condition

ErrorReport.ErrorType == "FailedMessage"

ErrorReport.OutboundTransportLocation == http://localhost:47653/SOAPExceptionHandler.WebService/CustomerProcessing.asmx

The above filter condition will make sure you are dealing only with SOAP exception raised due to messages posted to that particular web service (CustomerProcessing.asmx). There are quite a bit of promoted properties that comes along with "ErrorReport" you can take advantage of to cater your situation as listed below.

Description
ErrorType
FailureCategory
FailureCode
InboundTransportLocation
MessageType
OutboundTransportLocation
ReceivePortName
RoutingFailureReportID
SendPortName

Pipeline Component, XmlSerialization and Performance

Saravana Kumar in Biztalk — 2008-01-05T00:00:00

I'm basically replying to Yossi's post here. I tried to leave it as a comment in his post but for some reasons my comments are not getting posted in his blog.

Myself and Yossi used to work together in a big public sector Healthcare BizTalk project for nearly 1.5 years.

First off all I need to thank Yossi for his complement about my white paper "Understanding Design-Time Properties for Custom Pipeline Components in BizTalk Server". Yossi mentioned in his post about the potential performance problem in using XmlSerializer inside pipeline component. Until now I thought his point is valid, but after reading the very first chapter from Professional BizTalk Server 2006 by (DJ, KS and EF) , where Darren explains about Serialization and performance hit, its clear there is NO PERFORMANCE HIT by using XmlSerialization in a BizTalk solution.

I thought the explanation will be helpful for readers to make the decision. The following extract is from the book (Page 11):

"When you first use the XmlSerializer in your application against a given type, a dynamic class is created and compiled on the fly to represent the serialized class. This has an obvious performance over head, but it will be cached for susbsequent requests

This cache is maintained per AppDomain, which is fine for applications like BizTalk, as there is by default only one AppDomain per BizTalk host.
".

So, eventually by using XmlSerialization inside the pipeline component, you'll hit the penalty only once during the lifetime of the component. That's until some one restarts the host.

Nandri!,
Saravana

BizTalk MQSC Adapter

Connected Systems Chilled Out Blog — 2008-01-03T00:00:00

Previously, server based BizTalk adapter was used to access MQ Series and that requires MQ series server on Windows as intermediate server between BizTalk Server and non-Windows Queue Managers. In some situation, when installing the MQAgent to Windows MQ Series box is not an option the client based BizTalk adapter (MQSC) is needed to achieve the same goal.

Go and have a read here: BizTalk MQSC Adapter

Forms Authentication in Sharepoint

Connected Systems Chilled Out Blog — 2008-01-03T00:00:00

Explore several new features for authentication and authorization in Microsoft Office SharePoint Server (MOSS) 2007 and Windows SharePoint Services 3.0 that make it easier to develop and deploy solutions in Internet-facing and extranet environments.

Have a look at these great articles by Steve Peschka of Microsoft

Forms Authentication in SharePoint Products and Technologies (Part 1): Introduction
Forms Authentication in SharePoint Products and Technologies (Part 2): Membership and Role Provider Samples
Forms Authentication in SharePoint Products and Technologies (Part 3): Forms Authentication vs. Windows Authentication

Integrating Excel (Services) and PowerPoint with the Microsoft SDK for Open XML Formats

Microsoft Excel 2007 (nee Excel 12) — 2007-12-24T00:00:00

Today's author: Danny Khen, a program manager who works on both the Excel and Excel Services teams.

What is this about?

Office 2007 introduced new default file formats – Open XML. Excel, Word and PowerPoint take advantage of them. One of the nicest things about those file formats is that they enable easy manipulation of Office documents without needing the Office applications up and running – by simply modifying XML “parts” within the files. In June 2007 we published an SDK that facilitates this process.

On an entirely unrelated subject (not really… wait for it…), Microsoft Office SharePoint Server 2007 introduced Excel Services – a true server-side, enterprise-grade version of Excel, that enables robust and scalable calculation of Excel workbooks on the server, displaying interactive, data-connected Excel reports in a browser, and controlling proprietary information while still sharing Excel-based results to a wide audience. Excel Services expose a SOAP-based web service, which lets developers create custom solutions that use Excel models as part of their logic – helping cut IT costs by letting the subject-matter experts maintain those models on an ongoing basis, without needing a developer to change code.

All of this lends itself to a variety of interesting integrations between Office applications and server-side Excel models. I’d like to show you how you could create a useful solution this way.

The “UpdateChart” application

Imagine that you regularly create and maintain Word documents or PowerPoint slide decks, which incorporate charts with data that needs to be updated. This is common, for example, in the investment industry, where portfolio managers and analysts present their results, proposals or analyses this way to their customers. The data charts would often be created in Excel. The analyst would have an Excel workbook, with all sorts of data aggregations and calculations, most of which are not to be exposed to customers – they represent the investment house’s intellectual property. So the analyst updates and refreshes the Excel workbook, then copies the charts and pastes them into the final pitch book, which is consequently handed to the customer.

Another common case is budgeting or other what-if scenarios. For example, you prepare a sales projection, and you use an Excel model to calculate your projected sales. You then want the results in the form of a chart in a PowerPoint presentation.

How cool would it be if all of this could be automated? Furthermore, imagine that the person preparing the end document did not even have to be the person maintaining the Excel model behind it – or even have access to open the Excel file! Responsibilities could be split this way, and the document creator could easily use the Excel results – even though s/he has no open rights to the Excel model. The benefits of this are great from a compliance standpoint.

The “UpdateChart” demo application (posted here) shows a simple case of doing exactly that. In this application, a server-side Excel workbook has a model that creates a chart. Conceptually (although it is not part of the demo), this workbook could be connected to data sources, and could calculate a different, up-to-date chart each time it is run. A client-side program uses this workbook to update a chart in a PowerPoint deck. The program connects to the server using a web service, gets the up-to-date calculated workbook, extracts the updated chart from it, and uses it to replace the chart within the PowerPoint deck – all by using the Open XML SDK, and requiring neither Excel nor PowerPoint to run.

The building blocks

Projected Sales.xlsx: An Excel workbook that contains a chart with the title "Projected Sales".
Projected Sales.pptx: A PowerPoint presentation that contains an identical chart on one of the sheets, with an identical title.
UpdateChart.exe: The program. This is a console (command line) application; running it with no arguments displays its usage help. In the demo package, the compiled program is located under UpdateChart\bin\Debug.
Microsoft Office SharePoint Server 2007: Not included with this demo, but also not entirely required in order to try out some of the demo program’s capabilities.

Running “UpdateChart”

The Basics: Simple Excel and PowerPoint file integration

The simplest thing that this program can do is open the Excel file directly (not using Excel Services or Excel), grab the chart from it, and replace the chart in the PowePoint file. This is interesting from the standpoint of understanding how Open XML file manipulation works. It also highlights the fact that in Office 2007, the format of a chart in Excel and PowerPoint (and Word) is identical – they can be exchanged by mere file manipulations, without needing any of the Office applications.

To see how this works, start by opening the Excel file in Excel, setting some values to the two parameter cells (G3 and H3), and saving the file. Then open a command window, and run the following in the folder where you have the demo Excel and PowerPoint files:

UpdateChart.exe "Projected Sales.xlsx" "Projected Sales.pptx" "Projected Sales"

(The third argument is the chart title that the program looks for in both files.) If you now open the PowerPoint file, you’ll see that the chart changed to reflect the new values from the Excel file.

Moving to the server

Using UpdateChart as shown in the previous section, while demonstrating Open XML SDK usage, is not very useful from the perspective of updating a live chart. The program does not know how to connect the Excel file to external data sources, refresh the data, and re-calculate the resulting chart. It just grabs the chart in whatever state it was when it was saved.

Here’s where the server comes into play. If you have access to a MOSS 2007 server, publish the demo Excel file to the server.

Then run this in a folder containing the demo PowerPoint file:

UpdateChart.exe -s <server_name> "<full path to server Excel file>" "Projected Sales.pptx" "Projected Sales"

You can now open your PowerPoint file and see that the chart is updated. Realize that this represents a much broader and stronger class of solutions than is actually implemented here; the Excel file on the server could be connected to data sources, and could generate a different updated file each time it is run, or potentially even different for each user who runs it.

Parameterization

What if your Excel model relies on parameter values within the workbook, instead of (or in addition to) external data sources? You’d want to let your users change some values in the workbook, and have those values affect the resulting chart. Well, this is possible, even if the users have no open access to the Excel file on the server – just the right to run it!

To demonstrate this, I’ve designated the two parameter cells (G3 and H3) as named parameters that the server recognizes (you’ll need to select them as server parameters in Excel’s “Publish” dialog when you publish the workbook to the server). Now, a user – or a program – can easily set values to those parameters, causing Excel Services to recalculate the Excel models with the new values and affecting the resulting chart. Our program does exactly that.

To see this, add an extra argument to the command line, that sets the parameter “Growth2007” to 70%:

UpdateChart.exe -s <server_name> -p Growth2007 "70%" "<full path to server Excel file>" "Projected Sales.pptx" "Projected Sales"

Your PowerPoint file should show a chart that reflects this growth in 2007.

What the code does

Note: The code sample, UpdateChart, was created using Microsoft Visual Studio 2008, however you do not need this particular version of Visual Studio to use the code. You can use the main source file, Program.cs, in a project of a previous VS version, or just open the file in a text editor and grab the pieces of the code that you find useful.

UpdateChart’s code does the following:

Parses arguments.
In the case of server usage (-s), uses the Excel Web Service to open a server workbook, optionally set a parameter value (when -p is used), and get the resulting workbook. The workbook is returned into the program as a bite array; this is a snapshot of the entire server-side Excel workbook, in the calculated state that it was when it was retrieved from the server.
In all cases, the code uses the Open XML SDK to:

Look for and extract the chart part within the Excel file.
Look for the chart part in the PowerPoint file, then override it with the new Excel chart.

One thing to notice is that in the case of the server-side Excel file, no client-side temporary file is used. The code receives the server result directly into memory as a byte array, then uses the option to initialize an Open XML file package from a stream – feeding the byte array directly into the package.

In the client-side Excel file case, the code simply initializes the Open XML package from a file.

Useful links

Code sample for this post: http://officeblogs.net/excel/updatechart.zip
Open XML SDK download: http://www.microsoft.com/downloads/details.aspx?FamilyID=AD0B72FB-4A1D-4C52-BDB5-7DD7E816D046&displaylang=en
MOSS 2007 SDK – Creating Custom Solutions with Excel Services: http://msdn2.microsoft.com/en-us/library/ms517343.aspx
MOSS 2007 SDK – Excel Web Service reference: http://msdn2.microsoft.com/en-us/library/microsoft.office.excel.server.webservices.aspx

Microsoft Dynamics CRM Data Import

Microsoft Dynamics CRM Team Blog — 2007-12-17T00:00:00

CRM MVP Ronald Lemmen returns as a guest blogger. You can read more from Ronald at his blog.

This post will dive deep into the possibilities for Data Import for Microsoft Dynamics CRM. Discussed will be the options which Microsoft gives for importing data in both the versions 3.0 as well as 4.0, which third party tools are available and what the possibilities are for creating your own custom build data import. Directly related to Data Import, is data cleansing. In this post I will discuss possible data cleansing as well.

What I won’t discuss in this post, is the most commonly used approach for getting data into CRM: Manual. While planning your data migration, don’t forget about the option to manually enter the data into the system. Depending on the amount of data, it might be cheaper to enter all data by hand. Also when you have too much data to import by hand, look at the rare data. Maybe some entities can be entered by hand. If you do want to bulk import data and will not go the manual route, then make sure you read on.

Disclaimer: All the information on Microsoft Dynamics CRM 4.0 is based on RC0 which means that it is still subject to change.

Microsoft Solutions

Microsoft offers multiple options to import data into your CRM implementation. These options are:

Bulk Import Wizard
Data Migration Framework / Manager
Data Migration Wizard for Microsoft Office Outlook with Business Contact Manager
Outlook Client

Bulk Import Wizard

The Bulk Import Wizard is available from the User Interface as you can see on the image below.

This feature allows the end user to import the following entities:

Contact
Account
Leads
Campaign Responses

The end user can upload csv (comma-delimited values) files or text files delimited with semicolons, colons or tabs. Data to be imported can only be of one specific entity, although lookup data can be uploaded as well when the guid is specified.

Examples for when to use the Bulk Import Wizard are:

Exports from Outlook to a .csv file
Acquired marketing lists

Changes with Microsoft Dynamics CRM 4.0

The Bulk Import Wizard has been improved in Microsoft Dynamics CRM 4.0. The list of entities to import has been extended with almost all system entities as well as custom entities. Also, the mappings created between the source data and the CRM system can now be saved and reused in subsequent import activities. Another new feature is the ability to see the list of imported records after the import has succeeded. Based on this list actions can be executed like Quick Campaigns and even deleting the records if necessary. The last major improvement is the ability to enrich data which is already in the system.

Data Migration Framework

The second tool which Microsoft offers is the Data Migration Framework. This tool can be used to import data if any of the following scenarios do apply:

Migrate data from multiple sources
There are records which are related to others within the same or another entity
Notes and / or attachments need to be migrated
Data needs to be cleansed before importing into the CRM system

The image below describes the approach of the Data Migration Framework. The first step is to create and initialize the CDF database. This is a database which will temporarily store the data from the source systems before it is imported into the CRM system. The source data can be extracted from multiple sources and be loaded into the CDF database. The process of moving data from the source to the CDF database is not part of the Data Migration Framework. The most common approach is using DTS or SSIS, but basically any technique can be used to import data into the CDF database. Once the data is in this temporary database, then the data can be transformed and eventually cleansed. After this phase, the data can be migrated into the CRM System.

This application does support the following out of the box entities:

Account	Discount	Note	Product
Address	Discount List	Opportunity	Quote
Annotation (including attachments)	E-mail	Opportunity Product	Quote Product
Case	Fax	Order	Sales Literature
Competitor	Invoice	Order Product	Subject
Contact	Invoice Product	Phone Call	Task
Contract	Lead	Price List	Unit
Contract Line	Letter	Price List Item	Unit Group

All out of the box and custom attributes can be imported, with some exceptions like the created on/by and modified on/by attributes.

Data Migration Wizard for Microsoft Office Outlook with Business Contact Manager

This is an application is part of the Data Migration Framework as well. This is especially designed for migrating data from Outlook with Business Contact Manager to Microsoft CRM 3.0. Since no other source data can be migrated with this tool, I will not further discuss this tool.

Changes with Microsoft Dynamics CRM 4.0

The new version of Microsoft Dynamics CRM does also offer a new version of the Data Migration Framework. Not only is the tool renamed to Data Migration Manager, also the functionality has changed. The tool now has a user friendly interface next to the command line tools and also custom entities can now be imported. Furthermore new functionality like the data maps and duplicate detection can be used while importing the data through the Data Migration Framework.

Look at the blog of Philip Richardson [1] to see a demo of how to use the Data Migration Manager in version 4.0.

Outlook Client

A very easily overlooked option is to use the Outlook Client. There is a synchronization tool build in the Outlook client which does copy the data from Outlook to the CRM server. This approach is not very scalable, but could be enough for small data imports of contact data. Add-ons like the CardScan solution do use this approach to load data into Microsoft Dynamics CRM.

3rd Party Migration Tools

If the Microsoft Solutions do not offer you the required functionality, then you can look at third party applications. There are multiple tools available. The most common ones are the ImportManager as sold by CRM Extensions and c360 and the Scribe Insight application. Both will be discussed here. Other tools can be found in the links section [2]. In the same links section, you will also find references to the comparison between ImportManager and Scribe Insight as tested by Sandor Schellenberg [3].

CRM Extensions / c360: ImportManager

Both CRM Extensions as well as c360 offer the ImportManager with identical functionality. This application allows imports from one or more data sources, create new or update existing records of a given entity in the CRM database. This could be any customizable entity or a custom entity. Also in the same process relationships between the imported record and other entities can be created. While importing data, duplicate detection rules can be applied to determine if a record is new or should be updated.

There are connectors created for the data sources ODBC and text files. Other connectors can easily be created by a developer to suit the needs of the customer. This application also offers a scheduling option as well which makes it very usable for repeating imports as well.

With the release of CRM 4 new functionalities will be released as well. The ImportManager will then be a full Client / Server application which allow this application to be used for CRM Live and Hosted environments as well.

Scribe: Scribe Insight

Scribe Insight is an application which exists already for a long time. It offers the same functionality as ImportManager and more. This is a CRM migration application with connectors to many different applications including Salesforce.com, ACT, GoldMine and of course Microsoft Dynamics CRM.

One of the reasons why this application is widely used, is the possibility to create two-way migration functionality. Data can be imported in Microsoft Dynamics CRM and can also be exported to be used in any other application. Another reason to choose this application can be because of the wide range of possibilities in modifying data. Scribe offers so called functions in which data manipulation can take place. Within these functions the developer has many possibilities to calculate or create the correct value for a specific attribute.

Scribe does offer training to quickly learn how to use this application. This training is strongly recommended because of the many possibilities with this application.

Scribe will release the adapter for the newest version about two weeks after the RTM release of Microsoft Dynamics CRM 4.0.

Custom Migration Application

The latest option is to create a migration application in house. Advantages of creating your own migration application include:

Full control of the application
Ability to adjust performance settings based on data
Relatively easy to include exceptional business logic

The obvious disadvantage is of course that knowledge about the CRM web services is required and also knowledge about other techniques required to perform the data migration. Some possible approaches are discussed in this chapter.

Web Services

The most obvious approach is to create a .NET application which uses the CRM web services to import data into CRM. The SDK [4] is a very good starting point. Basically the developer would read data from the source application, create an instance of the entity to be created, fill the attributes with the correct values (could be calculated or looked up in another system), add the record to the CRM system. If needed a check can be performed to see if the record is already in the system.

This approach does allow a lot of possibilities for performance improvement. If data needs to be requested from another source, it could be all loaded while the application loads instead of a per record request. Also the connection with the Microsoft Dynamics CRM web service allows several performance settings. Make sure to look at the settings for UnsafeAuthenticatedConnectionSharing. A link to the blog of Aaron Elder is included[5] with more information about performance settings for the web service.

SSIS – Web Services

If you like SQL solutions more, then you can leverage a new feature which is available if you’re database is running on MSSQL Server 2005. This version includes the possibility to create SSIS packages which can communicate with web services. How to use the web services task in SSIS is described on Technet[6].

BizTalk – CRM Adapter

Another possible approach is to connect CRM with BizTalk. This is very valuable if BizTalk is already used within the organization. Microsoft has developed a BizTalk adapter for Microsoft Dynamics CRM 3.0[7]. This adapter allow you to perform the web service calls directly from within BizTalk. The advantage of this approach is that you can create this solution with minimal knowledge of the CRM application. A disadvantage though is that it is very hard to optimize the performance when using the BizTalk Adapter.

Data Quality

A topic which is directly related to Data Import, is the quality of the data which is imported. The quality is one of the most crucial success factors of any CRM project. A lot of checks can be performed while importing data, especially checking for duplicates. Microsoft Dynamics CRM 3.0 does not offer any out of the box tools for duplicate detection, but there are some good alternatives. Also some of the third party tools for importing data do include duplicate detection functionality.

3rd Party Tools

The most known third party add-on for data quality is the c360 Data Quality Center add-on. The core functionality of this add-on is duplicate validation and address validation. The address validation verifies the address using US Postal Code standards. The address are verified, corrected and enhanced by this engine. The duplicate detection software does check for duplicates against the entities Lead / Account and Lead / Contact. Duplicates can be detected while saving a record or can be scheduled for a nightly batch. Detection of a duplicate is done by using match code rules which can be defined by administrators. The Data Quality Center is part of the c360 Core Productivity Pack[8].

Another supplier of a Data Quality add-on for Microsoft Dynamics CRM 3.0 is Pitney Bowes. They have developed a similar add-on which also does address verification and duplicate management. [9]

Microsoft Dynamics CRM 4.0

With the release of Microsoft Dynamics CRM 4.0, Microsoft is making a large step in terms of data quality. Instead of relying on a third party add-on, the duplicate checking is now included within the software. The functionality which Microsoft has included in the product is very similar to the duplicate checking as supplied by c360. It does allow the administrator to set up rules and based on these rules the system will recognize a potential duplicate and inform the user. Also batches can be run which do show a list of potential duplicate records.

Philip Richardson has created a short screen cast which does show the duplicate detection like an end user[10].

Conclusion

In this article we have seen that there are many different approaches and applications available for performing a data import into Microsoft Dynamics CRM. For each different business case another approach or application is the correct one. When the 3.0 version of the CRM product is used within your organization, then a third party application is quickly needed. The release of Microsoft Dynamics CRM 4.0 will allow a lot more out of the box, so for relatively simple data migrations can be easily done without third party software or a custom build application. If the migration is larger though, or specific requirements need to be fulfilled, then you will still need to rely on a third party solution or create your own solution.

Links for more information

[1] Demonstration of how to use the Data Migration Manager in Microsoft Dynamics CRM 4.0 http://www.philiprichardson.org/screencasts/titan/DataMigration/DataMigration.html
[2] URL’s for 3^rd party import applications:
- Scribe Insight http://www.scribesoftware.com/microsoft-dynamics-crm.asp
- c360 ImportManager http://www.c360.com/ImportManager.aspx
- CRM Extensions ImportManager http://www.crmextensions.com/Page.aspx?id=66673538-2ae2-db11-9607-0003ff3b5e8e
- Inogic Import and Conversion Tools http://www.inogic.com/dynamics_crm_solutions.htm
- Vertical Marketing Inc. Goldmine to Microsoft CRM Convertor http://crmsuperchargers.com/GMtoMSConverter.htm
[3] Comparison between Scribe Insight and ImportManager by Sandor Schellenberg
- Part 1: http://www.friendlycrmonster.com/2007/03/scribe-insight-vs-c360-import-manager.html
- Part 2: http://www.friendlycrmonster.com/2007/03/scribe-insight-vs-c360-import-manager_27.html
- Part 3: http://www.friendlycrmonster.com/2007/04/scribe-insight-vs-c360-import-manager.html
- Conclusion: http://www.friendlycrmonster.com/2007/04/and-winner-is.html
[4] Microsoft Dynamics CRM 3.0 SDK http://www.microsoft.com/downloads/details.aspx?familyid=9C178B68-3A06-4898-BC83-BD14B74308C5&displaylang=en
[5] CRM Web service performance improvement by Aaron Elder http://www.invokesystems.com/cs/blogs/mscrm/archive/2006/06/01/28.aspx
[6] How to use the web services task within SSIS http://technet.microsoft.com/en-us/library/ms140114.aspx
[7] BizTalk – Microsoft Dynamics CRM 3.0 Adapter http://www.microsoft.com/downloads/details.aspx?FamilyID=4628FCA6-388D-45BC-A154-453B920DBCB8&displaylang=en
[8] c360 Data Quality Center http://www.c360.com/DataQualityCenter.aspx
[9] Pitney Bowes Group 1 Software Data Quality Adapter http://www.g1.com/Products/Customer-Data-Quality/CDQ-Platform-For-MS-CRM/
[10] Screen cast on Duplicate Detection in Microsoft Dynamics CRM 4.0 http://www.philiprichardson.org/screencasts/titan/DuplicateDetectionCTP3/DuplicateDetectionCTP3.html

Ronald Lemmen

Using BizTalk Oracle Adapter - NativeSQL

A BULLs view of LIFE and TECHNOLOGY — 2007-11-28T00:00:00

Steps to run a sample scenario which retrieves data from the Oracle DB

1. System DSN - ODBC - keep the name same as the name of the database, else the adapter will not recognize the Database

2. Create a Physical Port using the BizTalk Administration Console.
   a. Select the type of the port as Oracle Database.
   b. Click configure and provide data as mentioned in steps below
   c. Provide the Oracle password
   d. In Path - provide the path to the BIN directory of your Oracle Home.
   e. In Service Name specify the name of the DSN - which is the name of the database
   f. Provide the user name, and click OK.
   g. Select the Send Pipeline as XML Transmit.
   h. Select the Receive Pipeline as XML Receive.

3. In the project where you need to create an orchestration, right click and select add generated items.

4. Select Add Adapter MetaData and click Add

5. Select Oracle(r) Database,
   a. select the SQLServer instance where BizTalk is installed
   b. Select the BizTalkMgmtDb
   c. In Port select the port created in Step 2.
   d. Click Next

6. In Select the set of Services to be added to the database, click on OracleDb, then click on the DSN name (created in Step 1) and select appropriate table(s) or Native SQL. For out example we would be using the NativeSQL.

7. Click on Finish, the wizard will generate 3 schema and 1 orchestration file.
   a. The schema files generated includes the following:
        1. NativeSQLService_1.xsd
        2. NativeSQLService_NativeSQL_x5d_1.xsd
        3. NativeSQLService_obj_1.xsd

b. An empty Orchestration named BizTalk Orchestration.odx is generated.

8. Open the NativeSQLService_NativeSQL_x5d_1.xsd file and and you will see the following nodes:
   a. SQLExecute
   b. SQLExecuteResponse
   c. SQLEvent
   d. SQLEventResponse

9. We would be focusing on the SQLExecute and SQLExecuteResponse. SQLExecute has StatementText and ParameterSet. In this exercise we will only use the StatementText. To use the StatementText, make it a distinguish field so that it can be accessed and set in Orchestration.

10. Now Open the Orchestration, created. Define 2 messages one corresponding to SQLExecute type - say "MessageOracleSQL" and other SQLExecuteResponse type - say "MessageOracleSQLResponse".

11. Configure a new port - in wizard select Use Existing port and select BizTalk_Server_Oracle_Adapter.NativeSQL.

12. Select Port direction as "I will be sending a Request and receiving a response", and port binding as specify later.

13. In the Orchestration now add a Construct Message Shape and specify the output as "MessageOracleSQL".

14. Add a new Transform Shape which takes some input message and outputs MessageOracleSQL.parameters, this step is required to initialize the message.

15. Add a new MessageAssignment Shape and set the StatementText property of the "MessageOracleSQL" to the Oracle SQL Statement to be executed. "MessageOracleSQL.parameters.StatementText = {Oracle SQL Statement}", note since the SQLExecuteResponse is a generic data structure, to return the data from the Oracle generate a statement that returns the data as XML in One Column.

15. Add a Send Shape and select Message as "MessageOracleSQL", and connect it to the SQL Execute - Request of the port configured in step 11/12.

16. Add a Receive Shape, whic connects with the Response of SQL Execute Port, with the Message as SQLOracleResponse.

17. Now in order to retrive the data from the Message, define a Transformation which takes the input as SQLExecuteResponse schema and generates the output as required. Here you would be required to use the Scripting Functiod to break the XML from the columndata into the required message.

BizTalk - Strange Exception Messages and Corresponding Fixes.

Saravana Kumar in Biztalk — 2007-11-20T00:00:00

During our BizTalk development we come across some really strange error message, which normally doesn't reflect the real cause. I always wanted to consolidate everything in one place. After seeing couple of errors that's being raised in the news groups, I thought this is the right time to materialize it. If you have come across any strange errors and willing to share please let me know via my contact page.

========================================================

Exception: 'System.Xml.XmlDocument' does not contain a definition for 'Status'

========================================================

Cause:

System.Diagnostics.Debug.WriteLine(PO_SOURCE.Status.ToString());

Resolution:

status = PO_SOURCE.Status;
System.Diagnostics.Debug.WriteLine(status.ToString());

where "status" is a variable of type System.String.

Credit: Tim

Remark: When you try to do the conversion directly on the context property, the above exception is raised. You can easily fix it by first assigning the context property to a local variable and then doing the conversion.

Date Logged: 17th May 2007 (BizTalk 2006)

========================================================

ERROR: The BAM deployment failed.
A connection cannot be made. Ensure that the server is running.
No connection could be made because the target machine actively refused it

========================================================

Credit: Saravana Kumar

Remark:

If you receive the following error when you try to deploy BAM activities using bm.exe tool

The error information doesn't reflect on the actual cause. Make sure, "SQL Server Analysis Services" is started.

Date Logged: 26th April 2007 (BizTalk 2006)

========================================================

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.)

========================================================

Remark:

Make sure you Visual Studio project is pointing to the correct management server (Project Properties -> Deployment -> Server (BizTalk Group).

Date Logged: 29th May 2007 (BizTalk 2004/2006).

Nandri!

Saravana

BizTalk Map - Suppress Element if Attribute is not required.

Saravana Kumar in Biztalk — 2007-11-20T00:00:00

Scenario: The following question is taken from BizTalk.General NewsGroup (altered slightly for readability purpose)

"I'm mapping an element in the source schema to an attribute in the destination schema. The element does not exist in the source schema, but when I run the map it creates the element in the destination schema that houses the attribute I am mapping to. The attribute does not show up in the result of the map, just the empty element. I need to have it not create the empty element if it is not going to put a value in the attribute."

Here is the solution:

Thanks goes to my collegue Matthew Johnstone and same technique was explained by Kiran Pabba in the newsgroup. I know for sure Matthew worked out this sample himself :-)

BizTalk, MSDTC, SSO and Cluster

Saravana Kumar in Biztalk — 2007-11-20T00:00:00

Last night (BTW it was a Sunday) around 8PM I received a call from my ex-boss and good friend Mike Prager who is working as a Senior architect in one of the major retail project in UK, saying "Hey! SK, we had a power failure this afternoon in our data center and after the servers came back all our receive locations are disabled and send ports are not started. When I try to access them via the admin console, I'm receiving the error

"Cannot retrieve list of objects due to a WMI provider failure"

have you experienced this before."

Well, I've seen this error in the past, but there are tons of things which can cause this error in BizTalk, we tried few things like checking whether all the SSO services are health on all the machines, MSDTC is running and configured correctly on each box, correct SSO master secret server is configured on the BizTalk group, etc, etc.

At last, I left Mike with the option to try DtcPing, since I'm not physically there. I couldn't assist much.

This morning I received a call from enthusiastic Mike, saying "Hey! I solved the problem, the error is due to the race condition (dependency) between the DTC and SSO service in the SQL cluster. When the SSO service starts before the DTC, there is an issue. You get the WMI error. But if the DTC starts before SSO, everything is well and good."

The reason I'm blogging it is I never experienced this before and it's one of those strange things which we'll never think about. I hope this will solve someone hours of troubleshooting time.

Thanks Mike, for sharing this information.

Nandri!

Saravana