donderdag 3 oktober 2013

SQL query: hourly processed messages for BizTalk ports

One of my clients wanted to know how many messages were processed on a certain BizTalk port.
To show some statistics, I created a SQL query that lists a message count of the messages processed per hour for a certain Port.

Here's the SQL query I used:
use BizTalkDTAdb

SELECT top 200
      a.[Event/Direction],
      a.[Event/Port],
      CONVERT(VARCHAR(10), a.[Event/Timestamp], 111)   as [date],
      DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
      COUNT(*) as [Count]  
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID
WHERE a.[Event/Port] <> 'PortSqlMonitor'
AND a.[Event/Port] = 'BizTalkPort'
AND a.[Event/Direction] = 'Receive'
AND a.[Event/Timestamp] > DATEADD(day, -3,GETDATE())
GROUP by  a.[Event/Port], CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp]), a.[Event/Direction]
ORDER BY CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp])


dinsdag 17 september 2013

Using Fiddler for viewing exchanged messages with BizTalk

I was trying to view the SOAP messages and headers that were actually exchanged with a certain webservice.
To do so, I used Fiddler2... available from http://fiddler2.com/.

Fiddler2 will allow you to monitor traffic when using HTTP, SOAP or WCF ports in BizTalk. However, Fiddler will not trace any messages sent to endpoints by BizTalk by default as it does not use WinInet. To overcome this issue, you'll need to configure a proxy to allow Fiddler to intercept the messages.
Note that these proxy settings will need to be removed when Fiddler is not running. Because otherwise all traffic through this proxy will not be received by anything.

In the send port of BizTalk go to the Configuration settings. Open the tab 'Proxy' to configure the necessary settings. Now you should change following settings:
  • Server: 127.0.0.1
  • Port: 8888

That's is all you need to do. Now open fiddler2 and process a message. If all is configured correctly, you should see the exchanged messages coming through fiddler.


vrijdag 19 juli 2013

BTUG.be is born

I would like to make a small announcement... today we publicly announced the birth of a brand new Belgian BizTalk User Group, called BTUG.be! We were planning something like this for quite some time now, and you even might have picked up some rumours about it in the past. But as of today it is official, Belgium now has its very own BizTalk User Group.



I'm proud to say that I'm one of the co-founders of this BTUG.be. Together with Sam Vanhoutte we have started this initiative under excellent guidance of Lex Hegt, who shared his knowledge he gained with the Dutch BizTalk User Group.
And to start out, we wanted to make it a joined collaboration of the Belgian BizTalk Community. In this spirit we are glad that also i8c decided to support the initiative and Sven Van den brande agreed to fulfill our board of the BTUG.be. So that makes the all new Belgian BizTalk User Group will be worked out as a joined effort by Cnext, Codit and i8c with in the board at startup myself, Sam Vanhoutte and Sven Van den brande.
I'm already looking forward on having a great time working together with these guys organising all upcoming events.

A very first launch event is being planned to have place in november. For keeping up to date on all matters of this BTUG.be, I'll recommend you subscribe to the mailing list on the website http://www.btug.be and even keep a close eye to the website for all announcements. And certainly don't forget to follow our twitter account @BTUGbe.

And last of all, I would like to call out for sponsors... As the entire organization will be driven on volunteer work. So to cover all the necessary costs we will need to make for hosting events, catering, travel expenses in case of international speakers, etc. we are on the lookout for sponsors to keep it fun and free for everyone. You can always contact us on any questions or to share your interest for sponsoring using this entry form on the website.

maandag 27 mei 2013

BizTalk blog posts 02/2013 - 05/2013

The past time I haven't posted a lot on this blog, especially not much blog posts on BizTalk subjects. This doesn't mean that I haven't done any BizTalk related blog posts, I just haven't done it on my personal blog for a while... I've mostly posted on my companies blog, where I am the sole contributor and administrator.

But for all of you loyal readers of my personal blog, I thought it would be nice to give an overview of all of my latest posts. I'm thinking of doing this every once in a while to keep all of you updated on ALL of my posts.

So here goes for the period till May 2013...
Hope you enjoy these blog posts!

woensdag 17 april 2013

Became an official BizTalk360 Product Specialist

Yesterday, BizTalk360 introduced the whole new title "BizTalk360 Product Specialist" on their blog. Because of the constantly increasing popularity of the monitoring tool BizTalk360, to streamline the process the new title and badge where introduced. The idea is to honour individuals who have gained adequate knowledge in installing, configuring and implementing BizTalk360 solution at customer sites.



The only way of obtaining this title is by receiving the confirmation from the BizTalk360 team itself. I myself filed a request immediately... And I am proud to announce my request got approved. Now I have the honour to officially call myself a "BizTalk360 Product Specialist"!

As you might have seen, I already placed the official badge at my own blog on the column on the right. I also updated my LinkedIn profile accordingly.

donderdag 11 april 2013

Guide to a BizTalk High Availability Server Environment

I just discovered a series of blog post that I just had to share with all of you... It's a complete guide to set up a High available BizTalk environment.
A perfect walthrough from start to finish to obtain a BizTalk High availability environment.

The guide covers 7 blog posts in total:


Congrats to René Brauwers on the great guide for setting up a High available BizTalk environment.

CU1 for BizTalk 2013... using Microsoft Update!

The Microsoft Team has just announced the release of the very first Cumulative Update For BizTalk 2013.

But an even more surprising news is that it is available using Microsoft Update!... This is the very first time a BizTalk Update can be obtained with Microsoft Update, as before it had to be done by downloading it manually through the hotfix download. An amazing improvement if you ask me, making it a whole lot easier for administrators.

The update will still be available through the hotfix download though.


The Content of the Cumulative Update


The very first CU for BizTalk 2013 brings 3 fixes:
  • User cannot perform certain database-related operations in BizTalk Server 2013
  • BAM tools cannot be configured in a multi-node BizTalk Server 2013 environment
  • The vertical scroll bar on the target schema does not work correctly when you use Visual Studio to design a BizTalk Server 2013 map


How to install using Microsoft Update


The following steps are applicable if you have Microsoft Update enabled in the machine where BizTalk Server is installed.
  1. Log into a server where BizTalk Server is already installed, and Microsoft Update is enabled
  2. Check for windows update
  3. Notice available fixes for BizTalk Server under optional updates
  4. Select the update and install
(Perform the above operations in each of the BizTalk nodes)

The original announcement can be found here.

donderdag 21 maart 2013

Microsoft BizTalk Server 2013 available on MSDN

It took a while, but here it is... BizTalk Server 2013 is now in RTM (Manufacturing)! as of now you can download BizTalk 2013 from MSDN, and it will be available for purchase in April.

Besides all kinds of new features, the new version of BizTalk also brings us a whole new licensing model. At least it is new in terms of BizTalk pricings... it will be a per-core licensing model, just like the SQL Server licensing.

A more detailed info can be found on my companies blog post.
the official announcement of the BizTalk team, can be found in this post on their blog.

vrijdag 8 maart 2013

Error on Calendar/Contacts sharing in Outlook

I 've encountered a problem sharing Contacts from within Outlook. We were trying to share a contactgroup where both parties are from the same company, so on the same Office365 license.
However, on trying to send out a share request we stumbled upon following error:

"Problem: Your organization's policy does not allow you to share with this recipient."

At first it seemed that there should be anything wrong with the organizations settings made in office365. But nothing strange was configured there... Setting the permissions to the other mail addres did seem to work. What got me questioning there would be anyhting wrong in the configurational settings.

It turned out there was a difference when trying to share the contact or setting the permission. And after realising this, even sharing the contact worked just fine... without changing any of the configuration settings!

Solution


What turned out to be the problem than?

when the user started typing the recipient name in the "To" box of the "Share invitation" dialog, the recipient was picked up by autocompletion. However, the autocompletion picked up the recipient from the Contacts instead of from the Global Address List. So the name of the recipient appeared as "User Name <user@company.com>", where it had to be "User Name" when you choose from the Global Adress list.

So on sending the email invitation to share the contact list, Microsoft Exchange considered the recipient to be outside the organization. Which resulted in the above error message of course.


woensdag 27 februari 2013

Change SQL backup location

During the installation of a SQL instance, you'll have the ability to define some folders
These locations can be defined:
  • User database directory
  • User database lof directory
  • Temp DB directory
  • Temp DB log directory
  • Backup directory


Within SQL Server Management Studio you have the ability to change the default location for your Data and Log files for all new databases. Just  right click on the server name and select properties, navigate to the Database Settings page. Here you can find a section Database default locations for changing the data and log directories.



But if you search through all of the pages under Database Settings you will not find anything that shows the default backup directory.  To find this we need to look in the registry.

Open the registry tool REGEDIT and navigate to following key:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer

Or something similar for your instance of SQL server. The registry key BackupDirectory is the one you'll need to change to set another default Backup Directory.


Changing the registry can also be done with a T-SQL query command. To do so, you'll be using the extended stored procedures XP_REGREAD and XP_REGWRITE.

Reading the falue in registry can be done by using this command:

DECLARE @BackupDirectory VARCHAR(100)
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
  
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer'
,
  
@value_name='BackupDirectory'
,
  
@BackupDirectory=@BackupDirectory 
OUTPUT SELECT @BackupDirectory

This will result in something similar as this:


Changing the default folder can be done by using the following command

EXEC master..xp_regwrite
     
@rootkey='HKEY_LOCAL_MACHINE'
,
     
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.BTSTPTST1\MSSQLServer'
,
     
@value_name='BackupDirectory'
,
     
@type='REG_SZ'
,
     
@value='C:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSTPTST\MSSQL\Backup'


key file authentication with the BizTalk SFTP adapter

Authentication on an SFTP location can be done with simple username/password credentials… However another way to authenticate the sender is by using a Key Authentication file. But how can this last authentication method be used inside the BizTalk SFTP adapter?

the BizTalk SFTP adapter we used to achieve this is the  freely available SFTP adapter on Codeplex. Just download the adapter and install it. And don’t forget to add the adapter in the BizTalk admin console of course.

Now then let’s set up the port in BizTalk. In my example I ‘ve configured a receive location, but obviously the same goes for the send port configuration.

First of all choose the created adapter in the biztalk admin console, as shown below.

Port configuration

To set up the proper configuration for the SFTP port, just press the ‘Configure’-button.

Configure

These parameters need to be set:
  • Schedule: define a timely schedule how often you want to poll the SFTP location (receive location only)
  • File Mask: set the correct file mask
  • SSH Host: the host address of the SFTP location
  • SSH Identyfile: Select the Key authentication file on disk
  • SSH Identyfile Passphrase: the password of the selected key authentication file
  • SSH Remote Path: the path on on the SFTP location
  • SSH User: the user name to authenticate on the SFTP location
Seems pretty straight forward, doesn’t it?
Well there are some things you ‘ll need to take into account to make sure the connection can be made…

Make sure the identyfile type is supported

You’ll need to make sure the identyfile type is supported by the library. The supported versions can be found here. As this is the library which is used by the bizTalk SFTP adapter.

Make sure the identyfile can be recognized

In my case for example, I got an *.ppk file as authentication file. But as it turned out, this wasn’t recognized.
The error message you git is this:
invalid privatekey: D:\tempkey.ppk

Which in the end seemed a bit misleading, as the problem wasn’t the private key for the authentication file. It was just the *.ppk file that wasn’t supported.
To make the key authentication file working, I had to convert the file to an OpenSSH key file. This can be done by using PuttyGen for example, and exporting the authentication file as an OpenSSH key file.

Don’t use both password parameters

Also make sure you only use the intended parameter SSH Identyfile Passphrase, and leave the SSH password blank.
In case both password fields are filled, the SFTP adapter will try to authenticate by the username/password credentials stated. So it won’t offer the specified identyfile to authenticate.




I orignally posted this on my companies blog: http://blog.cnext.eu/2013/02/19/using-key-file-authentication-with-the-biztalk-sftp-adapter/

vrijdag 1 februari 2013

SQL Server Compact 4.0 Tooling in Visual Studio 2010

I developed a program which uses SQL Server Compact. Initially it was developed creating SQL Server COmpact 4.0 databases. Now after reinstalling my laptop, I tried to open the database, but it appaered I was missing the necessary tooling for SQL Server Compact 4.0, I could only open/create SQL Server Compact 3.5 databases.



I've immediatly installed the SQL Server Compact 4.0 runtime, which can be found here.
But this however didn't seem to be enough... I still couldn't open my created SQL compact database.

What I needed to do, was install some additional software using Microsoft Web Platform Installer 3.0.
Make sure you install these packages:
  • Microsoft Visual Studio 2010 SP1
  • Microsoft Visual Studion 2010 SP1 Tools for SQL Server Compact 4.0


Note that selecting and installing the Visual Studio 2010 SP1 or the Visual Web Developer Express 2010 SP1 also installs the SQL Server Compact 4.0 components that are needed for the tooling support. These components are called as Visual Studio 2010 SP1 Tools for SQL Server Compact 4.0 and SQL Server Compact 4.0. The Web Platform Installer automatically detects whether these components are already installed on the machine, and if they are not installed, the Web Platform Installer will install them. There is no need to separately select the SQL Server Compact 4.0 components in the Web Platform Installer.

The installer for visual studio SP1 can also be found here.


maandag 7 januari 2013

Error: The self-extracting zip file is part of a multidisk zip file

I tried to install the Cumulative update package 14 for SQL Server 2008 R2.

After requesting the update on http://support.microsoft.com/kb/2703280, I got an email containing a link to download the file 449383_intl_x64_zip.exe. After downloading the file an trying to run the self extracting zip file, I got following error:

This self-extracting zip file is part of a multidisk zip file. Please insert the last disk of the set.


There is no other option than pressing the OK-button.And after pressing the OK-button 3 times, I got following error message:


An error occured while unzipping. One or more files were not succesfully unzipped. The error code is 110.


The solution for this seemed to be to just download the file from the given link in my email again.
After this I could succesfully run the self-extracting zip file an install my cumulative update package.





dinsdag 18 december 2012

BizTalk deployment - How do YOU do it?



For the deployment of BizTalk applications we use a custom made tool within my company Cnext. Now I was curious to have some feedback from the community on the deployment of BizTalk applications. How do you handle the biztalk deployments at your company?

Our deployment manager tool is based on the idea to store all used objects in a database model (BizTalk artifacts, but also stuff like MSMQ, file locations, SQL objects, etc. …). Most of these objects are added using auto discovery of the BizTalk databases, so manually adding of objects is reduced to a minimum. Above all, BizTalk artifacts can have a different configuration (binding) defined per environment (test, dev, prod, …).
It also allows you to define all dependencies. Again most of these dependencies will be defined by the tool automatically.

This way of working makes it possible for the tool to define which actions need to be taken to deploy a certain application (or just a part of the application or only some objects). The deployment manager tool will define which objects need to be removed and redeployed (also unenlisting/disabling en starting/enabling artifacts will be done by itself). As a result, using the tool will allow us to deploy much faster, because the objects to redeploy are reduced to an absolute minimum and no complete redeploy is needed (like BizTalk Deployment framework does for example).

Another very useful and much used functionality is the possibility to define complete business/functional flows, including some generic components. This makes sure you can also deploy or redeploy a complete (new) flow (like an order flow for example) by itself, including all necessary objects (as well receive as send ports, file locations, etc. …).

The deployment can be done cross BizTalk applications. So the separation in applications is no longer deployment dependent.

The most important part is to set the database model correct and keep it this way… this will guarantee a much easier and faster deployment, where each environment has its own version of the current deployed objects.

Please answer following questions:

  1.      Is your BizTalk deployment automated (BizTalk deployment framework, custom scripting with MSBuild or BTSTask, powershell scripts, etc. …), or do you just do manual deployment using MSI and binding files?
  2.        Which deployment tools or scripts are you using, or have you used before? And what are your thoughts of these tools (benefits and complaints)?
  3.        What do you think of a tool like our custom deployment manager tool (using autodiscovery, etc. …)?


Thanks for your replies.
 When there is enough feedback, I'll devote a new post on my analysis.



woensdag 28 november 2012

SQL Server Configuration Manager - Error 0x80041010

When opening the SQL Server Configuration manager (for SQL Server 2008 R2 in my case), I kept getting following error:

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]


The solution to resolve this turned out to be quite simple. Just open a command prompt and execute following command:

C:\Program Files\Microsoft SQL Server\100\Shared>mofcomp "C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"

Depending on the SQL version installed the path to the file could also be something like C:\Program Files\Microsoft SQL Server\90\Shared\sqlmgmproviderxpsp2up.mof

woensdag 14 november 2012

Blog admin of my companies blog

First of all, I would like to thank all of my regular reader and hope you've al enjoyed my blog posts for almost 2 years now. I already announced in one of my previous posts some time ago that I would join the contributors of the biztalk admin blog at http://www.biztalkadminsblogging.com/ .

Today, I'm proud to announce that there is another brand new blog I will be contributing to on BizTalk and Azure related news, problem solvement, etc.... My companies (Cnext) blog (http://blog.cnext.eu) . Well actually, the blog already existed quite some time (even longer than my own blog :) ), but there wasn't any new posts for over 2 years. Most recently I became the new administrator of the Cnext blog and had the task to revive it. After 1 month, 3 completely new posts, and still some blog posts in the making, it is ready to be shared with all of you... see for yourself at the Cnexts revived blog.

The first thing I did is restyle the entire blog to match the current branding of Cnext. And after publishing some new material, I can say that it is ready to be read!

Now go and check it out.. and keep following all of my blog posts.




More info on cnext can be found on http://www.cnext.eu. And as mentioned the blog will be situated at http://blog.cnext.eu... keep an eye out for all new material to be added soon!

woensdag 7 november 2012

Biztalk 2013 Beta released

Microsoft finally released a beta version of the new biztalk, wich can be found here.
The first thing that should be noticed is that Microsoft decided to change the next versions name from Biztalk 2010R2 to Biztalk 2013. This has some very important consequences… It indicates the next version to be a major release, which implies a much longer support from Microsoft.
Microsoft’s support lifecycle policy says that products will have 5+5 years (mainstream+extended) support. However, that applies to major versions. If the product would still be BizTalk Server 2010 R2, it will not get an extended support lifecycle end date.

So in conclusion, having the next Biztalk version being a full release is an important sign to the market, it implies a much longer support lifecycle for the product.


Biztalk 2013 will have following new features:

  • Integration with Cloud Services: messages using different relay endpoints hosted on Azure.

  • RESTful services: provides adapters to invoke REST endpoints as well as expose BizTalk Server artifacts as a RESTful service.

  • Enhanced SharePoint adapter: the need for dependency on SharePoint farms has been removed, while still providing backward compatibility.

  • SFTP adapter: added an SFTP adapter that will enable sending and receiving messages from an SFTP server.

  • ESB Toolkit integration: ESB Toolkit is now fully integrated with BizTalk Server. Also, the ESB Toolkit configuration experience is vastly simplified to enable a quick setup.

  • Dependency tracking: dependencies between artifacts can now be viewed and navigated in Admin console.

  • Improvements in dynamic send ports: the ability to set host handler per adapter, instead of always using the default send handler of the adapters.

woensdag 24 oktober 2012

Exception handling in BizTalk orchestrations explained

When creating BizTalk orchestrations, we also have some building blocks available to do exception handling. In this blog I'll try to give a very brief explanation of the available building blocks for your orchestration. I won't describe everything in detail, because this would lead us to far (and would give one massive blog post).

For exception handling in orchestration there are 3 shapes available:
  • Throw Exception
  • Scope
  • Compensate

I want go in much details for the Scope and Compensate shapes. If you need more detailed description on how Exception handling can be used with these 2 shapes I would suggest reading this blog from Richard Seroter. And if this still doens't answers all of your questions, let me know... than I can devote another post with a more detailed explanation on some aspects of the exception handling in orchestrations.

Throw Exception

Firstly we have the 'Throw Exception'-shape. this one is the simplest of all shapes for exception handling.
Frankly, it does nothing more than it's name suggests... It just throws an Exception, nothing more fancy. To achieve this you'll need to follow these three simple steps:
  1. Define a variable with the type set to any Exception class you prefer. In below example I've created an exception variable of type Microsoft.Biztalk.Message.Interop.BTSException


  2. Note: The Report To Analyst property in the screenshot exists on many shapes. The property however has no influence on the behavior of your orchestration. I merely is a property used to integrate withe the Orchestration Designer for Business Analysts (ODBA) Visio plugin. If set to false, that shape will be considered as a low level detail that won't be visible in the Visio Designer.

  3. Instantiate the variable and set any values according to the occured error
  4. Now use the Throw Exception shape and configure it ro use the Exception object you've created



Scope

Scopes are used for three reasons
  1. to configure transaction (long running or atomic)
  2. to handle exceptions
  3. to trigger compensating logic
For more info on the transaction types, I'll refer to this blog from Sandro Pereira.

A scope shape can have one or more Exception handling blocks and/or one Compensation Block. The Transaction type you've chosen for your scope will define which of these blocks can be added. An atomic scope can only have a compensation block, and a scope configured with the transaction type non can only have an Exception block. While a long running scope can have both blocks.
For an overview of the possibilities, check the below screenshot.

Note that there could be more than one Exception block, but only one Compensation block added to a Scope

Exception handling block

The Exception handling block is used to catch exceptions that occur inside the scope the block belongs to. The Exception Object Type in the properties of the exception block will define which Exceptions types will be handled by this Exception block.


When an exception occurs, the engine will check for the closest exception block that can handle this type of exception. The consideration of which exception block will be called is determined sequentially, just like in .NET. If no suitable exception handler is found, the default exception handles is triggered, and this means that the compensation blocks for all nested scopes will be called, after which the exception is rethrowed and the orchestration will become suspended.

Compensation block

Compensation is a way to correct or undo logical piece of work that have previously committed. An exception can occur in the orchestration afther succesfully executing all logic in the scope. In this case, the process might be in a state where it is logically incorrect. So the action already performed in a transactional scope need to be compensated... this can be achieved using a compensation block
If no compensation block is added, the default compensation will be performed. this means calling all compensation blocks of the nested scopes, starting with the most recently completed ones, just as with the default exception handler.

Compensate

The compensate shape can only be used inside of an exception handler. The explicit use of this compensate shape, allows us to specify the order of compensation execution ourselfs. So this can be used in cases the default order (starting from te most recently completed ones and working its way back) doesn't fit.
1 compensate shape can only trigger 1 compensation block of a transaction. So the order must be defined using multiple compensate shapes, calling the compensation blocks in the desired order.



Remarks

  • Keep in mind that the default Exception handler, if triggered, will initiate the compensation by calling all compensation blocks for the nested scopes. Compensation blocks of the nested scopes will be called starting with the most recently completed scope and work its way back.

    But whenever you catch an exception using the Exception handler block, then you must explicitly do the compensation.


  • If no custom compensation block has been added, the default compensation will be performed. Just like with the exception handler, this will call the compensation blocks of any nested scopes starting with the most recently completed scope.


  • A Compensate shape can only be used inside an Exception handling block or another Comensation Block. When calling the compensate of the current scope, the default compensatino handler will be triggered instead of the compensation code defined by the compensation block.

woensdag 17 oktober 2012

Error on attaching DB

When trying to reattach a database I kept getting following error:

In the SQL log I could find below error. The same error was given when trying to attach the DB with a T-SQL statement.

Msg 5120, Level 16, State 101, Line 3 Unable to open the physical file "E:\Program Files\Microsoft SQL Server\MSSQL10_50.BTSISDEV1\MSSQL\DATA\BizTalkMsgBoxDb.mdf". Operating system error 5: "5(failed to retrieve text for this error. Reason: 15105)".

After several attempts on how to solve this I finally found the solution.
Turned out, all I needed to do was run the SQL Server Management Studio as an Administrator.
How can it be that simple, yet has me looking for a solution for over an hour :).

dinsdag 9 oktober 2012

BizTalk: using macros in file names

BizTalk allows you to use several macros for defining a file name in a biztalk send port.

The following table lists the supported macros and describes how the File send handler replaces them.

Macro name Substitute value
%datetime%Coordinated Universal Time (UTC) date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).
%datetime_bts2000%UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).
%datetime.tz%Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).
%DestinationParty%Name of the destination party. The value comes from the message context property BTS.DestinationParty.
%DestinationPartyQualifier%Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.
%MessageID%Globally unique identifier (GUID) of the message in BizTalk Server. The value comes directly from the message context property BTS.MessageID.
%SourceFileName%Name of the file from which the File adapter read the message. The file name includes the extension and excludes the file path, for example, Sample.xml. When substituting this property, the File adapter extracts the file name from the absolute file path stored in the FILE.ReceivedFileName context property. If the context property does not have a value—for example, if a message was received on an adapter other than the File adapter—the macro will not be substituted and will remain in the file name as is (for example, C:\Drop\%SourceFileName%).
Aa578022.note(en-us,BTS.20).gifNote
Correct implementation of this macro requires that the output message is the same message as the received message.
%SourceParty%Name of the source party from which the File adapter received the message.
Aa578022.note(en-us,BTS.20).gifNote
Correct implementation of this macro requires that the output message is the same message as the received message.
%SourcePartyQualifier%Qualifier of the source party from which the File adapter received the message.
Aa578022.note(en-us,BTS.20).gifNote
Correct implementation of this macro requires that the output message is the same message as the received message.
%time%UTC time in the format hhmmss.
%time.tz%Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).

NOTE

The File send handler does not replace the macros with a value if any of the following are true:
  • The corresponding system property is not set.
  • The macro is misspelled.
  • The value for the macro contains symbols that are not valid in the file name.

Using Macros in SMTP Headers

There are however some restrictions when using the macros in SMTP headers.
See this post for more details on this matter.

Below is a short overview of the use of macros in SMTP headers.

Macro Description For use with To For use with CC For use with Subject
%MessageID%Globally unique identifier (GUID) of the message in BizTalk Server. The value comes from the message context property BTS.MessageID.NoNoYes
%datetime_bts2000%UTC date time in the format YYYYMMDDhhmmsss, where sss means seconds and milliseconds (for example, 199707121035234 means 1997/07/12, 10:35:23 and 400 milliseconds).NoNoYes
%datetime%UTC date time in the format YYYY-MM-DDThhmmss (for example, 1997-07-12T103508).NoNoYes
%datetime.tz%Local date time plus time zone from GMT in the format YYYY-MM-DDThhmmssTZD, (for example, 1997-07-12T103508+800).NoNoYes
%time%UTC time in the format hhmmss.NoNoYes
%time.tz%Local time plus time zone from GMT in the format hhmmssTZD (for example, 124525+530).NoNoYes
%SourceParty%Name of the source party from which the File adapter received the message.NoNoYes
%SourcePartyQualifier%Qualifier of the source party from which the File adapter received the message.NoNoYes
%DestinationParty%Name of the destination party. The value comes from the message context property BTS.DestinationParty.YesYesYes
%DestinationPartyQualifier%Qualifier of the destination party. The value comes from the message context property BTS.DestinationPartyQualifier.NoNoYes


Source: http://msdn.microsoft.com/en-us/library/aa578022(v=bts.20).aspx