Prerequisites:

The following solution should be built on a development machine with: 

a) Visual Studio 2005 and BizTalk 2006 installed.

b) A Sql Server Database will need to be created on a Sql 2005 instance (local or remote).
(This database is needed to host and run the Mapping Helper Stored Procedure)

A discussion of this sample can be found here:

http://objectsharp.com/blogs/matt/archive/2006/07/19/5520.aspx


Steps to install:
_________________________________

1) Unzip the BtsTransformationHelper.zip file (using folder names) on the root of the c:\ drive.
A directory structure should be created under C:\BtsTransformationHelper. You should see a directory 
structure as:
C:\BtsTransformationHelper\BTSSqlTransformation
C:\BtsTransformationHelper\CLRTransformHelpers
C:\BtsTransformationHelper\Files
C:\BtsTransformationHelper\SqlScripts

 
_________________________________

2) Using Sql Server Management Studio create a Sql Server 2005 database called -> TransformationHelper
OR:
Execute script: C:\BtsTransformationHelper\SqlScripts\CreateDataBase.sql to create the TransformationHelper 
Sql 2005 database
(Note: This script may have to be edited)

_________________________________
 
3) Execute script: C:\BtsTransformationHelper\SqlScripts\DataBaseSetUp.sql
while connected to the database created in the last step.  
Note: Sql Server Management Studio can be used to run the script.
Note: This script will create the lookup Country table, install the stored procedure and register 
the managed code that the stored procedure will call.

_________________________________

4) Execute test script: C:\BtsTransformationHelper\SqlScripts\TestScript.sql
This will test the stored procedure that was installed in the last step.
An XML result set should be produced after running the test script. No errors should appear.
Note: Sql Server Management Studio can be used to run the script.
_________________________________

5)  If you will be running the TransformationHelper Database on a remote machine please review the 
following to avoid Distributed Transaction Errors:

For windows XP:

http://codebetter.com/blogs/jeff.lynch/archive/2005/04/15/23022.aspx


For Windows 2003 and Windows XP:

http://geekswithblogs.com/sthomas/archive/2005/06/21/44361.aspx

Additionally using Windows 2003 please ensure that have:

Enabled Network DTC Access:

http://support.microsoft.com/?id=817064

_________________________________

6) In Windows Explorer double click on the BizTalk Solution: 
C:\BtsTransformationHelper\BTSSqlTransformation\BTSSqlTransformation.sln 

This will open the BizTalk project in Visual Studio 2005.
Right mouse button on the BTSSqlTransformation BizTalk project in Visual Studio 
and in the popup menu choose -> Properties. 

In the Property Pages dialog, select the Configuration Properties -> Deployment node
in the left hand side tree view.

Check to ensure that the Server and Configuration Database properties are 
configured properly.

Examine the orcTransformUsingSqlProc.odx orchestration that calls the stored 
procedure to carry out the mapping.

_________________________________

7) Right mouse button on the BTSSqlTransformation BizTalk project in Visual Studio and 
choose Deploy in the popup menu. 
This will deploy the BizTalk assembly to a BizTalk Application called SqlMapper.

_________________________________


8) View the Managed code called from the stored procedure.

Open solution:
C:\BtsTransformationHelper\CLRTransformHelpers\CLRTransformHelpers.sln

This will open up the Sql Server Project that contains the ProvinceHelper class
that is called by the stored procedure.


_________________________________


9) Edit the Binding File for a Remote Sql Server.

Edit the  binding file in notepad:  C:\BtsTransformationHelper\SqlMapper.BindingInfo.xml

Search for the below string in the binding file:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TransformationHelper;Data Source=localhost 

If you have installed the TransformationHelper database on a remote Sql Server edit the section:
Data Source=localhost
Changing localhost to the name of the remote server.

If you are using Standard Sql Security, edit the connection string to something like the below:
Set the proper Password, User ID, Data Source.

Provider=SQLOLEDB.1;Password=PutPasswordHere;Persist Security Info=True;User ID=PutLoginHere;Initial Catalog=TransformationHelper;Data Source=localhost
 
Also in this file find the string 

<Address>SQL://localhost/TransformationHelper/</Address>

Replace localhost with the name of the Sql Instance that is hosting the TransformationHelper Database (Same as the edited Data Source, as above).

_________________________________

10) Import the Binding File

Using the BizTalk Administration Console, navigate to the SqlMapper Application and right mouse button on it.
In the pop up menu choose Import -> Bindings.  
In the Import Bindings Dialog Box, choose file:
C:\BtsTransformationHelper\SqlMapper.BindingInfo.xml

This will create the necessary File Receive Port and Send Port and Sql Send Port. It will also 
bind the logical ports in the orchestration to the correct physical posts

_________________________________

11) Start and test.

Using the BizTalk Administration Console, navigate to the SqlMapper Application and right mouse button on it.
In the pop up menu choose -> Start  

This will start the ports and orchestration.

Drop file:

C:\BtsTransformationHelper\Files\AllApplicants.xml

To folder:

C:\BtsTransformationHelper\Files\ReceiveMessageToMapUsingSql

A mapped message should then appear in folder:

C:\BtsTransformationHelper\Files\SendMessageMappedUsingSql