Dec 132007

I’ve created a BizTalk Excel File decoder pipeline.  It accepts Excel 2003-2007 .xls files.  It will parse each worksheet into a child node under the root node you specify.


Here are the Pipeline Component Properties.


Here is an example XLS file.



The resultant XML file creates the Root Node “SHEETS” as specified in the pipeline properties.  The children of “SHEETS” are the worksheet names listed in alphabetical order.  I dropped 1200 Excel spreadsheets into a receive location and BizTalk parsed each perfectly in less than 10 seconds.

<Sheets xmlns=""> <First> <RowData> <Column1>row1column1</Column1> <Column2>row1column2</Column2> </RowData> <RowData> <Column1>row2column1</Column1> <Column2>row2column2</Column2> </RowData> <RowData> <Column1>row3column1</Column1> <Column2>row3column2</Column2> </RowData> </First> <Fourth> <RowData> <Column1>Fourth</Column1> </RowData> <RowData /> </Fourth> <Second> <RowData> <Column1>Second</Column1> </RowData> <RowData /> </Second> <Third> <RowData> <Column1>Third</Column1> </RowData> <RowData /> </Third> </Sheets>

To learn more about obtaining this Pipeline Component, go here.

Dec 122007

Something recently changed on a development server that disabled the ability for IE to open xml documents.

I resolved it by following these steps:

1. From start menu select run

2. Type ‘regedit’ and press enter. This will bring registry editor.

3. From the regedit menu select Edit and then Find

4. Type “HKEY_CLASSES_ROOT\Mime\Database\Content Type\text/xml” in the ‘Find What’

5. Regedit will find the content that is related to that entry

6. Verify your classid CLSID={48123BC4-99D9-11D1-A6B3-00C04FD91555} ?

7. Verify your Extension=.xml ?

8. Verify your Encoding=hex:08,00,00,00 ?

If all those values are correct, but still not working, do the following:

1. From start menu select run
2. Type ‘regsvr32 msxml3.dll’
3. Click OK on the dialog box.

Dec 042007

A friend asked a few questions about the Xref functiods that I thought everyone (including myself) could benefit from

  1. Can we have many application [sic] using Cross-referencing functoid and pointing to the same database? Yes, this is the beauty of the Xref functionality, you can create multiple Application Types, and application type would be for inbound purchase orders for example, and you can have another application type for outbound invoices for example. Even to a greater extent, you can have application instances for each application type, so you can have a partner specific application instance of the inbound purchase order, and a partner specific application instance for outbound invoices. The ListOfAppType.xml would look like this:
    <?xml version="1.0" encoding="utf-8"?> <listOfAppType> <appType> <name>Inbound Purchase Order</name> </appType> <appType> <name>Outbound Invoice</name> </appType> </listOfAppType>

    And here is the example of how the ListofAppInstance.xml would look:

    <?xml version="1.0" encoding="utf-8"?> <listOfAppInstance> <appInstance> <instance>Partner 1</instance> <type>Inbound Purchase Order</type> </appInstance> <appInstance> <instance>Partner 2</instance> <type>Inbound Purchase Order</type> </appInstance> <appInstance> <instance>Partner 1</instance> <type>Outbound Invoice</type> </appInstance> <appInstance> <instance>Partner 2</instance> <type>Outbound Invoice</type> </appInstance> </listOfAppInstance>

    Here is what it looks like within the tool

    Xref Example

  2. When is purge data script useful? There is a stored procedure that is called xref_Cleanup, this is a stored procedure that brings back the various tables to factory settings. Only use this when you want to clear out everything. There is currently no stored procedure nor out of the box way to clear out portions of the data. I actually opened a ticket because I could not load data after I truncated all of the data in the various tables because I could no longer import data into the tables anymore. Truncating the tables is not the way to clear out the tables, you need to run this stored procedure. For your information, here is the stored procedure to see what it does:

    CREATE PROC [dbo].[xref_Cleanup] AS TRUNCATE TABLE [dbo].[xref_AppInstance] TRUNCATE TABLE [dbo].[xref_AppType] TRUNCATE TABLE [dbo].[xref_IDXRef] INSERT INTO [dbo].[xref_IDXRef] (idXRef) VALUES (N'') TRUNCATE TABLE [dbo].[xref_IDXRefData] TRUNCATE TABLE [dbo].[xref_MessageArgument] TRUNCATE TABLE [dbo].[xref_MessageDef] TRUNCATE TABLE [dbo].[xref_MessageText] TRUNCATE TABLE [dbo].[xref_ValueXRef] INSERT INTO [xref_ValueXRef] (valueXRefName) VALUES (N'') TRUNCATE TABLE [dbo].[xref_ValueXRefData]

You can download the Xref Tool here.