Sending IDOCs to SAP using SSIS – MSSQL Integration Services

Normally receiving and sending IDOCs can be easily done by using BizTalk Adapter for mySAP Business in BizTalk Server which is included in Microsoft BizTalk Adapter Pack 2.0. But what if I want to send an IDOC using SSIS and BizTalk is installed on a different server? Below are the steps on how you can do that.

Note: Since SSIS 2008 only supports <= .NET 3.5 all custom assemblies that will be called within it should be using this framework.


1. WCF LOB SDK Adapter (WCF-LOB-Adapter-SDK-2010-x86.exe) – Get it here. or in BizTalk DVD Installer under BizTalk Server\ASDK_X86

2. Microsoft BizTalk Adapter Pack 2.0 x86 – Get Evaluation version here. or in BizTalk DVD Installer under BizTalk Server\AdapterPack_x86

3. SAP Libraries x86 – this needs to be installed on C:\Windows\SysWOW64 folder. See section for SAP Adapter in this Microsoft Adapter Pack 2.0 install guide.

IDOC Library:

Next step is to create an IDOC WCF Client that you can call inside SSIS.

1. Open Visual Studio -> Create a new Project Library.

2. Right click the Project -> Click Add Adapter Service Reference.

3. Set the binding to sapBinding and set the configure a URI to:

sap://CLIENT={SAP Client ID};LANG=EN;@A/{Application Server Host}/{System Number}?ListenerGwServ={Gateway Service}&ListenerGwHost={Gateway Host}&ListenerProgramId={Program Id}&RfcSdkTrace=False&AbapDebug=False.  See this help for the configuration

4. Click Configure and set the credentials, then click Connect. Since we are sending IDOCS the contract type should be set to Client (Outbound operations).

5. In the Category, browse for the specific type of IDOC and version, when generating the schema you might encounter an error : “Error returned by RfcCallReceiveEx while calling RFC: IDOCTYPE_READ_COMPLETE..”, It means that you’re selecting an incorrect version of the IDOC, you should ask your SAP resource to identify the Release Number.

Select the Send and click Add. Click OK. This will generate a WCF client that you can call inside SSIS.

6. Sign the Assembly, compile and deploy to GAC then copy to C:\windows\Microsoft.NET\Framework\v2.0.50727.

It’s necessary to copy to this folder so you can add a reference to it in SSIS.


1. Add a data flow.

2. Set OLE DB Source / File / etc.

3. Add a Script Task, set the script task to Script Destination. Double click the script task and change the target framework to .NET 3.5 by going to project properties.

Add reference to the IDOC Library.

Create the IDOC object and pass it to the IDOC WCF Client.


Using Scripting Destination in SSIS

Sample on how to call WCF client in SSIS
To call IDOC WCF Client:

I’ve modified the code and copied it from

using System;
using System.Collections.Generic;
using System.Text;

// Add WCF, WCF LOB Adapter SDK, and SAP adapter namepaces
using System.ServiceModel;     //Change the Project to target .NET 3.5 and reference System.ServiceModel
using Microsoft.Adapters.SAP; //This assembly is under the Microsoft Adapter Pack 2.0 install folder
using Microsoft.ServiceModel.Channels; //This assembly is under WCF LOB SDK Install folder

// Include this namespace for WCF LOB Adapter SDK and SAP exceptions
using Microsoft.ServiceModel.Channels.Common;

namespace SapTypeIDOCClient
    class Program
        static void Main(string[] args)
            // variable for the IDOC client
            IdocClient idocClient = null;

            Console.WriteLine("IDOC XML client sample started");

		//Construct IDOC Object here. MATMAS01, PHRMAS01, SUBMAS
		var idocObj = new {IDOC OBJECT}

		//Assign Properties and segments of IDOCS here.

                // Variable for the GUID
                System.Nullable<System.Guid> adapterTxGuid;
                // string to hold the Idoc data
                string idocData;
                // string to hold the SAP transaction ID (TID)
                string sapTxId;

                // The client can be configured from app.config, but it is
                // explicitly configured here for demonstration.
                // set AutoConfirmSentIdocs property to true
                SAPBinding binding = new SAPBinding();
                binding.AutoConfirmSentIdocs = true;

                // Set endpoint address
                EndpointAddress endpointAddress = new EndpointAddress("{SAP Connection String see settings above");

                // Create client and set credentials
                idocClient = new {IDOC WCF Client}(binding, endpointAddress);
                idocClient.ClientCredentials.UserName.UserName = "YourUserName";
                idocClient.ClientCredentials.UserName.Password = "YourPassword";

                // Open the client and send the Idoc

                //Get a new GUID to pass to SendIdoc. You can also assign a null.
                //value to have the adapter generate a GUID.
                adapterTxGuid = Guid.NewGuid().ToString();

		//We are using the Send Method, it accepts a strongly typed iDOC (XML), SendIdoc sends FlatFile IDOC
                idocClient.Send(idocData, ref adapterTxGuid);

                // The AutoConfirmSentIdocs binding property is set to true, so there is no need to
                // confirm the IDOC. If this property is not set to true, you must call the
                // RfcConfirmTransID method of a TrfcClient with adapterTxGuid to
                // confirm the transaction on the SAP system.

                // Get SAP tx id from GUID
                sapTxId = SAPAdapterUtilities.ConvertGuidToTid((Guid) adapterTxGuid);

                Console.WriteLine("IDOC sent");
                Console.WriteLine("The SAP Transaction Id is : " + sapTxId);

            catch (Exception ex)
                Console.WriteLine("Exception is: " + ex.Message);
                if (ex.InnerException != null)
                    Console.WriteLine("Inner Exception is: " + ex.InnerException.Message);
                // Close the IDOC client
                if (idocClient != null)
                    if (idocClient.State == CommunicationState.Opened)


Automating\Silent Installation of BizTalk Deployment Framework (BTDF) using Powershell

To do a silent install/automate the installation of BizTalk Application using BizTalk Deployment Framework you can use the Powershell script below:

 Automates BizTalk Application deployment using BTDF 5.0

 Automates BizTalk Application deployment using BTDF 5.0
   1. It installs the MSI on the specified application path
   2. Calls EnvironmentSettingsExporter to generate the settings xml
   3. Updates Environment Variables
   4. Executes the MSBuild with parameters

 File Name: Install-BizTalkApplication.ps1
 Author: Randy Aldrich Paulo
 Prerequisite: Powershell 2.0, BizTalk Deployment Framework 5.0, BizTalk Server 2010

 MSI File generated using BizTalk Deployment Framework 5.0

.PARAMETER ApplicationInstallPath
 Location wherein the resource files will be copied, it will be use by the BTDF during the deployment

.PARAMETER Environment
 Name of environment (Local,Dev,Test,Prod) to be used, this value will be passed to
 EnvironmentSettingsExporter and willbe used to construct the environment variable: ENV_SETTINGS

 Install-BizTalkApplication -MsiFile "E:\Installer\Application 1\Application1.msi"
 -ApplicationInstallPath "E:\Program Files\Application 1"
 -Environment DEV

 Install-BizTalkApplication -msi "E:\Installer\Application 1\Application1.msi"
 -path "E:\Program Files\Application 1"
 -env TEST

 Install-BizTalkApplication "E:\Installer\Application 1\Application1.msi"
 "E:\Program Files\Application 1" TEST

 Install-BizTalkApplication "E:\Installer\Application 1\Application1.msi"
 "E:\Program Files\Application 1" TEST -SkipUndeploy $false

function Install-BizTalkApplication
  [Parameter(Position=0,Mandatory=$true,HelpMessage="Msi file should be existing")]
  [ValidateScript({Test-Path $_})]
  [Parameter(Position=1,HelpMessage="Path wherein the resource file will be installed")]
  [Parameter(Position=2,Mandatory=$true,HelpMessage="Only valid parameters are Local,Dev,Test and Prod")]



 #Step 1 : Run MSI 
  $script =
      $args = "-i $MsiFile INSTALLDIR=`"$ApplicationInstallPath`" /qn /norestart"
   Write-Host " Installing MSI File.." -ForegroundColor Cyan
   Write-Host "  MSI File: $MsiFile" -ForegroundColor DarkGray
   Write-Host "      Args: $args" -ForegroundColor DarkGray
   $exitCode = (Start-Process -FilePath "msiexec.exe" -ArgumentList $args -Wait -Passthru).ExitCode
   Write-Host " Exit Code: $exitCode"
   if($exitCode -ne 0)
    Write-Error "Installing $MsiFile failed!, Exit Code: $exitCode"
   Write-Host " Installed MSI success.." -ForegroundColor Green
   Write-Host ""
  Invoke-Command -scriptblock $script
 #Step 2 : Run MSBuild & Deploy
   <# Start Step 2.2 Run EnvironmentSettingsExporter, this one generates the xml file
   (Exported_DevSettings.xml, Exported_LocalSettings.xml etc..)
   $args = "`"" + (Join-Path $ApplicationInstallPath "Deployment\EnvironmentSettings\SettingsFileGenerator.xml") + "`"" + " Deployment\EnvironmentSettings"
   $exePath = ("`"" + (Join-Path $ApplicationInstallPath "\Deployment\Framework\DeployTools\EnvironmentSettingsExporter.exe") + "`"")
   Write-Host " Generating Environment Settings File.."  -ForegroundColor Cyan
   Write-Host " Location: $exePath" -ForegroundColor DarkGray
   Write-Host "  Args: $args" -ForegroundColor DarkGray

   $exitCode = (Start-Process -FilePath $exePath -ArgumentList $args -Wait -PassThru).ExitCode
   Write-Host " Exit Code: $exitCode"
   if($exitCode -ne 0)
    Write-Error " Generating Environment Settings File failed!, Exit Code: $exitCode"
   Write-Host " Generated Environment Settings File. " -ForegroundColor Green
   Write-Host ""
   <# End Step 2.2 Run EnvironmentSettingsExporter, this one generates the xml file
   (Exported_DevSettings.xml, Exported_LocalSettings.xml etc..)#>
   <# Start Step 2.3 Set the Environment Variables ENV_SETTINGS and BT_DEPLOY_MGMT_DB #>
   $settingsFile = "Deployment\EnvironmentSettings\Exported_{0}Settings.xml" -f $Environment
   $EnvSettings =Join-Path $ApplicationInstallPath $settingsFile

   Write-Host " Setting Environment Variables"  -ForegroundColor Cyan
   Write-Host "      ENV_SETTINGS = $EnvSettings" -ForegroundColor DarkGray;
   Set-Item Env:\ENV_SETTINGS -Value $EnvSettings
   Write-Host  " BT_DEPLOY_MGMT_DB = $BTDeployMgmtDB"  -ForegroundColor DarkGray;
   Set-Item Env:\BT_DEPLOY_MGMT_DB -Value $BTDeployMgmtDB
   Write-Host " Setted Environment Variables"  -ForegroundColor Green
   Write-Host ""
   <# End Step 2.3 Set the Environment Variables ENV_SETTINGS and BT_DEPLOY_MGMT_DB #>
   <# Start Step 2.4 Execute MS Build with parameters #>
   #Get .NET Version
   $dotNetVersion = gci 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' | sort pschildname -des | select -fi 1 -exp pschildname
   if($dotNetVersion = "v4.0") { $dotNetVersion = "v4.0.30319" } #Include other info if .NET 4.0
   if (Test-Path ( Join-Path $env:windir "Microsoft.NET\Framework\$dotNetVersion\MSBuild.exe" ))
    $BTDFMSBuildPath = Join-Path $env:windir "Microsoft.NET\Framework\$dotNetVersion\MSBuild.exe"
    Write-Host " Using MSBuild $dotNetVersion" -ForegroundColor DarkGray
    Write-Error " MSBuild not found."
   #Assign MS Build Params
   $logger="FileLogger,Microsoft.Build.Engine;logfile=`"" + ( Join-Path $ApplicationInstallPath "DeployResults\DeployResults.txt" ) + "`""
   $btdfFile="`"" +  (Join-Path $ApplicationInstallPath "Deployment\Deployment.btdfproj") + "`""
   $args = "/p:{1} /l:{2} {0}" -f $btdfFile,$parms,$logger
   Write-Host " Executing MSBuild from: $BTDFMSBuildPath"  -ForegroundColor Cyan
   Write-Host " ArgList: $args" -ForegroundColor DarkGray
   #Check MSBuild Return Code
   $exitCode = (Start-Process -FilePath $BTDFMSBuildPath -ArgumentList $args -Wait -Passthru).ExitCode
   Write-Host " Exit Code: $exitCode"
   Write-Host ""
   if($exitCode -ne 0)
    Write-Error " Error while calling MSBuild, Exit Code: $exitCode"
   #Copy Log File
   Write-Host " Copying  Log file."
   $args =  "Deployment\Framework\CopyDeployResults.msbuild /nologo"
   Start-Process -FilePath $BTDFMSBuildPath -ArgumentList $args
   <# End Step 2.4 Execute MS Build with parameters #>
  Write-Host " Running MS Build and deploying.." -ForegroundColor Cyan
  Invoke-Command -scriptblock $script
  Write-Host " Deployed application" -ForegroundColor Green


Download Script (MSWord): Install-BizTalkApplication – Powershell Script 


BizTalk Deployment Framework (BTDF) Tutorial – Basic Instruction – Walkthrough

BizTalk Deployment Framework for BizTalk 2010 Walkthrough

Since it’s really hard to find a simple instruction on how to use the BizTalk Deployment Framework (BTDF) i come up with compiled simplified instruction to get you started.

BTDF Files

When you add the BizTalk Deployment Framework in your project these are the important files that you need to take note of:

Deployment.btdfproj – this is the main file that contains the configuration on how your installer will behave.

SettingsFileGenerator.xml – this is an excel template that you can open using Excel in which you will map the settings (binding information) per environment. These files: Exported_DevSettings.xml, Exported_LocalSettings.xml, Exported_ProdSettings.xml, Exported_TestSettings.xml will be automatically generated by BTDF during installation.

Update Deployment.btdfproj

Double click Deployment.btdfproj to open.

The first part is pretty much self-explanatory:

<Configuration Condition="'$(Configuration)' == ''">Debug</Configuration>
<Platform Condition="'$(Platform)' == ''">x86</Platform>

Take note of the the last 3 settings, I set SkipIISReset since I’m not deploying anything on IIS and UsingMasterBindings(set to false initially, will be set to true later on) since I want to use a single binding file for all environments. How is it possible? Basically I would first manually configure the bt application (receive location/send ports) then I will export the binding (MasterBinding) and I will add place holders inside it so that the XmlPreProcessor (Part of BTDF) will substitute during installation.  Pretty cool huh?

Lets go to the last part:

   <BizTalkHosts Include="SSO_Host;Sending_Host" />
   <Components Include="CommonComponent.dll">
       <LocationPath>..\..\$(ProjectName)\Shared Assemblies</LocationPath>
    <Schemas Include="CommonSchema.dll">
       <LocationPath>..\..\$(ProjectName)\Shared Assemblies</LocationPath>
    <Schemas Include="MyTest.Schemas.dll">
<Transforms Include="MyTest.Transforms.dll">
 <Orchestrations Include="MyTest.Orchestrations.dll">
<LocationPath>..\..\$(ProjectName)\Source\MyTest.Orchestrations\bin\$(Configuration)</LocationPath>    </Orchestrations>

This means that I would like to

– Restart the BizTalk Host: SSO_Host and Sending_Host after the installation

– I have a common .NET component named CommonComponent.dll and common schema named  CommonSchema.dll both on Shared Assemblies folder.

– I have Schema, Orchestration and a Map. I changed the name because normally for big solution you don’t want to put all schemas in the schemas project and so on. Sometimes it’s better to split it functionally, this example configuration show you how  you can do that.

Next Steps

1. Deploy the solution manually or create a dummy PortBindings.xml (must be valid) to deploy using BTDF then click Tools -> Deployment Framework for BizTalk -> Deploy BizTalk Solution

2. Manually configure the receive ports, send ports, orchestration bindings.

3.  Export the bindings and named it as PortBindingsKnownGood.xml

4Run ElementTunnel.exe

Both (ElementTunnel and adapterXPaths.txt) can be found under <Program Files>\Deployment Framework for BizTalk\5.0\Framework\DeployTools.

The generated PortBindingsMaster.xml now has selected nested XML fragments decoded into plain, unencoded XML, controlled by the XPath statements in AdapterXPaths.txt. (see BTDF FAQ)

5. Add the the generated binding file: PortBindingsMaster.xml to the solution.

Configure PortBindingsMaster.xml

1. Double click PortBindingsMaster.xml to open.

2. Now go to the Address section of ReceivePort or SendPort and add a placeholder on the place you want to substitute during installation. Here I want the ${BackupOrderPath} substituted during installation.

<!-- ifdef ${ _xml_preprocess} -->
<!-- else -->
<!-- endif -->

 Configure SettingsFileGenerator.xml 

Right click SettingsFileGenerator.xml -> open with MS Excel.

This is how it should look like:


1. Open Deployment.btdfproj and update both UsingMasterBindings and ApplyXmlEscape to True

2. Click Tools-> Deployment Framework for BizTalk -> Build Server Deploy MSI. This will generate an MSI file under the Project Folder\Deployment\bin folder.

3. Double click the MSI to install.

4. In the Step asking for XML File click Elipsis and select the correct settings file under EnvironmentSettings folder. Then click next.

5. BizTalk Application installed.!!

Silent Install / Unattended installation of BTDF using powershell:

BizTalk Best Practices | Tip and Tricks

Below is a list that needs to be remembered when developing BizTalk solutions.

BizTalk Orchestration:

XPath Expressions: 1. To do an XPath count:

countVariable = System.Convert.ToInt32(xpath(yourMsg, "count({XPATH Instance path,can be copied from Schema})"));

2. To extract a value using XPath (string):

stringVar = xpath(yourMessage, "string({XPATH Instance path,can be copied from Schema})")

BizTalk WCF-SQL Adapter:

When confronted with the problem about MSDTC issue, either due to cross domain or the actual MSDTC being disabled this adapter will still work by just setting the useAmbientConnection to FALSE

BizTalk mySAP Adapter:

1. RFC/BAPI function not returning any result.

When expecting a response from SAP via RFC or BAPI you always need to supply an empty node wherein you’re expecting a result. For example if the field is under the T_QAIVCTAB, in the mapping you need to generate this field by mapping a scripting functoid that returns empty string.

2. Set EnableSafeTyping to True

To avoid nasty data type issue when sending/receiving a response set this value to true in your binding. Data type error: An error occurred when trying to convert the byte array [30-00-30-00-30-00-30-00-30-00-30-00-30-00-30-00] of RFCTYPE RFCTYPE_DATE with length 8 and decimals 0 to XML format. Parameter/field name: Error message: Year, Month, and Day parameters describe an un-representable DateTime. —> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime. at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day) Configuration: Schema Generation: During the schema generation (WCF Consume Adapter Service) also set the Enable Safe Typing to TRUE.

BizTalk Mapping:

Microsoft.XLANGs.Core.XTransformationFailureException: Error encountered while executing the transform. Error:Transformation failed.. —> System.Xml.XPath.XPathException: Function ‘userCSharp:ConvertToDecimal()’ has failed. —> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.FormatException: Input string was not in a correct format.

1. Be careful in data conversion inside the mapping especially when using Scripting Functiod to convert values. One prime example is when you use the Convert.ToDecimal method. If you use this method and the value contains scientific notation ( 3.71615996396169E-02) it will throw an exception. The correct way of doing it is by using the Decimal.Parse method.

decimal d = Decimal.Parse("8.71615996396169E-02", System.Globalization.NumberStyles.Float);

SAP Schema Generation Error:

Error Message:

Error while retrieving or generating the WSDL. Adapter message: Details: ErrorCode=RFC_EXCEPTION. ErrorGroup=RFC_ERROR_APPLICATION_EXCEPTION. SapErrorMessage=SEGMENT_UNKNOWN.  AdapterErrorMessage=Error returned by RfcCallReceiveEx while calling RFC: IDOCTYPE_READ_COMPLETE..


Idoc segment in SAP is not set to Released. Ask the SAP Team to check whether all segments are released.

BizTalk Deployment

Use BizTalk Deployment Framework (BTDF) to simplify deployment. BTDF can be downloaded from here. BTDF quick tutorial: Automating/Silent Install of BTDF using powershell here:

ESB Toolkit 2.1 using Enterprise Library 5.0

If you’re here probably you have a problem about this error:

[A]Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ConfigurationSourceSection cannot be cast to [B]Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ConfigurationSourceSection. Type A originates from ‘Microsoft.Practices.EnterpriseLibrary.Common, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ in the context ‘Default’ at location ‘C:\Windows\assembly\GAC_MSIL\Microsoft.Practices.EnterpriseLibrary.Common\\Microsoft.Practices.EnterpriseLibrary.Common.dll’. Type B originates from ‘Microsoft.Practices.EnterpriseLibrary.Common, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ in the context ‘Default’ at location ‘C:\Windows\assembly\GAC_MSIL\Microsoft.Practices.EnterpriseLibrary.Common\5.0.505.0__31bf3856ad364e35\Microsoft.Practices.EnterpriseLibrary.Common.dll’

Now there’s been lot of discussion how ESB Toolkit 2.1 breaks all other application that uses Enterprise Library 5.0 when deployed on the same machine, that’s because it updates the machine.config, which is the mother of all configs.

As of this moment there’s no fix regarding this matter. To undo the error that ESB Toolkit causes (see error above) you can follow the steps below:


Step 1. You need to check the platform that your application targets, either 32bit or 64bit and the .NET Version. For BizTalk 2010 it’s 4.0. 

To check the Host whether it’s using 32bit (default) or 64bit explicitly. BizTalk Admin Console -> Platform Settings -> Hosts -> <HostName> -> Properties -> Options -> 32-bit only flag.

Step 2. After determining the target platform,  open the Notepad.exe (Run as Administrator). It’s important to run it as administrator as we are going to edit the machine.config.

Step 3. Based on the platform open the machine.config using the Notepad.

32-bit: %Windows%\Microsoft.NET\Framework\v4.0.30319\Config

64-bit: %Windows%\Microsoft.NET\Framework64\v4.0.30319\Config

Step 4. Comment or remove the following sections:

<section name=”enterpriseLibrary.ConfigurationSource” type=”Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ConfigurationSourceSection, Microsoft.Practices.EnterpriseLibrary.Common, Version=,Culture=neutral, PublicKeyToken=31bf3856ad364e35″/>

<enterpriseLibrary.ConfigurationSource selectedSource=”ESB File Configuration Source”><sources><add name=”ESB File Configuration Source” type=”Microsoft.Practices.EnterpriseLibrary.Common.Configuration.FileConfigurationSource, Microsoft.Practices.EnterpriseLibrary.Common, Version=,Culture=neutral, PublicKeyToken=31bf3856ad364e35″ filePath=”E:\Program Files\Microsoft BizTalk ESB Toolkit 2.1\esb.config”/><add name=”ESB SSO Configuration Source” type=”Microsoft.Practices.ESB.SSOConfigurationProvider.SSOConfigurationSource, Microsoft.Practices.ESB.SSOConfigurationProvider, Version=, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ applicationName=”ESB” description=”ESB SSO Configuration” contactInfo=”” userGroupName=”BizTalk Application Users” adminGroupName=”BizTalk Server Administrators”/></sources></enterpriseLibrary.ConfigurationSource>

Step 5. Save and restart the application/services/host instances.

BizTalk upgrade issue – Missing Assembly.cs file

All BizTalk solutions from 2006R2 was upgraded to 2010 version and was checked-in to TFS couple of months back and today I try to get the latest version and tried to compile it and boom! it throws an error.

It seems that when you use the upgrade wizard and try to check-in (TFS), the Assembly.cs file is being ignored.

To solve this I went to the copy of the solution (or History if not available) and copy every Properties folder (which contains the Assembly.cs) to the new one.

Finally in the Source Control, I selected the top folder then click Add Items to the Folder -> Sort it by Name -> Exclude all that is not Assembly.cs -> Finish.

Lesson of the day:  to always check whether the Assembly.cs is included during the checkin when migrating a BizTalk solution.