Gorgo.Live.ToString()

Mariusz, Gorzoch tech Blog

Archive for the ‘BizTalk 2010’ Category

UseAmbientTransaction = True

leave a comment »

Be carefully when you are using “UseAmbientTransaction=True” on the SQL receive port. This is especially important when you are calling stored procedure (sp) with under the hood performs some updates. When you use that property then BizTalk is performing execution under the transaction and this transaction get rollback even in case when BizTalk can not find subscribers for it. This is extremely important as if you sp works in the way that it return record and then mark it as processed then this will not be done and the some record will be return over and over again. On the end you will end up with hundreds errors on BizTalk and even kill your system.

be careful and good luck

Advertisements

Written by Mariusz Gorzoch

25 January 2012 at 16:13

Posted in BizTalk 2010

6648 Orphaned DTA Service Instances in {Server}.BizTalkDTADb, 1 Messages with refcount less than 0 in {Server}.BizTalkMsgBoxDb [SQLSTATE 42000] (Error 50000)

leave a comment »

My dear BizTalk 2010. I’ve just installed you and placed really simply app… why you are screaming with this nasty error ?

Actually not BizTalk itself is doing that but rather one of his SQL jobs :

Monitor BizTalk Server
Scans for any known issues with the BizTalkMgmtDb, BizTalkMsgBoxDb, and BizTalkDTADb databases. This includes orphaned instances.
Note This job is created on BizTalk Server 2010.

After digging for a while I found a good post describing what to do with that. The idea is to use special tool called “BizTalk Terminator”.

As you can find in description of mentioned post this tools is mentioned to resolve one of common database integration problem. In the old days those tasks where performed manually. Lucky now… we can just start that tool and use it.

Here are steps to use mentioned terminator:

  • Do backup of entire environment. In my case I was lucky that we are running BizTalk on VM host, so I’ve just asked to create snapshot of the entire machine
  • Determine task in “BizTalk Terminator” to run in order to resolve problem. In case of our issue the problem should be resolved by task:

image

  • Shutdown BizTalk instance and all his SQL jobs (make sure that they are not running)
  • Open “Terminator” , specify server name and database information
  • Check all checkboxes in order to confirm that you did backup and stop all instances and SQL jobs
  • Find task “Repair Refcounts for All Messages” and execute it (no parameters is required)

image

image

  • Find task “Repair Orphaned DTA Service Instances” and execute it (no parameters are required)

image

image

After performing above steps I did run SQL job “Monitor BizTalk Server” and confirmed that problem was resolved.

good luck…

Written by Mariusz Gorzoch

26 August 2011 at 21:31

Posted in BizTalk 2010

BizTalk + Visual Studio 2010 – issues with deployment

leave a comment »

Today a quick note : if you ever encounter issues with deploying your solution to BizTalk like:

  • you can not deploy your solution because BizTalk/Visual studio complain about improper binding
  • you deploy your solution over existing one and setting of your send/receive port get lost or mess-up

then probably visual studio forgot to refresh copy of your binding BizTalk configuration.

In that case you need:

  • shutdown Visual Studio
  • go to “C:\Users\{UserName}\AppData\Roaming\Microsoft\BizTalk Server\Deployment\BindingFiles” and delete all files from there
  • open Visual Studio and try to deploy solution once again

now everything should start work again like a charm.

Written by Mariusz Gorzoch

30 May 2011 at 10:21

BizTalk 2010 and InfoPath integration

with one comment

Today I needed to build a solution where BizTalk will process some data and throw out a InfoPath form with details of processed message. I remember that in the past were I was doing that I used an orchestration and some construct/assign massage shape to achieve that, but today I decided to find if this is really the easiest possible way and guess what ! = this is the most complicated way.

In this post I decided to describe the simplest way I found and believe me it is worth reading if you used orchestration approach in the past. Lately I have an chance to work for a while with one smart person from Denmark, who shows me that BizTalk is done in the way that a lot of thinks can be done really easy (if you know what you are doing ;))

So, where we start:

1. Start by designing your InfoPath form.

Do this by simply grabbing your XML document (the one with is produced by BizTalk and you want to turn it on info InfoPath form). Simply start InfoPath and pick-up option to design your form from XML/XSD document. In my case I manage to design form like the one shown below (not really fancy one):

image

Once you finish, publish your template somewhere where everyone can access it.

2. Create instance of your document

Simply open your form and fill it with data. Once you do it, save this form to desktop and open it with “notepad”. The result in my case is shown below:

image

What we need from that file are the “Processing instructions” or information placed in the header witch are used by explorer (and all other kind of apps) to find out what program should be used to process that file. In my case this section looks like this:

<?mso-infoPathSolution
solutionVersion="1.0.0.2"
productVersion="14.0.0"
PIVersion="1.0.0.0"
href="http://whmpplgdn7121:81/BizTalkInfoPath/Forms/template.xsn"
name="urn:schemas-microsoft-com:office:infopath:BizTalkInfoPath:http—hempel-com-SDS-V100" ?>
<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.3"?>

Most of that staff is constant, but some like ‘”href” are not. Href is pointing to the place were you published you InfoPath template and it tells to InfoPath where he should look for it, when he is rendering your documents for the users.

Mark this entire “processing instruction” section and copy it to your clipboard, we goanna need it in a second.

3. Create send port of file type and “XmlTransmit” pipeline

Go to BizTalk to your favorite app and create a send port of type “File” and Pipeline = “XmlTransmit”:

image

and here goes a trick. Click on the “..” on the right side of the “Send pipeline”. This should open an window for you as shown below:

image

Option with are important for us are :

  • ProcessingInstructionOptions = 0 (0-append processing instruction, 1-create new)
  • ProcessingInstructionScope = 0 (0 – document, 1 – Envelope)
  • XmlAsmProcessingInstruction = here is a place where you should past your processing instructions

Once done, hit “OK” button and try to send document fallowing schema of the document you used to build InfoPath form. This should create an new file for you on the output folder, which you can click and see content in the InfoPath:
image

and once I click on it, the magic happens:

image

nice and simple

Written by Mariusz Gorzoch

16 May 2011 at 20:13

Adding WCF-SQL adapter on BizTalk

leave a comment »

I keep forgetting about how to do it, so I just decided to place this instruction here and keep as a reference when I’ll need this in the future. So, how to add WCF-SQL adapter to BizTalk ?

  1. Go to BizTalk installation CD and find “BT Server” folder and install ASDK_x86
  2. Go to BizTalk installation CD and find “BT Server folder and install AdapterPack_x86
  3. Go to “BizTalk administration console"->”BizTalk group”->Platform settings”, right click on “Adapter and choice “New->Adapter”. From “Adapter” drop down list pick-up “WCF-SQL”, give it a name “WCF-SQL” and hit Ok
  4. Restart BizTalk host instance

Done. Now I have a reference in case I’ll need this in future 🙂

Written by Mariusz Gorzoch

13 May 2011 at 15:55

Posted in BizTalk 2010

Missing BAM Excel plug-in

with one comment

I’m playing lately a lot with BizTalk and yesterday I’ve tied BAM. Unfortunately with my surprise I didn’t get BAM Excel tab on the ribbon menu. I goggle around that and found that I just need to enable it in the Manage Excel ADD-in. Unfortunately again, in my case this plug-in was not available there.

After digging, searching thru the list of files on my machine I found that this plug-in is located “C:\Program Files (x86)\Microsoft BizTalk Server 2010\ExcelDir\Bam.xla”. Once I founding then it was easy…. I’ve just went to Manage Excel ADD-in window and added BAM plug-in thru browse functionality. Once done… I get a new BAM tab on Excel :

image

My environment:

  • BizTalk 2010
  • Office 2010 32bit
  • Windows 2008 64bit

Written by Mariusz Gorzoch

21 April 2011 at 13:03

Posted in BizTalk 2010

Tagged with

WCF-SQL adapter and simple select statement

leave a comment »

In order to use WCF-SQL adapter you need to install two additional component :

  • ASDK_x86
  • AdapterPack_x86

Both of those can be found on BizTalk 2010 installation CD. Once you do it, you need to register new adapter in BizTalk adapter tab (simply go there and add new adapter by pointing WCF-SQL). After that you are ready to start using new adapter.

Here I would try to go thru simple example how to use that adapter to perform simply “select” statement and by this retrieve one record from DB and put it (after simple transformation) into BizTalk MsgBox.

First go to Visual Studio and create new project. Once project will be created go and add two new schemas and map:

1. Schema1 = “resp.xsd”

image

This schema will be used to map returned data by our select statement. Please notice two important things here:

  • Entire schema need to be “qualified” one
  • You need to set-up namespace (we will use it during configuration of WCF-SQL adapter). In my case I’m using “http://hempel.com/cw02” for all my artifacts

2. Schema2 = “common.xsd”

image

again:

  • mark elements as “qualified”
  • remember of setting up proper namespace

3. Map = “ConverttoCommon.map”

image

which is used to translate Schema1 into Schema2.

In addition to above you need a database on SQL and table of the structure like the one shown bellow:

image

Once you do this, then compile project and deploy it to BizTalk. Inside BizTalk go to receive port and create one of “one-way” type. After that add new location and:

image

then click on “Configure”: Set-up connection string to SQL and click on Binding:

image

set-up binding in the way that:

  • XmlStoredProcedureRootNodeName = root node of you SQL schema (this will be added by the adapter during retrival of data)
  • XmlStoredProcedureRootNodeNameSpace = “http://hempel.com/cw02”
  • InboundOperationType = “XmlPoling” <- this is importent as this defines the way how the data will be returned by the adapter
  • PolledDataAvailableStatment = “select count(1) from source” <- this will let the adapter to know if there is something to retrieve
  • PollingStatement =
   1:  select 
   2:  1 as tag, NULL as parent,
   3:  'http://hempel.com/cw02' as "cust:Source!1!xmlns:cust", 
   4:   Id as "cust:Source!1!cust:Id!element",
   5:   Name as "cust:Source!1!cust:Name!element",
   6:   Attr01 as "cust:Source!1!cust:Attr01!element",
   7:   Attr02 as "cust:Source!1!cust:Attr02!element",
   8:   Attr03 as "cust:Source!1!cust:Attr03!element"
   9:  from source for xml explicit
 Unfortunately this is the way how you need to make SQL statement. If you don’t go for this then you will get improper namespace for root elements returned by SQL which will prevent further processing of the message (you will be unable to map it to our internal SQL response message schema (schema1))

Done.

Next go to port definition and add “Inbound map”:

image

Click OK, and add also send port (for example file one) and set-up it in the way that it will subscribe for our common message:

image

Completed! Now if you put some data into SQL table, then you should get a file in output folder (if you use file send adapter in sent port).

Written by Mariusz Gorzoch

9 March 2011 at 15:21

Posted in BizTalk 2010