Stored Procedures for Oracle through Dot Net

One of the requirements in the current Project is to call queries from Oracle and perform validations. We are calling about 100 of the queries using "script funtoids" in the mapper. I wanted to optimize the database connections. Instead of sending 100 queries and hitting the database 100 times. I wanted to have single stored procedure to replace the 100 queries. Having worked with stored procedures for SQL database before, I did not find it an issue. My plan was to create an array that is input to the Stored Procedure and get an array as an output to the stored procedure. The idea was to send all possible variables in an array to the stored procedure. Process them and get possible values as an array. Use this array to populate the "Right Hand" side of the map.

On course of my effort, I realized that there are limitations as such of working with Oracle through arrays. I gave up after trying!

Pipeline tools

For those that don't know, there are some really handy pipeline tools in the SDK under the folder:

\Program Files\Microsoft BizTalk Server 2004\SDK\Utilities\PipelineTools

These tools allow you to test your pipeline components and, even more handily, flat file schemas without having to set up the BizTalk Server environment. They are pretty much essential when developing flat file schemas (particularly when you have a header, trailer and body schema).
Here is a list of the pipeline tools available:

DSDump.exe - this tool outputs a textual description of the schema.

Pipeline.exe - this tool executes a pipeline and produces one or more output files. Very useful for testing custom pipelines.

XMLDasm.exe - this tool executes the XML Disassembler component by simulating a receive pipeline.

XMLAsm.exe - this tool executes the XML Assembler component by simulating a send pipeline.

FFDasm.exe - this tool executes the Flat File Disassembler component by simulating a receive pipeline. Essential for flat file development!

FFAsm.exe - this tool executes the Flat File Assembler component by simulating a send pipeline. Again, essential for flat file development!

Note: The pipeline.exe tool does not access the BizTalk Server databases, so executing a pipeline that contains the BizTalk Framework Assembler/Disassembler may not work as it accesses the BizTalk Management database during runtime to match up receipts to documents.

Remote Connection to HAT

I read some articles on Remotely connecting to HAT. Not very straightword. My requirements were to just get a view of transaction process without using Remote Desktop to the server. Due to limited server connections it was always a problem to connect to it. This is how you connect to server:


1) Open the HAT.

2) Go to Tools----Preferences-----Select "Archived Data"....Enter the SQL server name and select the Database.

You are all set!

Converting Eurpean/American Dates to ISO-format

We are getting European/American dates in the inbound files. We need to convert them to ISO format dates before sending it over to database. This is how to do it:

1) Drag a script functoid over to the map grid.

2) One input to that "functoid" will be the date field from the input schema and the second input will be a constact value of either "1" ( for European format) and "2" (American format).

2) Create a Public Function in external assembly with two paramaters.

Public Function dateformat( byval strDate as string, Byval flag as integer)
Dim newdate As Date
Dim strReturnDate As String

Select Case flag
Case 1
newdate = Convert.ToDateTime(strDate , New CultureInfo("en-gb"))
strReturnDate = Format(newdate , "yyyy-MM-dd")

Case 2
newdate = Convert.ToDateTime(strDate , New CultureInfo("en-us"))
strReturnDate = Format(newdate , "yyyy-MM-dd")
End Select

Return strReturnDate

End Function