Azure, .NET and Software development

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.XmlDocument 4. 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 x

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 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. This is a known issue which occurs while installing Service Pack for Visual Studio the 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

While passing DateTime parameter to Oracle using generated schemes error 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

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 namesp

My previous sample will generate one file for output so 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!

1. Create database and tables 2. 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, Amo