Posts

Showing posts from February, 2010

Passing XML as a parameter to Oracle or SQL

Image
In some cases we need to pass whole XML as a parameter to stored procedure in Oracle or SQL

The issue is you cannot write XML as XML element
XML will be broken

The solution is pretty simple

1. Create a BizTalk project
2. Create new orchestration
3. Create new message InputXML and set type to System.Xml.XmlDocument4. Create Receive shape assign InputXML to it as Message 5. Create Receive Port which will be assigned to file receive port in BizTalk applicatoin 6. Connect Receive Port with Receive shape 7. Create new Schema name it SQLParams
It should look like
XMLParam is the element which will hold our XML
8. Add new Message to orchestration name it Transformed, set type to SQLParams 9. Add variable Doc to orchestration , set type System.Xml.XmlDocument 10. Insert to orchestration Message Construct shape 11. Insert to Message Construct shape Message assignment shape name it Transformation 12. Edit Transformation , set expression

Doc.LoadXml(@"
<ns0:Root xmlns:ns0=""http://XMLParam.SQLP…

BizTalk Server 2009 course for IT

Working hard on BizTalk Server 2009 course for IT - Courseware Library.
Will be great if you will send your suggestions and regards to include
to the course. It will be available soon for Trainer Lead courses at Microsoft Certified Partners for Learning Solutions (MCPLS) centers.

BizTalk Server 2009 R2

BizTalk Server 2009 R2 is planned for 2010 and will include:

Support for new Platform releases:Visual Studio 2010
SQL Server 2008 R2
Windows Server 2008 R2
Improved Business to Business(B2B) scenarios:
Mapper Enhancements: Mapper Enhancement includes enhanced visualization, scrolling and search capabilities.
FPTS adapter: a secure and standards-based adapter allows exchanging documents between trading partners using the FTP protocol.
Updated B2B Accelerators
Productivity Enhancements:

Performance tuning dashboard
Improved support for event processing and filtering for RFID Events
Support PowerShell for Management tasks.
New SCOM object model to better reflect BizTalk artifacts.

Creating project ‘BizTalk project name’… project creation failed.

Creating project ‘BizTalk project name’… project creation failed.
This is a known issue which occurs while installing Service Pack for Visual Studiothe details and suggestions you can check at http://msdn.microsoft.com/en-us/library/ee449462(BTS.10).aspx
But sometimes just you can run BizTalk installer in Repair mode

Passing DateTime parameter to Oracle

While passing DateTime parameter to Oracle using generated schemeserror occurres. It is not possible to convert string to DateTime for oracle Stored Procedure
in this case binding property EnableSafeTyping must be enabled
for details please check http://msdn.microsoft.com/en-us/library/cc185389(BTS.10).aspx

Debatching records from WCF-SQL using LOOP and XPath

Image
I will continue on my sample which demonstrated records polling from SQL using WCF-SQL adapter
How I wrote in my last post it is not the best solution , it will be much slower then disassembling data in pipeline (which I will post in 2-3 days)
So what I want to do. First I will count the number of records retrieved from SQL then using LOOP shape I will iterate through records and send each to a file adapter
If you did not read my two last posts , read them and create BizTalk project , install SP and create table in MSSQL , create application and ports in BizTalk
I will continue on the same orchestration

Add new variable in orchestration and set Identifier: RecordsCount Type: System.Int32
After Send shape insert Expression shape Set expression :
RecordsCount = (System.Int32)(xpath("count(/*[local-name()='TypedPolling' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1']/*[local-name()='TypedPollingResultSet0' and namespace-uri()='http://…
My previous sample will generate one file for outputso if my SP will return many records the result will be the same and in one output file I will get many records.
In some cases you will need to debatch/disassemble the result and for each record to have one separate file.
There are two ways to solve the issue 1. make disassembler changing the structure of the generated XSD 2. make loop in orchestration and retrieve records with indexer using XPath
In my next post I will show the 2nd option even it is not the best solution. Using disassembler will work many times faster if you have many records!

How to poll database using WCF-SQL adapter

Image
1. Create database and tables2. Create stored procedure 3. Create BizTalk project 4. Create orchestration 5. Consume WCF-SQL 6. Write received data to file share


First create TESTWCFSQL database from you SQL Management studio Run the script to create table:
USE TESTWCFSQL GO
IF OBJECT_ID('dbo.MainData', 'U') IS NOT NULL DROP TABLE dbo.MainData GO
CREATE TABLE dbo.MainData ( MainDataID int primary key identity(1,1), FirstName nvarchar(30), LastName nvarchar(30), Amount int ) GO

Run script in SQL management studio to create procedure
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_SCHEMA = N'dbo' AND SPECIFIC_NAME = N'PollData' ) DROP PROCEDURE dbo.PollData GO
CREATE PROCEDURE dbo.PollData AS select MainDataID, FirstName, LastName, Amount from dbo.MainData delete from dbo.MainData GO

insert some sample lines executing
use TESTWCFSQL
insert into MainData (FirstName, LastName, Amount) values (N'Patrick', N'Cash', 1000), (N&#…