Oct 232007
 

After creating the .XLS to .CSV feature, I decided to expand my application to just create the Excel Spreadsheet as an XML, which is more useful for those who use BizTalk.

I’ve added the following functionality:

1.  Translate the 1st sheet only.

2.  Create the XSD Schema for the XML of the 1st sheet

3.  Create multiple XML files for each sheet in the .XLS

4.  Create one XML file for all sheets in the .XLS with each sheet as a child record of a parent node <Sheets>

If you’d like a copy, email me.

Here is a screen shot of the newest feature.

image

You can download it here:

Oct 192007
 

image

 

I continue to add features to my original ISA Control Number Randomizer for the BizTalk HIPAA adapter.  My latest feature allows automatic conversion from Excel spreadsheets to CSV files.  It is a nice little feature if you’re trying to consume .xls files using BizTalk.  Just define your comma delimited schema and consume the file. 

Current features include:

1. Randomize ISA Control Numbers for Files Already in Folder (even with multiple ISA/IEA)

2. Folder Watcher allows you to drop files into your source folder their ISA/IEA control numbers will automatically be randomized

3. Drag-n-Randomize files allows you to drop files into the application and they will be randomized and output in the destination folder you select

4. Excel to CSV converts .XLS to .CSV

Email me if you would like a copy.

Here is a screen shot of the new feature

image

Oct 152007
 

While messing with Code lists (http://msdn2.microsoft.com/en-us/library/aa577951.aspx) I was getting the following error:

Code list database error

Data Type mismatch in criteria expression.

Make sure the document has the correct standard, pills vcialis 40mg and a valid version. The CodeList database table name is constructed by joining the standard and the standard_version of the schema with an underscore.

<OK>

Code List Error

 

The issue is not with how the CodeList database table name is constructed, price for me it was the fact that the first column I had defined as an Number when it should have been defined as Text.

Oct 152007
 

A recent project I was developing I was required to pull data from either a file location or a FTP location, and based on the filename it was supposed to do something.

I started testing and I came across an XLANG/s error:

Shape name: Decide Type of Password Reset
ShapeId: 56e918c9-c009-4f4a-a722-a3f6c2f7dd55
Exception thrown from: segment 1, progress 15
Inner exception: There is no value associated with the property ‘FILE.ReceivedFileName’ in the message.

I had forgotten to change my code in the decision shape from testing just the File.ReceivedFileName to something more intelligent.

I started looking at how I could change the code. In this case I was pulling the data from an FTP location, so obviously, the FILE.ReceivedFileName did not exist in the message context. I thought of writing the code in an expression shape to be like this:

if(inputMsg(BTS.InboundTransportType)=="FILE") { filename=inputMsg(FILE.ReceivedFileName); } else { filename=inputMsg(FTP.ReceivedFileName); }

But I was not satisfied (am I ever?), I then looked at the XLANG/s operators and after reviewing the exists operator I came up with some different code:

if(FILE.ReceivedFileName exists inputMsg) { filename=inputMsg(FILE.ReceivedFileName); } else { filename=inputMsg(FTP.ReceivedFileName); }

Now there is no perceived benefit in using one way of extracting the filename compared to another, but it is just another arrow in the quiver.

Oct 112007
 

I was recently asked to create a secure FTP adapter for BizTalk.

I created it and tested it. I then went to create the install package.

The issue is I needed to determine where BizTalk is installed and put the assemblies in that directory and write where I put those assemblies into the registry.

Here are the steps to make the MSI ‘smart’

  1. Open the Launch Conditions by right clicking on the Setup project View – Launch Conditions
    Launch Conditions
  2. Right click on the Search Target Machine folder and click Add Registry Search
    Registry Search
  3. In the properties, add the following values
    (Name) BizTalk Install Path
    Property BIZTALKINSTALLPATH
    RegKey SOFTWARE\Microsoft\BizTalk Server\3.0
    Root vsdrrHKLM
    Value InstallPath

    We are going to use this value BIZTALKINSTALLPATH thru the rest of the process

  4. Now we need to add the entries to the registry. We do this by right clicking on Setup project View – Registry (like step 1) and click Import
    Registry Import
    and import the registry file created from the Adapter Registry Wizard
  5. Now edit the locations that have the path hard coded
    Registry Modification
  6. Now we need to add the directory where BizTalk is installed for us to put the assemblies there, in File System (right click Setup – View File System), right click and Add Special Folder
    Custom Folder
    with the following properties
    (Name) BizTalk Folder
    Always Create False
    Condition  
    DefaultLocation [BIZTALKINSTALLPATH]\
    Property NEWPROPERTY1
    Transitive False
  7. In your BizTalk folder, add the assemblies you want to install
  8. Now we want the MSI to not only install the adapter, but also make sure that it is in the adapter list. We do this by executing the following VB script code, we want to make sure that the strAdapterMgmtCLSID in the script matches the entry in the registry and add it to the Application Folder.
    File System VBS
    'AddAdapter.vbs 'Adds adapter to BizTalk strAdapterName = "sFTP" strAdapterComment = "FTP using SSL" strAdapterMgmtCLSID = "{AC6145E9-44C2-4C3F-AC3A-D207C9E99B87}" AddAdapter strAdapterName, strAdapterComment, strAdapterMgmtCLSID Sub AddAdapter(strAdapterName, strAdapterComment, strAdapterMgmtCLSID) Set objLocator = CreateObject("WbemScripting.SWbemLocator") Set objService = objLocator.ConnectServer("localhost", "root/MicrosoftBizTalkServer") Set objAdapterClass = objService.Get("MSBTS_AdapterSetting") On Error Resume Next Set objAdapterInstance = objService.Get("MSBTS_AdapterSetting.Name='" & strAdapterName & "'") If (objAdapterInstance is Nothing) Then On Error Goto 0 Set objAdapterInstance = objAdapterClass.SpawnInstance_ Else On Error Goto 0 End If objAdapterInstance.Name = strAdapterName objAdapterInstance.Comment = strAdapterComment objAdapterInstance.MgmtCLSID = strAdapterMgmtCLSID On Error Resume Next objAdapterInstance.Put_(0) If (Err.Number <> 0) Then Else End If End Sub

  9. Now we simply need to add it as a custom action, we right click the project again View-Custom Actions, we want to make sure it is done after the registry is made, so lets put this in the Commit, right click and browse to the Application Folder and select OK.

    Add Custom Action

    It should look like this

    image

You are ready to go!

    Oct 082007
     

    I have need for this occasionally so I thought I would publish the code that I have written.

    namespace StottIS.ChangeConfigration { public enum Active { Start, Stop } public class Adapter { public static void RecieveLocationStateChange(string ReceivePort, string ReceiveLocation, Active ActiveFlag) { BtsCatalogExplorer root = new BtsCatalogExplorer(); try { root.ConnectionString = "Server=.;Initial Catalog=BizTalkMgmtDb;Integrated Security=SSPI;"; //Enumerate the receive locations in each of the receive ports. foreach (ReceivePort receivePort in root.ReceivePorts) { if (receivePort.Name == ReceivePort) { foreach (ReceiveLocation location in receivePort.ReceiveLocations) if (location.Name == ReceiveLocation) { switch (ActiveFlag) { case Active.Start: location.Enable = true; break; case Active.Stop: location.Enable = false; break; default: location.Enable = true; break; } } } } root.SaveChanges(); } catch (Exception e)//If it fails, roll-back all changes. { root.DiscardChanges(); throw e; } } } }

    In an orchestration Expression Shape, or in your code outside of BizTalk you simply call this code:

    StottIS.ChangeConfigration.Adapter.RecieveLocationStateChange("Password Reset","Password Pickup",StottIS.ChangeConfigration.Active.Stop);

    Oct 032007
     

    I need to have a correlation set when an email comes in and when a file comes from an ftp site. The file name is not in the email. There is actually not anything that I can use to correlate between the two.

    I figured that I simply would create a pipeline component and correlate off that context property. So I whip up a pipeline component that is REALLY easy, I have a property in the propertybag that can be set in design/promote time.

    I use the following code:

    inmsg.Context.Write("PropertyValue", "http://PromotionPropertyValue.Property", Value); return inmsg;

    I create the correlation set and deploy away. However when I pull the file from the FTP site, the orchestration does not start up!

    I look at the subscriptions and this is what I see:

    Subscription Details

    Well, of course I have that promoted, I just did it in the code, so I look at the message that is suspended (cause it had no where to go).

    Not Promoted

     

    WHAT! The value is there, but it is not Promoted.

     

    I then went in and looked thru the methods available here and I saw Promote instead of Write. I changed the code to look like this:

    inmsg.Context.Promote("PropertyValue", "http://PromotionPropertyValue.Property", Value); return inmsg;

    I ran the message thru again and this is what I saw afterwards:

    Is Promoted

     

    Now my orchestration was kicked off and I am a happy camper. (Okay, not quite, the rest of the logic has to be tested now.)