I was working on tracking down an issue with MDNs not being received for some AS2 communications. So I decided to enable the AS2 status reporting capabilities in BizTalk. I needed to configure BizTalk so that the BAM Tools functionality was available in order for the AS2 status reports to be available. During the process of using the BizTalk configuration wizard I received an error, which is given below:
"BizTalk 2006 Configuration: Microsoft SQL Server 2005 Data Transformation Services (DTS) for BAM Archiving is not installed on the local machine. Please install Microsoft SQL Server 2005 Integration Services. (Microsoft.BizTalk.Bam.CfgExtHelper.ToolsHelper" … Additional information: “Could not load file or assembly ‘Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0”…
This error occurred when trying to configure the BAM Tools and I was specifying the database server names and the names of the databases. A red icon with an "X" occurred to the left of the database name. If you did not know, you can double click on the red icon on all of the pages of the BizTalk configuration wizard to see the error.
After looking on the internet I came across two blog posts (http://blogs.msdn.com/mattlind/archive/2006/08/30/730575.aspx, http://geekswithblogs.net/andym/archive/2006/11/27/99246.aspx) that gave me a few hints on what needed to be done but I still felt there were gaps in documentation and the blog posts so I wanted to give a little more information here for other people so that they would not need to come up with their own ideas of what needed to be done. I will also be referring to the multi-server BizTalk installation guide which can be found among the guides here: (http://www.microsoft.com/downloads/details.aspx?FamilyID=df2e8a88-fb23-49a4-9ac7-d17f72517d12&DisplayLang=en). The AS2 and EDI Status Reporting features of BizTalk Server 2006 R2 do provide a huge help in doing diagnostics of EDI interchanges and AS2 communications, but the process of enabling this feature is complicated.
A multi-server install usually means that BizTalk is on one (or more) servers and the SQL Server engine components are on some other (one or more) servers. In the BizTalk multi-server install guide, if you are trying to setup BAM Tools (one of the prerequisites for the AS2 Status reporting), it recommends that you install SQL Server Integration Services (SSIS) on the BizTalk box. This is wrong and could represent a license violation if you actually did this. This is one solution but there is actually a different solution which is better. BTW, the BAM install guide does not mention anything about the AS2 Status reporting so you can just skip this document if you are trying to setup the AS2 reporting functionality.
Although the error message indicates that the problem is a DLL named Microsoft.SqlServer.ManagedDTS is missing, this does not mean that something about the DTS functionality outside of SSIS is not functioning properly. This refers to a DLL that is installed on the SQL Server box relating to SSIS but not part of it that also needs to be on the BizTalk server. If you open up the SQL Server system and look in the GAC (at c:Windowsassembly), you will see that this DLL exists on a SQL Server where the "Workstation Tools" have been installed. So all that is required is that you install the SQL Server client tools on the BizTalk server which in the SQL installer are designated as "Workstation Tools". BTW, there is not a way to export a copy of the DLL from the GAC and I would advise against trying to individually copy this DLL out of a CAB from the SQL install because there are also additional DLLs that may need to be on the BizTalk server as well. It is important to note that it is not enough to just have the Workstation Tools installed only on the SQL Server itself. As one of the previous posters mentioned, you should select all of the Workstation Tools components on the install on the BizTalk server. This may require that you insert the install media into the BizTalk server or that you copy the installation software over to the BizTalk server.
In Andy Morrison’s post, he mentioned he had received an error when installing the SQL client tools (Workstation Tools), regarding OWC11. This is a variation on the install of the client tools. You could get other errors too – the key is to ensure the client tools run through successfully. Right before the client tools are installed you will see a window that goes through a series of prequisite checks for the SQL client tools install and you may need to ensure all of these tests pass with a warning or are successful before the client tools install will work. When I ran the client tools on the BizTalk server I did not receive an error so you may not need to reinstall OWC11 and you may not need to do a repair on the BizTalk installation. Ok, there are just a few more steps.
Then after you have run the Workstation Tools on the BizTalk server you will need to close the BizTalk configuration wizard and re-open it in order for the BAM Tools page to successfully see the Workstation Tools and connect to SQL Server appropriately. You can then specify the BAM Tools options and then recheck the AS2 status reporting option under the EDI/AS2 Runtime page. After running through the BizTalk configuration wizard successfully you will then need to open up the BizTalk administration console and go to the Party properties. On your AS2 party you will need to open up the AS2 properties and then on the General tab check to enable the AS2 status reporting. You may also want to check the other boxes on the Sender and Receiver pages of AS2 properties for whether to store the wire and decoded messages of the AS2 communications so that you can drill into this data from the Group Hub page.
After all of this configuration, you should restart your BizTalk applications and hosts and then wait a while so that the AS2 status reporting can collect some data. Then go to the Group Hub page and click refresh. The Group Hub page will now have some additional links to reports but you will probably need to scroll down on the group hub page in order to see them. Normally all of the group hub page fits within the standard size window but the additional AS2 status reporting enables the use of the scroll bar so be aware that these links may be off-screen initially when you view the Group Hub page.
Thanks,
MS Innovations Blog 
Leave a Reply