Microsoft BizTalk

Different Mind Set Between Data Warehouse and System Integration

I love doing database design with people. I've done my fair share of database modeling on my own; those are the easy projects, sure, but with no feedback. However, working with people on database never fails to help me look at things from different perspective.A very simple thing like whether a NOT NULL table column should have default values get very different reaction from a BI guy and a System Integration guy. Let me put some of the priorities out from different projects I've been in to help understand ......

Posted On Friday, March 1, 2013 2:54 PM | Comments (0)

Monitoring BizTalk Server Without SCCM, using SQL query

For the environment that does not have SCCM to monitor BizTalk server health, the following SQL query can be incorporate into a SQL job and have BizTalk server at least on a "constant" check. Note: be aware that the following was tested only against a single suspended instance with a send port. Since BizTalk SQL server does not allow select against the ServiceClass table, I couldn't use a single table join. With the data I currently have, I am not 100% sure the Receive Port, Pipeline, Orchestration ......

Posted On Wednesday, September 14, 2011 10:37 PM | Comments (0)

BizTalk documentator

This looks like a very useful tool: Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.000... mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans... ......

Posted On Friday, May 6, 2011 11:53 AM | Comments (0)

Automated BizTalk documentation

Yay, this should help us going through old legacy app with no doc, at least some help. Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.000... mso-pagination:widow-orphan; font-size:11.0pt; ......

Posted On Tuesday, February 15, 2011 2:09 PM | Comments (0)

BizTalk UseAmbientTransaction and System.ObjectDisposedException

[UPDATE: 2010-10-15] This update is long overdue. Once we implemented all the changes, we were still getting that error from time to time. The sympton was that we would not get that error for hours, even days, but once we get it, the error keeps coming. After fiddling with some settings, we found that setting the "PollWhileDataFound" to False did the trick. We have not seen the error since. [UPDATE: 2010-08-26] We were able to fix our problem by setting the WCF-SQL receive location transaction isolation ......

Posted On Wednesday, August 25, 2010 10:46 AM | Comments (2)

BizTalk Business Rules Install steps for StaticSupport

Damn, forgot to do this every time! http://blogs.msdn.com/b/ric... HKEY_LOCAL_MACHINE\SOFTWARE... (DWORD) or HKEY_LOCAL_MACHINE\SOFTWARE... (DWORD) 0 - This is the default key, and pretty much mimics the behavior of BizTalk Server 2004 where an instance of an object is always required as an input fact, and the method is only called when the rule is evaluated or executed. 1 ......

Posted On Wednesday, August 25, 2010 10:02 AM | Comments (0)

Embarrassing Gaffe

I was pretty sure the application didn't have an orchestration, and kind of freaked out when the send ports do not have any filter on ReceivePortName.  It's definitely THE most embarrassing moment of my BizTalk life.

Check and double-check!

Posted On Wednesday, January 20, 2010 11:59 AM | Comments (0)

Useful T-SQL queries on BizTalk Filter, Send Port, Receive Location, etc.

[2011-02-17] Disable all receive locations of Schedule adapter if the system has any suspended messages UPDATE RL SET RL.[Disabled] = -1 FROM BizTalkMgmtDb.dbo.adm_Recei... AS RL WITH(READPAST, ROWLOCK) INNER JOIN BizTalkMgmtDb.dbo.bts_recei... AS RP WITH(READPAST, ROWLOCK) ON RL.ReceivePortId = RP.nID INNER JOIN BizTalkMgmtDb.dbo.bts_appli... AS APP WITH(READPAST, ROWLOCK) ON RP.nApplicationID = APP.nID INNER JOIN BizTalkMgmtDb.dbo.adm_Adapter AS AD WITH(READPAST, ROWLOCK) ON RL.AdapterId ......

Posted On Wednesday, January 20, 2010 11:33 AM | Comments (0)

BizTalk FILE adapter mask is a prefix

It seems like the file mask we can enter into the File Mask textbox in a BizTalk FILE port configuration is a prefix.  I entered a mask that is "fix_*.txt" and the port is picking up everything with that prefix.  I had quite a few files with .txt[datetimestamp] in the folder and everything was picked up, created quite a havoc.

Posted On Wednesday, January 13, 2010 12:09 PM | Comments (2)

BizTalk transaction even cover an "AFTER INSERT" trigger

This surprised me.  I have a BizTalk SQL adapter calling a stored procedure to insert a row in some table.  The table has an "AFTER INSERT" trigger on it.

The original design had the trigger fire off an SSIS package.  But when the SSIS is called, the row status is still not committed.  I thought since I'm using an "AFTER INSERT" trigger, I was allowed to do that.  Need to read more on the transaction nature of triggers, I guess.

Posted On Wednesday, December 30, 2009 12:25 PM | Comments (0)

Using BizTalk to bridge SQL Job and Human Intervention (Requesting Permission)

I start off the process with either a BizTalk Scheduler (http://biztalkscheduledtas... or a manual file drop of the XML message. The manual file drop is to allow the SQL Job to call a "File Copy" SSIS step to copy the trigger file for the next process and allows SQL Job to be linked back into BizTalk processing. The Process Trigger XML looks like the following. It is basically the configuration hub of the business process <ns0:MsgSchedulerTrigger... xmlns:ns0="urn:com:something ......

Posted On Friday, February 4, 2011 3:29 PM | Comments (0)

Execute SQL Job synchronously for BizTalk via a Stored Procedure call

The async one was very easy to do. However, in the process automation task I was given, there was a need to execute SQL Job and waiting for the execution result. I couldn't find anything on the web to do what I wanted, so I make the following stored procedure. In addition, this stored procedure is quasi-synchronous. I start the job and then check the status. You can change the timing of the status check, but for the example below, it is "synchronous within 30 seconds." I wish there is a better way ......

Posted On Monday, November 1, 2010 3:18 PM | Comments (0)

BizTalk port polling interval and setting up a daily SQL receive location

I've always thoughts that BizTalk will start counting when the host instance gets restarted or the ports enabled. And when the interval hits inside the service window, it will do whatever it needs to do. We did a test today and I was surprised. BizTalk actually executes at the start of the service window and counts the interval time from that point. We were going to set up a SQL receive location polling every 24 hours to execute a daily task, but with this new discovery, we are going to set up a ......

Posted On Tuesday, December 15, 2009 4:35 PM | Comments (0)

Final note on creating PDF using SSRS and SSIS

Business Scenario: The client gets some kind of feeds that writes into a process table of their client processing system. The data is stored directly as the raw XML file inside a SQL data column. The XML could store multiple enrollment information for the same member. We need to use this info to determine whether a member is eligible, then insert into a staging table. The staging table is then used as a feed to drive a report that generates an image for each claim and sent off to the different provider. ......

Posted On Tuesday, December 8, 2009 2:19 PM | Comments (0)

Tricks to format XML properly for output from a SQL query

A few things I've learned in the past few days on SELECT {something} FOR XML AUTO, ELEMENTS Use subquery to denormalize data so that they can be grouped under the same parent element. Do not use synonym to get to your linked server. If you want any elements to be named properly, use full linked server syntax and alias the table/subquery. I'll add more as I learn them ......

Posted On Thursday, December 3, 2009 1:44 PM | Comments (0)

BizTalk nsoftware SFTP adapter VS file encoding

We run into this problem time and time again, and just want to put out the blog and set the record straight. BizTalk nsoftware SFTP adapter v2 does not have an option to change the encoding of the file. The only possible way that I can see if to upgrade to v3 and use the "After Get" to simulate a FTP command to change the encoding of the file. For another project, we used a sort of "hack" to ftp to and from an internal location to change the encoding, but that only made sense because it's a monthly ......

Posted On Thursday, September 24, 2009 3:32 PM | Comments (0)

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski