Which to use: BizTalk or SQL-Integration Service (SSIS) ?

I had a situation where I could have used any of these tools, but after some reading about the difference I have nailed down to use SSIS. So this blog is just a collection of my readings which helped me to differentiate and choose the product in my project.

BizTalk and SSIS are different tools and fit different scenarios.

SSIS is designed to move and manipulate very large amount of data over extremely high performance batch processing where BizTalk has been designed to move, process, validate, transform, and route low rate of transactions consisting of tiny amounts of business data

Use SSIS for:

Large ETL processes like de-batching the large CSV file into SQL database, or cleansing the large record before inserting in to database.

Batch Oriented Scenarios i.e. when the files need to be processed in specific time of the day as opposed to file needs to be processed as and when the files are available.

Use BizTalk:

When you have to do the Business Process Management (BPM). i.e. some business processes.

Near real-time scenarios i.e. as and when the files are available and it has to be processed.

SSIS and BizTalk can work together:

Though SSIS and BizTalk are used in different place depends on the scenario, it’s important to note that we can use SSIS with BizTalk. They can work together. For Example we have a project that would receive a batch of data once a day - containing 1000s of records. SSIS would receive the data, and then scrub the data (or validate the format). Once the data was acceptable, BizTalk would pick up each record and do the business processing.

BizTalk can do SQL-BULK insert but..

For bulk data processing we can use “SQL Bulk Load adapter ” available from http://www.biztalkgurus.com/blogs/biztalksyn/archive/2005/10/23/Processing-a-Large-Flat-File-Message-with-BizTalk-and-the-SqlBulkInsert-Adapter.aspx which can efficiently insert a large amount of data into SQL you are still stuck with the issues of transmitting the MessageBox database and the memory issues of dealing with really large messages.

Reference:

http://social.msdn.microsoft.com/forums/en-US/biztalkgeneral/thread/745b3b31-a83a-4629-bec2-49800e266c7e/

http://geekswithblogs.net/wmichel/archive/2007/04/11/111470.aspx

http://social.msdn.microsoft.com/forums/en-US/biztalkgeneral/thread/745b3b31-a83a-4629-bec2-49800e266c7e/

http://social.msdn.microsoft.com/forums/en-us/biztalkgeneral/thread/261D0FBC-EE91-4211-B811-F1D29A4A38D4

http://social.msdn.microsoft.com/Forums/en-GB/biztalkgeneral/thread/0338b17e-f9af-476c-984f-4982b65f230f

Installed BizTalk Server 2006 R2 – Service Pack 1 (SP1)

Installed the SP1 in our test server yesterday. It was pretty much a smooth ride with minor hurdles. I started off without reading the installation notes (as it’s in *.docx version and we still need to upgrade our MS-Word to latest version. We never felt the need of upgrading the MS-Word until now)

And these error messages didn’t provide much information to resolve. And same as the 13.6 MB log file.

Since our servers are not maintained properly in the recent time with regular reboots, I just rebooted the server and followed the instructions in the installation guide this time (Thought I would perform the installation properly this time, though the document doesn’t provide any clues for the above error).

I’ll emphasis on the followings to be considered before starting the installation:

• SP1-Updates the database schemas:
  • All BizTalk servers in a server farm or group should be updated with the service pack as this updates the database schemas.
  • Backing-up the BizTalk databases are important (as it’s the case for every service pack). If you want to uninstall the SP1 later for any reason, you must restore the BizTalk Server databases with your backup.
• I would always suggest to backup the master secret file.
• All BizTalk Server 2006 R2 hotfixes that are currently installed on the computer will be automatically removed by the service pack installation. Some of the hotfixes are part of the service pack, where some are not. So keep a note of all the BizTalk hotfixes in your server before the installation and compare it against the hotfixes which are part of this service pack(http://support.microsoft.com/kb/974563). If any of your hotfixes are not part of this service pack, you can install the SP1-compatible version later..atleast your note will help you to double check later.

  

• Installing BizTalk Server 2006 R2 SP1 does not change the version number of your BizTalk Server installation. Ours is still “3.6.1404.0” after the service pack installation. (the service packs of previous versions of BizTalk server updates the product version number) So to identify its existence later, Go to “Add or Remove Programs” check the “Show updates” check box and look under “Microsoft BizTalk Server 2006 <<Developer Edition>>” 

GAC it

Have you ever come across this error; I had an orchestration and a new requirement to update the orchestration with the BRE came in. After developing the orchestration, built the solution and deployed it in the test server. But the new code (executing the rule through Call Rule shape) didn’t reflect in the output. When I opened the orchestration debugger, there was a strange behaviour of the control flow. The control flow just jumped over the new code –the Call Rules shape, as shown in the below picture.

I have checked everything, and then later found that it’s because of the build error. When creating the executable for the application in the BizTalk administrator console, I have not added the updated assemblies in the “Resources” folder. This caused the mismatch to the User interface (UI) of the orchestration to the code behind of the orchestration. UI had reflected the newer version of the code but not the code behind!!!

Then created a new build, by properly including the updated assemblies in the “Resources” folder and GACed the updated assemblies. It worked as expected. So lesson leant is, do the basic things properly -“GAC IT”.

Calling .NET assembly or any out-of-box static methods in BizTalk Rule Engine (BRE)

Calling .NET assembly or any out-of-box static methods (like System.String.Concat etc) in BizTalk Rule Engine (BRE) is a simple stuff; there is not much documentation available for beginners.

For example, if the rule is

If CustomerType = ‘new’

THEN

WelcomeMessage = String.Concat(“Hello”, CustomerName)

In the above example, I need to use the “System.String.Concat” method from out-of-box mscrolib assembly (or any of your custom method from your helper class).

Executing from Orchestration:

Option 1:

By default, the rule engine requires you to assert all the facts, before they ca be executed. So to execute the rule which contains the .NET assembly or any out-of-box static methods, we must create an instance of the helper class and pass it as a parameter to the rules engine in the Call Rules shape.

So for the above rule, in the Orchestration

• Create an instance variable of type “String” - since we are calling the String.Concat() ( or the type of your helper class if you want to call a .NET assembly in your case)

• Passing the instance type as the parameter: In the “CallRules Policy Configuration” window, select the above created variable in the “Parameter Name” column.

Option2:

Modify the BRE’s behaviour of expecting us to assert the facts by changing the value of the StaticSupport registry key as specified in MSDN (http://msdn.microsoft.com/en-gb/library/aa950269%28BTS.20%29.aspx).

Registry key for 32-bit OS: [HKEY_LOCAL_MACHINE\Software\Microsoft\BusinessRules\3.0 ]

Registry key for 64-bit OS:[HKEY_LOCAL_MACHINE/SOFTWARE/Wow6432Node/Microsoft/BusinessRules/3.0]

For testing the rule in the BRE composer:

Follow: http://geekswithblogs.net/nsthompson/articles/ClassAndBusinessRulesEngineIntegration.aspx

I have not used the steps mentioned by Neil (in the referenced blog). I am not sure how we will use the fact retriever specific for “integrating the .NET” if you already using a fact retriever in your rule for some other purpose like caching the datatable/database connectionstring.

I have always used a test orchestration which invoked my rule. Yes, I have to deploy my rule before using the rule in the orchestration. That's the major downside of this method.

KB: Errors while configuring SQLXML

This post is just a knowledge base reference for me with the issues I have encountered while configuring the SQLXML.

We use SQLXML to execute the SQL query and display the xml document in the web browser.

(As part of the BizTalk BAM portal, users can see the actual message being processed by BizTalk. For example on clicking a link “Original message” in BAM portal, the actual message which is being processed by BizTalk to track the data for BAM has to be shown in the portal. This message(XML) can be retrieved from BAMPrimaryImport database. On clicking the link(Original message) we post the URL (“http://webserver/MsgView?sql=select%20LongReferenceData%20from%20dbo.bam_HireExtension_AllRelationships%20where%20ActivityID='18271dc1-6cd6-4b44-a2db-0bc37b2926c1'%20and%20ReferenceType='Message” ) which executes the SQL query contained in it as part of the querystring. Though this approach is prone to SQL injections, this is just a quick and dirty way to get the work done-The above description may be irrelevant to others, but will remind me the scenario where I have used SQLXML)

To setup the SQLXML, follow the instructions as in:http://sqlxml.org/faqs.aspx?faq=97

The steps in the above URL create a virtual directory in IIS. The only configuration change it performs to the default virtual directory is, mapping to the “sqlis3.dll” from C:\Program Files\Common Files\System\Ole DB directory.

Apparently it just performs simple steps with the virtual directory setting. But it is strictly suggested not to create the above using the IIS wizard, rather do it using the SQLXML wizard.

After configuring the virtual directory, when I executed the above query in the web browser, I received the following errors.

Error 1:

HResult: 0x80046000
Source: Microsoft SQL isapi extension
Description: Invalid connection settings: access denied

This error is due to the invalid SQL user credentials supplied when executing the query by using the virtual directory configured in the above steps.

Follow the instructions from Microsoft to solve the issue: http://support.microsoft.com/kb/820874/en-us

What I did was, created a separate login in SQL Server (Under Security à Logins) and provided this newly created SQL-User in the SQLXML configuration as specified in the article.

After the above, I received the

Error 2:

“Login failed for user ‘UserNameJustCreated’. The password of the account must be changed.”

Then I executed the following query

ALTER LOGIN UserNameJustCreated

WITH PASSWORD = 'password123',

CHECK_POLICY = OFF,

CHECK_EXPIRATION = OFF

Then I had the following error

Error 3:

The CHECK_POLICY and CHECK_EXPIRATION options cannot be turned OFF when MUST_CHANGE is ON

Executed the following to solve this error

ALTER LOGIN UserNameJustCreated WITH PASSWORD = 'password123'

GO

ALTER LOGIN UserNameJustCreated WITH

      CHECK_POLICY = OFF,

      CHECK_EXPIRATION = OFF;

(So I should have not executed the query as specified in the Error-2 section)

Then when I executed the SQL query hoping it would work, but I received another error

Error 4:

XML document must have a top level element. Error processing resource 'http://nfp-util01/MsgView?sql=select%20LongReference...

To know the exact error, select “View Source”  option and following is the error:

<?MSSQLError HResult="0x80040e09" Source="Microsoft OLE DB Provider for SQL Server" Description="The SELECT permission was denied on the object 'bam_HireExtension_AllRelationships', database 'BAMPrimaryImport', schema 'dbo'."?>

So the error was, it required the execute permission for the SQL-user (UserNameJustCreated) I have created in the one of the above step. Gave the execute permission, then I can see the XML in the browser as a sweety J

Error: Exception has been thrown by the target of an invocation

In our BizTalk project, I have used a C# helper class, whose method uses Microsoft’s Enterprise Library to invoke a SQL-stored procedure. The SQL stored procedure does some data validation and raises errors for business validation errors using its RAISERROR keyword.

When our tester was testing the above application, they had this strange error

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> <<SQL Server’s error message>>

But the expected error is the <<SQL Server’s error message>> only  which has been appended wrongly with the strange error “System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation”

After spending sometime, I found out that the problem could be due to “Instrumentation” in Enterprise Library. So I installed the “Instrumentation” from

Start -> All Programs -> Microsoft pattern & practices -> Enterprise Library 3.1 – May 2007-> Install Instrumentation

It solved the issue.

Resources on testing with BizTalk Server

Michael Stephenson has shared his resources and reference on testing with BizTalk Server.

http://code.msdn.microsoft.com/BTSTestGuide

Thanks Michael.

Cheers