May 222009
 
I found this method, I’ve used it a few times, so I’m going to put it here for my own use.
/// <summary>
/// Reads data from a stream until the end is reached. The
/// data is returned as a byte array. An IOException is
/// thrown if any of the underlying IO calls fail.
/// </summary>
/// <param name="stream">The stream to read data from</param>
/// <param name="initialLength">The initial buffer length</param>
public static byte[] ReadFully (Stream stream, int initialLength)
{
    // If we've been passed an unhelpful initial length, just
    // use 32K.
    if (initialLength < 1)
    {
        initialLength = 32768;
    }

    byte[] buffer = new byte[initialLength];
    int read=0;

    int chunk;
    while ( (chunk = stream.Read(buffer, read, buffer.Length-read)) > 0)
    {
        read += chunk;

        // If we've reached the end of our buffer, check to see if there's
        // any more information
        if (read == buffer.Length)
        {
            int nextByte = stream.ReadByte();

            // End of stream? If so, we're done
            if (nextByte==-1)
            {
                return buffer;
            }

            // Nope. Resize the buffer, put in the byte we've just
            // read, and continue
            byte[] newBuffer = new byte[buffer.Length*2];
            Array.Copy(buffer, newBuffer, buffer.Length);
            newBuffer[read]=(byte)nextByte;
            buffer = newBuffer;
            read++;
        }
    }
    // Buffer is now too big. Shrink it.
    byte[] ret = new byte[read];
    Array.Copy(buffer, ret, read);
    return ret;
}

May 152009
 

This is geared for single batches of transactions, be on the lookout for an upcoming tutorial on how to use the Batches functionality in BizTalk 2009.

This is actually a step by step tutorial on how to enable batching for outbound EDI transactions, where the input file needs to be split up from one single message but creating multiple transactions.

In my case I am going to take an invoice process. I am picking up a file from an FTP site with multiple rows, each row represents an invoice. Once all of the transactions are created, I need to release the batch.

The first think I need to do is create an orchestration that promotes the following context properties:

EDI.ToBeBatched
EDI.DestinationPartyId
EDI.EncodingType

Because I am going to be looping through a single message, I am unable to promote values in a loop, so I need to escape the loop to promote the values.

Create an orchestration that will be called from within the loop to send it to the Microsoft’s batching orchestration. In this case I am going to create a new project called InvoiceBatcher, and the in the project create an orchestration. Add the following references:

C:\Program Files\Microsoft BizTalk Server 2006\Microsoft.BizTalk.Edi.BaseArtifacts.dll
C:\Program Files\Microsoft BizTalk Server 2006\Microsoft.BizTalk.Edi.BatchingOrchestration.dll

Reference the EDI 810 project.

Setup objects and configuration for the orchestration

1. CorrelationType which contains the context properties

EDI.ToBeBatched
EDI.DestinationPartyId
EDI.EncodingType

2. Multi-part Message Type called EDIType representing the 810, also change the Type Modifier to Public (because we are going to be sending data to this orchestration outside of this project

3. EDIPortType which is a one way port type that points to the multi-part message type created in step 2.

4. Create a Correlation Set that is based on the CorrelationType created in step 1.

5. Create a message called OutEDIMsg that is based on the EDIType created in step 2.

6. Create a Port called Port and referencing the EDIPortType that was created in Step 3 and set it to Direct and direction set to Send.

7. Create an Int32 Orchestration Parameter called PartyNo with a direction of In

8. Create a Message Parameter called EDIMsg based on the EDIType created in step 2 with a direction of in.

9. Click on the Orchestration surface and change the Type Modifier to Public so it can be accessed by other orchestrations that will be referencing this assembly

Your orchestration view should look like this:

Orchestration Objects

The orchestration surface should look like this:

BatchPromote

The code in the Promote Context is this:

OutEDIMsg=EDIMsg;
OutEDIMsg(EDI.ToBeBatched)=true;
OutEDIMsg(EDI.DestinationPartyId)=PartyNo;
OutEDIMsg(EDI.EncodingType)=0;

On the Send EDI Message, send the OutEDIMsg and make sure that the Initializing Correlation Set is ‘CorrelationSet’

Deploy it to the BizTalk EDI Application

Next is to create the batch release process. The first thing we need to do is create a schema that represents the table in the management database.

Create a new project called PAM and create a schema called PAMRequest.xsd. Open the newly created schema in the XML editor, not the BizTalk Schema editor and paste the following code:

<?xml version="1.0"?>
<xs:schema xmlns:tns="http://PAM_Trigger" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://PAM_Trigger" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Request">
    <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="after">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element minOccurs="0" maxOccurs="unbounded" name="PAM_Control">
                      <xs:complexType>
                        <xs:attribute name="DestinationParty" type="xs:string" />
                        <xs:attribute name="EdiMessageType" type="xs:short" />
                        <xs:attribute name="ActionType" type="xs:string" />
                        <xs:attribute name="ActionDateTime" type="xs:string" />
                        <xs:attribute name="UsedOnce" 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="Response">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Success" />
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Deploy the project to the BizTalk EDI Application

Now to create an orchestration that will actually release the batch of transactions.

Create a new project called BatchRelease

Add a reference to the PAM assembly.

Create a new orchestration called ReleaseEDIBatch

Set the Type Modifier to Public so any other orchestration can reference it.

Create the following orchestration objects (from bottom to top)

PamResponseType (Multi-part Message Type)

PamRequestType (Multi-part Message Type)

PAMPortType (Request/Response Port Type)

TempXML (System.XML.XMLDocument)

PAMResponseMsg (PAMResponseType)

PAMRequestMsg (PAMRequestType)

PAMPort (Specify Later, Send-Recieve direction)

PartnerNo (Direction:In, Int32)

The Orchestration View should look like this:

PAMOrchestrationView 

The orchestration surface looks like this:

PAMSurface

The logic to create the PAM message in the message assignment shape is:

TempXML=new System.Xml.XmlDocument();
TempXML.LoadXml("<ns0:Request xmlns:ns0=\"http://PAM_Trigger\"><ns0:sync><ns0:after><ns0:PAM_Control DestinationParty=\""+System.Convert.ToString(PartnerNo)+"\" EdiMessageType=\"0\" ActionType=\"EdiBatchOverride\" ActionDateTime=\""+System.Convert.ToString(System.DateTime.Now)+"\" UsedOnce=\"0\" /></ns0:after></ns0:sync></ns0:Request>");
PAMRequestMsg.MessagePart=TempXML;

Deploy it to the BizTalk EDI Application

Now to create the actual Invoice orchestration, first reference the InvoiceBatcher and the BatchRelease assemblies. Create the multi-part message that represents the input message, and the port type to bring in the file from the ftp server. I created the following variables all as Int32:

ThisInvoiceNo (default value set at 0)

PartyNumber

InvoiceCount

OriginalInformixMsg (Informix810Type)

NewInformixMsg (Inforix810Type)

EDIMsg (InvoiceBatcher.Multi-part Message Types.EDIType)

InformixPort

The following Orchestration View represents the setup.

InvoiceOrchestrationView

The steps for this orchestration is to receive the file, count the number of invoices, set the party number, start looping while the current invoice number is less than the invoice count. In the loop, it increases the current invoice number, injects that number into the message. The map takes that newly created message and extracts the data from the input message and creates the 810 transaction. It then sends the transaction to be batched and continues creating new transactions. Once the loop completes, it releases the batch.

The orchestration looks like this:

InvoiceSurface

The Set Party Number expression shape has the following code:

InvoiceCount=(System.Int32)xpath(OriginalInfomixMsg.MessagePart,"count(//Invoice)");
PartyNumber=1;

The Inject Number message assignment has the following code:

ThisInvoiceNo=ThisInvoiceNo+1;
NewInformixMsg.MessagePart=OriginalInfomixMsg.MessagePart;
xpath(NewInformixMsg.MessagePart,"/File/Value/text()")=System.Convert.ToString(ThisInvoiceNo);

The map looks like this:

810map

The arguments to the Send EDI start orchestration shape (InvoiceBatcher.BatchPromote) are the following:

BatchPromoteArguments

The arguments to the Release Batch start orchestration shape (BatchRelease.ReleaseEDIBatch) are the following:

ReleaseEDIBatchArguments

The last thing of mention is to set up the send port for the ReleaseEDIBatch orchestration to bind to.

In the BizTalk EDI Application, create a request response send port set it to SQL and point to the BizTalk Management database. Sent the send and receive pipelines to DefaultXML

Set the following connection information as follows:

PAMPort

Bind to it and you are ready to go!

May 112009
 

I remember searching for this T-SQL statement a few months ago, so time to put it on the blog.

--GET FIRST DAY OF LAST MONTH 
SELECT DATEADD(MM, DATEDIFF(MM, '01/01/2000', DATEADD(MM, -1,GETDATE())), '01/01/2000') as [Date of First Day of Last Month]
--GET LAST DAY OF LAST MONTH 
SELECT DATEADD(SS,-1,DATEADD(MM, DATEDIFF(MM,'01/01/2000',GETDATE()),'01/01/2000')) as [Date of Last Day of Last Month]

May 072009
 

Since it is using OLE, you can connect to whatever data source you want.

Below is a brief list of sources to connect to:

Data Source Sample Connection String
UDL File Name={path.udl}
Sybase Provider=Sybase.ASEOLEDBProvider;Server Name=thisservername,5000;Initial Catalog=thisdb;User Id=thisusername;Password=thispassword
SQL Server Provider=sqloledb;Data Source=thisservername;Initial Catalog=thisdb;User Id=thisusername;Password=thispassword;
Provider=sqloledb;Data Source=thisservername;Initial Catalog=thisdb;Integrated Security=SSPI;
Oracle Provider=msdaora;Data Source=thisdb;User Id=thisusername;Password=thispassword;
Provider=msdaora;Data Source=thisdb;Persist Security Info=False;Integrated Security=Yes;
MySQL Provider=MySQLProv;Data Source=thisdb;User Id=thisusername;Password=thispassword;
Informix Provider=Ifxoledbc.2;User ID=thisusername;password=thispassword;Data Source=thisdb@thisservername;Persist Security Info=true
FoxPro Provider=vfpoledb.1;Data Source=c:\directory\thisdb.dbc;Collating Sequence=machine
Firebird User=SYSDBA;Password=thispassword;Database=thisdb.fdb;DataSource=localhost;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0
Exchange oConn.Provider = “EXOLEDB.DataSource” oConn.Open = “http://thisservername/myVirtualRootName”
Excel Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\thisspreadsheet.xls;Extended Properties='”Excel 8.0;HDR=Yes;IMEX=1″‘
DBase Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\directory;Extended Properties=dBASE IV;User ID=Admin;Password=
DB2 Provider=IBMDADB2;Database=thisdb;HOSTNAME=thisservername;PROTOCOL=TCPIP;PORT=50000;uid=thisusername;pwd=thispassword;
Access Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\directory\thisdb.mdb;User Id=admin;Password=;

 

You can access a lot more sources. Here is a list of sources you can check against:

Connection Strings

Another thing is that if you wanted to put a where clause in your statement to limit the data that is being returned, the forth argument in the Database Lookup functiod is the place to put it. BEWARE, you need to make sure that the column that you are matching with the first argument is correct.

whereclause

May 062009
 

Here is the functiod description for the Database Lookup functiod:

Use the Database Lookup functoid to extract information from a database and store it as a Microsoft ActiveX Data Objects (ADO) recordset. This functoid requires the following four input parameters in this order: a lookup value, a database connection string, a table name, and a column name for the lookup value.

Let’s review the arguments again

1. Lookup Value
2. Database Connection String
3. Table Name
4. Column Name

I did not want to create a view, as I don’t have permissions to add objects to that database, but I could not simply get the value from one table, I needed to join two tables together.

The answer was SIMPLE!

Underneath the covers, the statement that is being called is:

using (OleDbCommand command = new OleDbCommand("SELECT * FROM " + table + " WHERE " + column + "= ?", helper.Connection))
{
...
}

WAIT A MINUTE! I am not limited to only a table name in the table variable of the OleDbCommand method, I can put an entire join statement in argument 3 of the Database Lookup functiod.

Here is what I had for the arguments:

dblookup_arguments

The arguments without the single ticks

1. Source Data

2. Connection Information (in this case I just pointed to a udl file): ‘File Name=D:\Eric\Warehouse\Schemas\connection.udl’

3. Table name, which has the join: ‘EdiPartnerContacts INNER JOIN bts_party ON EdiPartnerContacts.PartyId = bts_party.nID’

4. Column that has the input value: ‘nvcName’

So in essence I had created the following query using the DBLookup functiod

SELECT     *
FROM         EdiPartnerContacts INNER JOIN
                      bts_party ON EdiPartnerContacts.PartyId = bts_party.nID

WHERE       nvcName=?

I then pulled the Email column from the data in a Value Extractor that was connected to the Database Lookup functiod

dblookup_map