MCTS in BizTalk 2006

I passed the MCTS certification exam in BizTalk 2006 today. There were 50 questions and I finished them in 60-70 minutes. I have been programming in BizTalk 2004 and 2006 extensively for a couple years now, as it turned out that I over-prepared for the exam; I should have (probably) passed it just based on my experience. I would have to say the exam questions are relatively easy and not well balanced. Like most of the bloggers pointed out that there are too many BAM and BRE related questions. Moreover, there are too many questions about distinguished/promoted properties.
There is a lack of the core architecture questions on the messaging engine, publishing-subscribing model, orchestrations etc.

I almost felt that those exam authors might be programming heavily in BAM/BRE; they were probably not extremely familiar (or concerned) with the more popular/well-known features in BizTalk like orchestrations, maps, pipelines etc. There're 0 questions about the custom components development which I consider it's a big weakness of the exam. Programming custom components like adapters, pipeline components, functoids, fact retrievers, and BAM interceptors are essential part of the BizTalk framework. The exam didn't cover these areas well.

Another big complaint from me is that MCTS in BizTalk 2006 is not a constituent part of the MCPD certification. From my perspective, it most definitely should be. BizTalk developers must have equally strong .NET programming skills as web / winform developer. Anyway, I hope MSFT would consider this seriously and revise the certification path.

System.Web.Services.Protocols.SoapException: Server was unable to process request. --> Cannot create ActiveX component. EnvDTE

I got this exception thrown at me when I tried to do the following in the web service:

DTE ideObject = (DTE) Microsoft.VisualBasic.Interaction.CreateObject
(
"VisualStudio.DTE.7.1", ""
);

You may run into the similar problem if you wanted to access native Microsoft objects such as excel or word. There are two solutions:
1) Make sure your aspnet_wp work process runs under a priviledged account that has the sufficient permission to carry out this task. This is usually done through the impersonation in web.config file. You will find plenty of posts explaining how this is achieved.
2) Grant explicit Launch/Activation and Access permission to "Microsoft Visual Studio Solution Object" in DCOM Config.

The following are the steps for option 2):
a) Go to "Start | Settings | Control Panel | Administrative Tools | Components Services".
b) Drill thru "Components Services -> Computers -> My Computer -> DCOM Config"
c) Select "Microsoft Visual Studio Solution Object"
d) Right click and bring up the "Properties"
e) Click on the "Security" Tab
f) In the groupbox "Launch and Activation Permissions"; select "Customize" radio button and click on "Edit" button; check both "Local Launch" and "Local Activation" to grant these permissions to Machine\ASPNET account.
g) In the groupbox "Access Permissions"; repeat step (f) to grant the "Local Access" to Machine\ASPNET account.
h) Click "Apply" to apply the changes.

Here are some screen shots:


Microsoft Visual Studio Solution Object Properties

Launch and Activation Permissions

Access Permissions

StartCache: Error 0x80070002: failed to get download: 90A40409-6000-11D3-8CFE-0150048383C9

Ran into a HUGE installation problem when I attempted to install SQL Server 2005 RTM on my personal computer. I had SQL Server 2005 Beta CTP installed before. I kept getting the OWC11 installation failure message. I ended up manually install the OWC11 component with logging turned on... The log file tells me it's the infamous : 1603 problem.

Action ended 21:33:45: ProcessComponents. Return value 1.
Action 21:33:45: StartCache. Starting cache of cabinets to local installation source
Action start 21:33:45: StartCache.
StartCache:  Error 0x80070002: failed to get download: 90A40409-6000-11D3-8CFE-0150048383C9
Action ended 21:33:45: StartCache. Return value 3.
Action ended 21:33:45: INSTALL. Return value 3.
Action ended 21:33:45: ExecuteAction. Return value 3.
Action 21:33:45: TheFatalError.
Action start 21:33:45: TheFatalError.

I ended up cleanning two registry keys which finally made it work:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\Delivery\90A40409-6000-11D3-8CFE-0150048383C9
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\Delivery\SourceEngine\Downloads\90A40409-6000-11D3-8CFE-0150048383C9

Hopefully it will help if you run into the same problem.

A Microsoft Distributed Transaction Coordinator problem prevented connection to the Configuration database again

When you work with a Microsoft BizTalk Server 2004 project on a Microsoft Windows Server 2003-based computer, and you deploy the assemblies to BizTalk Server 2004, you may receive the following error message from Microsoft Visual Studio .NET:

[DeploymentException] Cannot open database BizTalkMgmtDb on server [server]. 
[DeploymentException] Cannot open database BizTalkMgmtDb on server [server]. 
A Microsoft Distributed Transaction Coordinator problem prevented connection to the Configuration database. 
The transaction has already been implicitly or explicitly committed or aborted 

You will also receive a similar error if you run the BizTalk Deployment Wizard. KB839187 provided a solution which is pertinent but not sufficient in some scenarios. Recently, I had a new development set up that took me back to this problem and it cost me approximately 2.5 hours of productivity to figure out exactly why.

I have a mutile server BizTalk setup. BizTalk Databases and the default BizTalk host are on a dedicated Windows 2003 server box. I do my development work on laptop with Windows XP (SP2). When I try to deploy a BizTalk assembly, I ran into the problem described above. I immediately tried the workaround suggested by KB839187 which didn't work. After some research, I belive the following steps are a better overall approach to this problem.

1. Make sure your development machine and BizTalk server (server runs BizTalk databases) can ping each other


2. Make sure MS DTC service is up and running on both computers


3. Modify the following registry key on both computers: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\TurnOffRpcSecurity = 1


4. Enable the "Network Clients" for MS DTC for both computers. To do this:
   
   
   
   • Click through: Start | Settings | Control Panel | Administrative Tools | Component Services
   
   
   • Expand "Computers" under "Component Services" node
   
   
   • Right click on "My Computer" and click on "MSDTC" tab on "My Computer Properties" dialog
   
   
   • Click on "Security Configuration" button
   
   
   • For Windows Server 2003, check "Network Clients" checkbox as indicated in the following picture.
     
     
     For Windows XP (SP2), configure the "Security Settings" as indicated by the following picture.
     
     
   
   
   
   
5. Restart the MS DTC services on both computers if they have not been automatically restarted.

Themize Windows Application

I am thinking about writing an article about how to themize the windows forms by using a cascading style sheet. I have a subset of functionality implemented. Here's a form that applied a nice theme called "VRust".

How to use updategram to update multiple rows in BizTalk orchestration

Using the updategram to insert and delete are very straightforward. It's also easy to update one row of data using the updategram in BizTalk. BizTalk SQL adapter seems to be problematic when dealing with updating multiple rows of data using updategram. SQL XML states that you need to use the attribute "updg:id" to associate the records enclosed in the <updg:before> and those enclosed in <updg:after>(article here). I am pretty sure if you are reading this blog, you have tried the default schema generated by BizTalk and realized it didn't work. I have tried the following two approaches and they didn't work:

1. Use multiple <sync> nodes to describe the updategram. It didn't work.
   

   
   <?xml version="1.0" encoding="utf-8" ?> 
   <ns0:updateStatsPendingRequest xmlns:ns0="http://www.bankofamerica.com/">
    <ns0:sync>
     <ns0:before>
      <ns0:StatsPending APPLICATIONID="100010.032970" /> 
     </ns0:before>
     <ns0:after>
      <ns0:StatsPending APPLICATIONID="100010.032970" 
        SITEID="24" ASSIGNPROCESSED="true" /> 
     </ns0:after>
    </ns0:sync>
    <ns0:sync>
     <ns0:before>
      <ns0:StatsPending APPLICATIONID="100010.032971" /> 
     </ns0:before>
     <ns0:after>
      <ns0:StatsPending APPLICATIONID="100010.032971" 
        SITEID="25" ASSIGNPROCESSED="true" /> 
     </ns0:after>
    </ns0:sync>
   </ns0:updateStatsPendingRequest>
   

   
   Conclusion: SQL Adapter has difficulty handling multiple <sync> elements.
   


2. Use the <id> attributed as suggested by SQL XML. I added the extra id attribute into the schema like this:
   

   
   <xs:attribute name="id" type="xs:string" updategram:Prefix="updg" />
   

   
   This solution fails since the generated xml document instance doesn't have the correct xml schema prefix. As described by SQL XML, the prefix for the "id", "sync", "before" and "after" should really be "updg". However, the xml instance generated by BizTalk will look like:
   

   
   <?xml version="1.0" encoding="utf-8" ?> 
   <ns0:updateStatsPendingReq xmlns:ns0="http://www.bankofamerica.com/">
    <ns0:sync>
     <ns0:before>
      <ns0:StatsPending ns0:id="100010.040649" /> 
      <ns0:StatsPending ns0:id="100010.042313" /> 
     </ns0:before>
     <ns0:after>
      <ns0:StatsPending ns0:id="100010.040649" 
                        ns0:APPLICATIONID="100010.040649" 
                        ns0:SITEID="24" 
                        ns0:ASSIGNPROCESSED="1" /> 
      <ns0:StatsPending ns0:id="100010.042313" 
                        ns0:APPLICATIONID="100010.042313" 
                        ns0:SITEID="24" 
                        ns0:ASSIGNPROCESSED="1" /> 
     </ns0:after>
    </ns0:sync>
   </ns0:updateStatsPendingReq>
   

   
   

As you can see from the above xml instance, all of the elements and attributes are qualified with "ns0" prefix which is not what we wanted. After some research, I figured out that SQL XML need to use the "updg" prefix in order to correctly associate the records in <before> and <after> blocks. Therefore, we need to make the BizTalk to generate the document instance looks like this:

<?xml version="1.0" encoding="utf-8" ?> 
<ns0:updateStatsPendingReq 
  xmlns:updg="urn:schemas-microsoft-com:xml-updategram" 
  xmlns:ns0="http://www.bankofamerica.com/">
 <ns0:sync>
  <ns0:before>
   <ns0:StatsPending updg:id="100010.040649" /> 
   <ns0:StatsPending updg:id="100010.042313" /> 
  </ns0:before>
  <ns0:after>
   <ns0:StatsPending updg:id="100010.040649" 
                     ns0:APPLICATIONID="100010.040649" 
                     ns0:SITEID="24" 
                     ns0:ASSIGNPROCESSED="1" /> 
   <ns0:StatsPending updg:id="100010.042313" 
                     ns0:APPLICATIONID="100010.042313" 
                     ns0:SITEID="24" 
                     ns0:ASSIGNPROCESSED="1" /> 
  </ns0:after>
 </ns0:sync>
</ns0:updateStatsPendingReq>

After I tweak the schemas to generate the xml instance like above, it worked!!
If you run the SQL profiler, you will see the following TSQL statements are generated:

SET XACT_ABORT ON
BEGIN TRAN
DECLARE @eip INT, @r__ int, @e__ int
SET @eip = 0

UPDATE StatsPending SET SITEID=N'24', ASSIGNPROCESSED=N'1' 
WHERE (APPLICATIONID=N'100010.042313');  
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
 IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
 IF (@r__ > 1) RAISERROR ( N'Ambiguous update, unique identifier required', 16, 1)
 ELSE IF (@r__ < 1) RAISERROR ( N'Empty update, no updatable rows found', 16, 1)

UPDATE StatsPending SET SITEID=N'24', ASSIGNPROCESSED=N'1' 
WHERE (APPLICATIONID=N'100010.040649');  
SELECT @e__ = @@ERROR, @r__ = @@ROWCOUNT
 IF (@e__ != 0 OR @r__ != 1) SET @eip = 1
 IF (@r__ > 1) RAISERROR ( N'Ambiguous update, unique identifier required', 16, 1)
 ELSE IF (@r__ < 1) RAISERROR ( N'Empty update, no updatable rows found', 16, 1)

IF (@eip != 0) ROLLBACK ELSE COMMIT
SET XACT_ABORT OFF

The following are list of things you need to do in order to ask BizTalk to generate the "updg" prefix.

1. Define Updategram.xsd like this:
   
   


2. In your updategram schema's <xs:schema> element, add this line:
   

   
   xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
   

   
   


3. Add an <xs:import> element in your updategram schema:
   

   
   <xs:import
     schemaLocation="Updategram.xsd"
     namespace="urn:schemas-microsoft-com:xml-updategram" />
   

   
   


4. Add <updg:id> attribute in <before> and <after> elements by using the following line:
   

   
   <xs:attribute ref="updg:id" />
   

   
   Your schema will look like this:
   
   

   
   <?xml version="1.0"?>
   <xs:schema
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns="http://www.bankofamerica.com/commercialprocesstechnology/sbui"
    xmlns:updg="urn:schemas-microsoft-com:xml-updategram"
    targetNamespace="http://www.bankofamerica.com/commercialprocesstechnology/sbui"
    attributeFormDefault="unqualified"
    elementFormDefault="qualified"
   >
    <xs:import
     schemaLocation="Updategram.xsd"
     namespace="urn:schemas-microsoft-com:xml-updategram" />
    <xs:element name="updateStatsPendingReq">
     <xs:complexType>
      <xs:sequence>
        <xs:element
         xmlns:updategram="urn:schemas-microsoft-com:xml-updategram"
         updategram:Prefix="updg" minOccurs="1" maxOccurs="unbounded" name="sync">
         <xs:complexType>
          <xs:sequence>
           <xs:element updategram:Prefix="updg" minOccurs="0" maxOccurs="unbounded" name="before">
            <xs:complexType>
             <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="StatsPending">
               <xs:complexType>
                <xs:attribute ref="updg:id" />
                <xs:attribute name="APPLICATIONID" type="xs:decimal" />
               </xs:complexType>
              </xs:element>
             </xs:sequence>
            </xs:complexType>
           </xs:element>
           <xs:element updategram:Prefix="updg" minOccurs="0" maxOccurs="unbounded" name="after">
            <xs:complexType>
             <xs:sequence>
              <xs:element minOccurs="0" maxOccurs="unbounded" name="StatsPending">
               <xs:complexType>
                <xs:attribute ref="updg:id" />
                <xs:attribute name="APPLICATIONID" type="xs:decimal" />
                <xs:attribute name="SITEID" type="xs:string" />
                <xs:attribute name="ASSIGNPROCESSED" type="xs:boolean" />
               </xs:complexType>
              </xs:element>
             </xs:sequence>
            </xs:complexType>
           </xs:element>
          </xs:sequence>
         </xs:complexType>
        </xs:element>
       </xs:sequence>
     </xs:complexType>
    </xs:element>
     <xs:element name="updateStatsPendingRep">
       <xs:complexType>
         <xs:sequence>
           <xs:element name="Success" />
         </xs:sequence>
       </xs:complexType>
     </xs:element>
   </xs:schema>
   

   
   


5. In the message transformation shape when you construct the updategram, use a map similar to this:
   
   

Then just fire off the orchestration and watch the updategram does its work. The particular orchestration I ran was able to update approximately 1800 records in a few seconds without any problem. Email me if you need a sample project.

Inline XSLT Call Template

Google's Biztalk group posted this interesting problem about how to use the Inline XSLT Call Template and cumulative functoid in the map. I am quoting Jeremy's question here:

"In the following sample, I have a multiple invoice file that has 1 header record and a repeating detail record for each invoice. Can someone help me using inline xslt to sum the detail records of each invoice so that I can get the total for each invoice. I have seen how to do this just using xslt instead of the mapper, but I would like to do this within the mapper as I have other functoids that I am using. It appears I cannot get the cumulative sum functoid to sum each invoice with the scope parameter; instead it always seems to sum the entire file or nothing at all."

inbound: 

<Invoices xmlns="http://www.google.com">

 <Invoice>

  <Header>

   <Credit>-20</Credit>

  </Header>

  <Detail>

   <Debit>10</Debit>

  </Detail>

  <Detail>

   <Debit>9</Debit>

  </Detail>

  <Detail>

   <Debit>1</Debit>

  </Detail>

 </Invoice>

 <Invoice>

  <Header>

   <Credit>-10</Credit>

  </Header>

  <Detail>

   <Debit>7</Debit>

  </Detail>

  <Detail>

   <Debit>3</Debit>

  </Detail>

 </Invoice>

</Invoices>



outbound: 

<ns0:Invoices xmlns:ns0="http://www.google.com">

 <ns0:TotalFileCredit>-30</ns0:TotalFileCredit>

 <ns0:TotalFileDebit>30</ns0:TotalFileDebit>

 <ns0:Invoice>

  <ns0:TotalInvoiceCredit>-20</ns0:TotalInvoiceCredit>

  <ns0:TotalInvoiceDebit>20</ns0:TotalInvoiceDebit>

  <ns0:Header>

   <ns0:Credit>-20</ns0:Credit>

  </ns0:Header>

  <ns0:Detail>

   <ns0:Debit>10</ns0:Debit>

  </ns0:Detail>

  <ns0:Detail>

   <ns0:Debit>9</ns0:Debit>

  </ns0:Detail>

  <ns0:Detail>

   <ns0:Debit>1</ns0:Debit>

  </ns0:Detail>

 </ns0:Invoice>

 <ns0:Invoice>

  <ns0:TotalInvoiceCredit>-10</ns0:TotalInvoiceCredit>

  <ns0:TotalInvoiceDebit>10</ns0:TotalInvoiceDebit>

  <ns0:Header>

   <ns0:Credit>-10</ns0:Credit>

  </ns0:Header>

  <ns0:Detail>

   <ns0:Debit>7</ns0:Debit>

  </ns0:Detail>

  <ns0:Detail>

   <ns0:Debit>3</ns0:Debit>

  </ns0:Detail>

 </ns0:Invoice>

</ns0:Invoices>

Obviously the inbound and outbound xml document instances could share a same schema. Here's the schema code:

<xs:schema

 xmlns:xs="http://www.w3.org/2001/XMLSchema"

 xmlns="http://www.google.com"

 targetNamespace="http://www.google.com"

 elementFormDefault="qualified"

 attributeFormDefault="unqualified"

>

<xs:complexType name="Detail">

 <xs:sequence>

  <xs:element name="Debit" type="xs:int" />

 </xs:sequence>

</xs:complexType>

<xs:complexType name="Header">

 <xs:sequence>

  <xs:element name="Credit" type="xs:int" />

 </xs:sequence>

</xs:complexType>

<xs:complexType name="Invoice">

 <xs:sequence>

  <xs:element minOccurs="0" name="TotalInvoiceCredit" type="xs:int" />

  <xs:element minOccurs="0" name="TotalInvoiceDebit" type="xs:int" />

  <xs:element name="Header" type="Header" />

  <xs:element maxOccurs="unbounded" name="Detail" type="Detail" />

 </xs:sequence>

</xs:complexType>

<xs:complexType name="Invoices">

 <xs:sequence>

  <xs:element minOccurs="0" name="TotalFileCredit" type="xs:int" />

  <xs:element minOccurs="0" name="TotalFileDebit" type="xs:int" />

  <xs:element maxOccurs="unbounded" name="Invoice" type="Invoice" />

 </xs:sequence>

</xs:complexType>

<xs:element name="Invoices" type="Invoices" />

</xs:schema>

Here's the map I have used:

In the scripting functoid, I used the XSLT Call Template below:

<xsl:template name="MyXsltConcatTemplate">

 <xsl:param name="param1" />

 <xsl:element name="ns0:TotalInvoiceDebit" 

  namespace="http://www.google.com">

  <xsl:value-of 

   select="sum(*[local-name()='Detail']/*[local-name()='Debit'])" />

 </xsl:element>

</xsl:template>

We couldn't use the inline xslt here since we need to pass in a local node "Invoice" that occurs multiple times in the inbound document. You can't pass in any parameter using the inline xslt script. The inline xslt call template, however, allows you to pass in a local node and you can apply your xslt transformation within the scope of passed parameter. In this case it is "param1" and it refers to one of the "Invoice" element of the xml document.