May 092016
 

When you install BizTalk 3013R2 CU2, there is an undocumented requirement that you need to have BizTalk already configured.

If you don’t, you will get the following error

And when it finishes

 

If you look in the event log, you will notice this entry, which lead me to the belief that you need to have configured.

Description:

Application: BtsDeploySystemAssemblyTask.exe

Framework Version: v4.0.30319

Description: The process was terminated due to an unhandled exception.

Exception Info: System.InvalidOperationException

Stack:

   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory, System.Threading.Tasks.TaskCompletionSource`1<System.Data.ProviderBase.DbConnectionInternal>, System.Data.Common.DbConnectionOptions)

   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(System.Data.Common.DbConnection, System.Data.ProviderBase.DbConnectionFactory, System.Threading.Tasks.TaskCompletionSource`1<System.Data.ProviderBase.DbConnectionInternal>, System.Data.Common.DbConnectionOptions)

   at System.Data.SqlClient.SqlConnection.TryOpenInner(System.Threading.Tasks.TaskCompletionSource`1<System.Data.ProviderBase.DbConnectionInternal>)

   at System.Data.SqlClient.SqlConnection.TryOpen(System.Threading.Tasks.TaskCompletionSource`1<System.Data.ProviderBase.DbConnectionInternal>)

   at System.Data.SqlClient.SqlConnection.Open()

   at BtsDeploySystemAssemblyTask.BtsDeploySystemAssemblyTask.Main(System.String[])

I configured it, ran it and it worked.

The download page states nothing about BTS needing to be configured, (To apply this cumulative update package, you must have BizTalk Server 2013 installed. ) nor is the Readme.txt available.

 

I am creating an interface to Constant Contact. I need to get the list numbers the company uses.

So I need to call the following https://api.constantcontact.com/v2/lists

The resulting JSON looks like this

[
{
"id": "1",
"name": "General Interest",
"status": "ACTIVE",
"createddate": "2013-03-11T20:37:28.000Z",
"modifieddate": "2013-03-11T20:41:42.000Z",
"contactcount": 143
},
{
"id": "2",
"name": "Great News!",
"status": "ACTIVE",
"createddate": "2012-12-19T21:33:22.000Z",
"modifieddate": "2013-02-01T17:54:43.000Z",
"contactcount": 53
},
{
"id": "3",
"name": "Monthly Specials!",
"status": "ACTIVE",
"createddate": "2012-12-19T21:33:50.000Z",
"modifieddate": "2013-02-01T17:54:43.000Z",
"contactcount": 375
},
{
"id": "4",
"name": "Tips, Tricks, & Fun!",
"status": "HIDDEN",
"createddate": "2012-12-19T21:33:50.000Z",
"modifieddate": "2013-02-01T17:54:43.000Z",
"contactcount": 2
}
]

The problem with this JSON is that when I run the wizard to create the schema, I get the following error

I essentially need to add a header, and give it a name

I created a String Resource called EnhancedFormat and the value is {{“{0}”:{1}}}

Here is the pipeline code (took me about 10 minutes to write)

using CCListModifier.Properties;
using Microsoft.BizTalk.Component.Interop;
using Microsoft.BizTalk.Message.Interop;
using System;
using System.Collections;
using System.IO;
using System.Text;

namespace ConstantContact
{
[ComponentCategory(CategoryTypes.CATIDPipelineComponent)]
[ComponentCategory(CategoryTypes.CATIDDecoder)]
[System.Runtime.InteropServices.Guid("7CC58BBC-2DC4-48AC-976B-1E1E4B98FD4D")]
public class JSONFixer : Microsoft.BizTalk.Component.Interop.IBaseComponent,
Microsoft.BizTalk.Component.Interop.IComponent,
Microsoft.BizTalk.Component.Interop.IPersistPropertyBag,
Microsoft.BizTalk.Component.Interop.IComponentUI
{
private string rootNode = null;
public string RootNode
{
get { return rootNode; }
set { rootNode = value; }
}
public IntPtr Icon
{
get { return IntPtr.Zero; }
}
public IEnumerator Validate(object projectSystem)
{
if (projectSystem == null)
throw new System.ArgumentNullException("No project system");
        IEnumerator enumerator = null;
        ArrayList strList = new ArrayList();
        try
        {
        }
        catch (Exception e)
        {
            strList.Add(e.Message);
            enumerator = strList.GetEnumerator();
        }
        return enumerator;
    }
    public void GetClassID(out Guid classID)
    {
        classID = new System.Guid("7CC58BBC-2DC4-48AC-976B-1E1E4B98FD4D");
    }
    public void InitNew()
    {}
    public void Load(IPropertyBag propertyBag, int errorLog)
    {
        string val = (string)ReadPropertyBag(propertyBag, "RootNode");
        if (val != null) rootNode = val;
    }
    private static object ReadPropertyBag(IPropertyBag propertyBag, string propName)
    {
        object val = null;
        try
        {
            propertyBag.Read(propName, out val, 0);
        }

        catch (System.ArgumentException)
        {
            return val;
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
        return val;
    }
    public void Save(IPropertyBag propertyBag, bool clearDirty, bool saveAllProperties)
    {
        object val = (object)rootNode;
        WritePropertyBag(propertyBag, "RootNode", val);
    }
    private static void WritePropertyBag(IPropertyBag propertyBag, string propName, object val)
    {
        try
        {
            propertyBag.Write(propName, ref val);
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
    }
    public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg)
    {
        pInMsg.BodyPart.Data = EnhanceMessage(pInMsg.BodyPart.Data);
        return pInMsg;
    }
    private Stream EnhanceMessage(Stream stream)
    {
        StreamReader reader = new StreamReader(stream);
        string text = reader.ReadToEnd();
        text = string.Format(Resources.EnhancedFormat, RootNode, text);
        byte[] byteArray = Encoding.UTF8.GetBytes(text);
        MemoryStream resultingStream = new MemoryStream(byteArray);
        resultingStream.Position = 0;
        return resultingStream;
    }
    public string Description
    {
        get { return "Enhance JSON Pipeline Component"; }
    }
    public string Name
    {
        get { return "Enhance JSON"; }
    }
    public string Version
    {
        get { return "1.0"; }
    }
}

}

Here is the pipeline configuration

So when the pipeline executes it adds {“List”: to the beginning and } to the end

So here is the resulting xml

<?xml version="1.0" encoding="utf-8"?>
<ns0:Response xmlns:ns0="http://ConstantContactDefinition.ListOfLists">
  <List>
    <id>1</id>
    <name>General Interest</name>
    <status>ACTIVE</status>
    <created_date>2013-03-11T20:37:28Z</created_date>
    <modified_date>2013-03-11T20:41:42Z</modified_date>
    <contact_count>143</contact_count>
  </List>
  <List>
    <id>2</id>
    <name>Great News!</name>
    <status>ACTIVE</status>
    <created_date>2012-12-19T21:33:22Z</created_date>
    <modified_date>2013-02-01T17:54:43Z</modified_date>
    <contact_count>53</contact_count>
  </List>
  <List>
    <id>3</id>
    <name>Monthly Specials!</name>
    <status>ACTIVE</status>
    <created_date>2012-12-19T21:33:50Z</created_date>
    <modified_date>2013-02-01T17:54:43Z</modified_date>
    <contact_count>375</contact_count>
  </List>
  <List>
    <id>4</id>
    <name>Tips, Tricks, &amp; Fun!</name>
    <status>HIDDEN</status>
    <created_date>2012-12-19T21:33:50Z</created_date>
    <modified_date>2013-02-01T17:54:43Z</modified_date>
    <contact_count>2</contact_count>
  </List>
</ns0:Response>
 

So I was attempting to use the SSO Key/Value pair application downloadable here:

http://www.microsoft.com/en-us/download/details.aspx?id=14524

However, every time I created an application I would be unable to add any key/values.

I looked in the event log to see this error:

Log Name:      Application
Source:        SSO MMC Snap-In - CreateApplicationFields
Date:          4/4/2016 8:17:38 AM
Event ID:      0
Task Category: None
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      installer
Description:
The parameter is incorrect.

The fix is to increase the version of the referenced Microsoft.EnterpriseSingleSignOn.Interop assembly

I went to the GAC and saw this

C:\Windows\Microsoft.NET\assembly\GAC_MSIL\Microsoft.EnterpriseSingleSignOn.Interop\v4.0_9.0.1000.0__31bf3856ad364e35

I modified the C:\Program Files (x86)\Microsoft Services\SSO Application Configuration\SSOMMCSnapIn.dll.config for me to be this

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1" appliesTo="v4.0.30319">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.EnterpriseSingleSignOn.Interop" publicKeyToken="31bf3856ad364e35" culture="neutral" />
        <bindingRedirect oldVersion="5.0.1.0" newVersion="9.0.1000.0"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <appSettings>
    <add key="CompanyName" value="Stott Creations" />
  </appSettings>
</configuration>
Jan 112016
 

There was an error where, after installing CU2 for BizTalk 2013 R2, it would start throwing the following error

System.TypeLoadException: Method ‘get_UseXSLTransform’ in type ‘xxxxxxxxxxxx’ from assembly ‘xxxxxxxxxx’ does not have an implementation.
at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMarkHandle stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName, ObjectHandleOnStack type)
at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean loadTypeFromPartialName)
at System.RuntimeType.GetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark)
at System.Type.GetType(String typeName)

The only solution was to recompile the solution and redeploy

Today there is a new version of the CU which does not require a redeploy.
https://support.microsoft.com/en-us/kb/3119352

Oct 302015
 

So a client just purchased our ESB Vision product and one of the things that they wanted me to do was to build a silent install of BizTalk to be deployed on their developer machines. Here are the requirements

  • The dev machines are running Windows 7
  • IIS needs to be installed 
  • SQL Server 2012 SP2 needs to be installed
  • BizTalk 2013r2 needs to be installed
  • All LOB adapters need to be installed
  • Host Integration needs to be installed
  • ESB Toolkit needs to be installed
  • SQL needs to be running as the NT Service accounts

So here is the challenge:

  • Win7 doesn’t have an out of the box way of mounting an ISO
  • Not only does Visual Studio 2013 need to be installed, but also CU5
  • The ESB requires Analysis Server to be properly configured with the appropriately configured
  • The hotfixes that needs to be installed don’t lend themselves easily to ‘silent’ installations

So to overcome the inability to mount iso files, I downloaded 7-Zip and for my 64bit Windows 7 OS, I chose the 64bit version

I downloaded all of the installation bits into the C:\Images folder.

So in Windows 7 to install IIS I ran the following command:

DISM /Online /Enable-Feature /FeatureName:IIS-ApplicationDevelopment /FeatureName:IIS-ASP /FeatureName:IIS-ASPNET /FeatureName:IIS-BasicAuthentication /FeatureName:IIS-CommonHttpFeatures /FeatureName:IIS-CustomLogging /FeatureName:IIS-HealthAndDiagnostics /FeatureName:IIS-HostableWebCore /FeatureName:IIS-HttpCompressionDynamic /FeatureName:IIS-HttpCompressionStatic /FeatureName:IIS-HttpErrors /FeatureName:IIS-HttpLogging /FeatureName:IIS-HttpRedirect /FeatureName:IIS-HttpTracing /FeatureName:IIS-IIS6ManagementCompatibility /FeatureName:IIS-IISCertificateMappingAuthentication /FeatureName:IIS-IPSecurity /FeatureName:IIS-ISAPIExtensions /FeatureName:IIS-ISAPIFilter /FeatureName:IIS-LegacyScripts /FeatureName:IIS-LegacySnapIn /FeatureName:IIS-LoggingLibraries /FeatureName:IIS-ManagementConsole /FeatureName:IIS-ManagementScriptingTools /FeatureName:IIS-ManagementService /FeatureName:IIS-Metabase /FeatureName:IIS-NetFxExtensibility /FeatureName:IIS-Performance /FeatureName:IIS-RequestFiltering /FeatureName:IIS-RequestMonitor /FeatureName:IIS-Security /FeatureName:IIS-ServerSideIncludes /FeatureName:IIS-StaticContent /FeatureName:IIS-URLAuthorization /FeatureName:IIS-WebDAV /FeatureName:IIS-WebServer /FeatureName:IIS-WebServerManagementTools /FeatureName:IIS-WebServerRole /FeatureName:IIS-WindowsAuthentication /FeatureName:IIS-WMICompatibility /FeatureName:WAS-ConfigurationAPI /FeatureName:WAS-NetFxEnvironment /FeatureName:WAS-ProcessModel /FeatureName:WAS-WindowsActivationService

(yes, I might not needed to install all of the features)

The next thing I needed to do was install Visual Studio and CU5. I did this by running the following commands (which unpacked, installed, and subsequently deleted the install data):

“C:\Program Files\7-Zip\7z.exe” x “C:\Images\en_visual_studio_premium_2013_x86_dvd_3175275.iso” -oc:\Images\VS\ * -r -y

c:\Images\VS\vs_premium.exe /adminfile C:\Images\AdminDeployment.xml /norestart /Passive

rmdir c:\Images\VS /S /Q

“C:\Program Files\7-Zip\7z.exe” x “C:\Images\mu_visual_studio_2013_update_5_x86_dvd_6815499.iso” -oc:\Images\VSsp5\ * -r -y

C:\Images\VSsp5\VS2013.5.exe /norestart /Passive

rmdir c:\Images\VSsp5 /S /Q

The next was installing SQL Server and the CU 8 

“C:\Program Files\7-Zip\7z.exe” x “C:\Images\en_sql_server_2012_enterprise_edition_with_service_pack_2_x64_dvd_4685849.iso” -oc:\Images\SQL\ * -r -y

C:\Images\SQL\setup.exe /ACTION=”Install” /SAPWD=”P4ssw0rd!” /SQLSYSADMINACCOUNTS=%USERDOMAIN%\%USERNAME% /INDICATEPROGRESS=”True” /ENU=”True” /QS=”True” /UpdateEnabled=”True” /ERRORREPORTING=”False” /UPDATEENABLED=”True” /FEATURES=SQLENGINE,AS,IS,SSMS,ADV_SSMS /UpdateSource=”MU” /HELP=”False” /INDICATEPROGRESS=”True” /X86=”False” /INSTALLSHAREDDIR=”C:\Program Files\Microsoft SQL Server” /INSTALLSHAREDWOWDIR=”C:\Program Files (x86)\Microsoft SQL Server” /INSTANCENAME=”MSSQLSERVER” /SQMREPORTING=”False” /INSTANCEID=”MSSQLSERVER” /INSTANCEDIR=”C:\Program Files\Microsoft SQL Server” /AGTSVCACCOUNT=”NT Service\SQLSERVERAGENT” /AGTSVCSTARTUPTYPE=”Automatic” /ISSVCSTARTUPTYPE=”Automatic” /ISSVCACCOUNT=”NT Service\MsDtsServer110″ /ASSVCACCOUNT=”NT Service\MSSQLServerOLAPService” /ASSVCSTARTUPTYPE=”Automatic” /ASCOLLATION=”Latin1_General_CI_AS” /ASDATADIR=”C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Data” /ASLOGDIR=”C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Log” /ASBACKUPDIR=”C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Backup” /ASTEMPDIR=”C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Temp” /ASCONFIGDIR=”C:\Program Files\Microsoft SQL Server\MSAS12.MSSQLSERVER\OLAP\Config” /ASPROVIDERMSOLAP=”1″ /ASSYSADMINACCOUNTS=”FCBTwin7\estott” /ASSERVERMODE=”MULTIDIMENSIONAL” /COMMFABRICPORT=”0″ /COMMFABRICNETWORKLEVEL=”0″ /COMMFABRICENCRYPTION=”0″ /MATRIXCMBRICKCOMMPORT=”0″ /SQLSVCSTARTUPTYPE=”Automatic” /FILESTREAMLEVEL=”0″ /ENABLERANU=”False” /SQLCOLLATION=”SQL_Latin1_General_CP1_CI_AS” /SQLSVCACCOUNT=”NT Service\MSSQLSERVER” /SECURITYMODE=”SQL” /ADDCURRENTUSERASSQLADMIN=”False” /TCPENABLED=”1″ /NPENABLED=”0″ /BROWSERSVCSTARTUPTYPE=”Automatic” /IAcceptSQLServerLicenseTerms=”True” /SQLUSERDBLOGDIR=”C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log” /SQLTEMPDBDIR=”C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Temp\Data” /SQLTEMPDBLOGDIR=”C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Temp\Log” /BROWSERSVCSTARTUPTYPE=”Automatic”

rmdir c:\Images\SQL /S /Q

“C:\Program Files\7-Zip\7z.exe” x C:\Images\487325_intl_x64_zip.exe -oc:\Images\SQLSP8\ * -r -y

C:\Images\SQLSP8\SQLServer2012-KB3082561-x64.exe /quiet /IAcceptSQLServerLicenseTerms=”True” /INDICATEPROGRESS=”True”

rmdir c:\Images\SQLSP8 /S /Q

Now that SQL was installed, I can install BizTalk, LOB adapters, and the ESB Toolkit by running the following command, I needed to reference the BizTalkConfig.xml in the setup.

“C:\Program Files\7-Zip\7z.exe” x “C:\Images\en_biztalk_server_2013_r2_enterprise_edition_x86_and_x64_dvd_4434625.iso” -oc:\Images\BTS\ * -r -y

“C:\Images\BTS\BizTalk Server\setup.exe” /PASSIVE /NORESTART /CABPATH C:\Images\BtsRedistWin7EN64.cab /ADDLOCAL ALL

“C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Configuration.exe” /S C:\Images\BizTalkConfig.xml

msiexec.exe /i “C:\Images\BTS\BizTalk Server\LOB\Msi\Microsoft_BizTalk_Adapters_for_Enterprise_Applications.msi” /norestart /qn /lv c:\lob.log

msiexec.exe /i “C:\Images\BTS\BizTalk Server\ASDK_x86\AdapterFramework.msi” /quiet MUOPTIN=”Yes”

msiexec.exe /i “C:\Images\BTS\BizTalk Server\ASDK_x64\AdapterFramework64.msi” /quiet MUOPTIN=”Yes”

msiexec.exe /i “C:\Images\BTS\BizTalk Server\AdapterPack_x86\AdaptersSetup.msi” /qn

msiexec.exe /i “C:\Images\BTS\BizTalk Server\AdapterPack_x64\AdaptersSetup64.msi” /qn

msiexec.exe /i “C:\Images\BTS\BizTalk Server\ESBT_x64\BizTalk ESB Toolkit 2.3.msi” /qn

rmdir c:\Images\BTS /S /Q

Now it is time for HIS to be installed. I ran the following command

“C:\Program Files\7-Zip\7z.exe” x “C:\Images\en_host_integration_server_2013_enterprise_edition_x86_and_x64_cd_2642074.iso” -oc:\Images\HIS\ * -r -y

c:\Images\HIS\setup.exe /InstallPlatform /s “C:\Images\HISConfix.xml”

rmdir c:\Images\HIS /S /Q

Next I needed to modify the SQL Jobs so that the purge and archive was ‘working’ as expected. I needed to create a folder where the contents were compressed and then reconfigure the job. For future steps, I needed to add the NT SERVICE\MSSQLServerOLAPService as a sysadmin. I ran the following command

sqlcmd -s (local) -i C:\Images\UpdateSQLJob.sql

To add a new user as an admin to SSAS, you have to import a xmla file. Since my account was already there, I needed to include by my and the NT SERVICE\MSSQLServerOLAPService to the file. I did this by running a powershell command against the template xmla file. I also needed to wait for the powershell script to finish running before going on. The @ping 127.0.0.1 command waits for 5 seconds to finish before going on.

cd C:\Images

powershell -Command “(gc Role.xmla) -replace ‘THISSERVER’,'%COMPUTERNAME%’ | Out-File CurrentRole.xmla”

powershell -Command “(gc CurrentRole.xmla) -replace ‘CURRENTUSER’,'%USERDOMAIN%\%USERNAME%’ | Out-File CurrentRole.xmla”

powershell -NoProfile -ExecutionPolicy Bypass -Command “& {Start-Process Powershell -Argumentlist ‘-NoProfile -ExecutionPolicy Bypass -File CreateUser.ps1′ -Verb RunAs}”;

@ping 127.0.0.1 -n 6 > nul

@del C:\Images\CurrentRole.xmla /f /q

The next, I needed to apply the two bam activities that come as part of the toolkit

“C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Tracking\bm.exe” deploy-all -definitionfile:”C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit\Bam\Microsoft.BizTalk.ESB.BAM.Exceptions.xml”

“C:\Program Files (x86)\Microsoft BizTalk Server 2013 R2\Tracking\bm.exe” deploy-all -definitionfile:”C:\Program Files (x86)\Microsoft BizTalk ESB Toolkit\Bam\Microsoft.BizTalk.ESB.BAM.Itinerary.xml”

The last part is applying the cumulative update for BizTalk

“C:\Program Files\7-Zip\7z.exe” x C:\Images\485668_ENU_i386_zip.exe -oc:\Images\BTSCU1\ * -r -y

C:\Images\BTSCU1\BizTalk2013R2_CU1-KB3055198-ENU.exe /quiet

 

Here is the content of the needed files:

AdminDeployment.xml

<?xml version=”1.0″ encoding=”utf-8″?>

<AdminDeploymentCustomizations xmlns=”http://schemas.microsoft.com/wix/2011/AdminDeployment”>

  <BundleCustomizations TargetDir=”default” NoWeb=”default”/>

  <SelectableItemCustomizations>

    <SelectableItemCustomization Id=”Blend” Hidden=”no” Selected=”no” />

    <SelectableItemCustomization Id=”LightSwitch” Hidden=”no” Selected=”no” />

    <SelectableItemCustomization Id=”VC_MFC_Libraries” Hidden=”no” Selected=”yes” />

    <SelectableItemCustomization Id=”OfficeDeveloperTools” Hidden=”no” Selected=”no” />

    <SelectableItemCustomization Id=”SQL” Hidden=”no” Selected=”yes” />

    <SelectableItemCustomization Id=”WebTools” Hidden=”no” Selected=”yes”/>

    <SelectableItemCustomization Id=”SilverLight_Developer_Kit” Hidden=”no” Selected=”no” />

    <SelectableItemCustomization Id=”Win8SDK” Hidden=”no” Selected=”no” />

    <SelectableItemCustomization Id=”WindowsPhone80″ Hidden=”no” Selected=”no” />

    <SelectableItemCustomization Id=”BlissHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”HelpHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”IntelliTraceHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”LocalDBHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”NetFX4Hidden” Selected=”yes” />

    <SelectableItemCustomization Id=”NetFX45Hidden” Selected=”yes” />

    <SelectableItemCustomization Id=”PortableDTPHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”PreEmptiveDotfuscatorHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”PreEmptiveAnalyticsHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”ProfilerHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”ReportingHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”SDKTools3Hidden” Selected=”yes” />

    <SelectableItemCustomization Id=”SDKTools4Hidden” Selected=”yes” />

    <SelectableItemCustomization Id=”Silverlight5DRTHidden” Selected=”no” />

    <SelectableItemCustomization Id=”SQLCEHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”SQLCLRTypesHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”SQLDACHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”SQLDOMHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”SQLSharedManagementObjectsHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”StoryboardingHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”TSQLHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”VCCompilerHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”VCCoreHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”VCDebugHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”VCDesigntimeHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”VCExtendedHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”WinJSHidden” Selected=”yes” />

    <SelectableItemCustomization Id=”WinSDKHidden” Selected=”yes” />

  </SelectableItemCustomizations>

</AdminDeploymentCustomizations>

 

 

There are two things we are going to discuss in this post:

  1. Dealing with namespaces in tsql without using the WITH XMLNAMESPACES statement
  2. Setting setting data from a sql variable in the modify statement

I have always struggled with the correct placement of the WITH XMLNAMESPACES statement: I can’t get it in the correct place or ‘escaped’ correctly, so I can never save the stored procedure. So we will show how you can use the methods to deal with XML without having to declare the WITH XMLNAMESPACES statement and in this case we are going to modify the existing value, passing in the variable from the sql statement.

To remove the use of WITH XMLNAMEPACES statement, you simply have to put in your xml method the following statement: ’declare namespace ns0=”http://whateveryournamespaceis.com”; {your work here}’

When using the modify statement, I wanted to pass a variable in from the stored procedure; you have to use the with sql:variable statement

Here is a simple example:

declare @xml XML = 
'<XML xmlns="http=stottcreations.com/data">
   <Record>
      <Element>Value1</Element>
   </Record>
 </XML>'
 declare @replaceValue char(5)='ABCDE'
 set @xml.modify('declare namespace ns0="http=stottcreations.com/data";
                  replace value of(ns0:XML/ns0:Record/ns0:Element/text())[1]
                  with sql:variable("@replaceValue")')
 select @xml

Which results in this xml

<XML xmlns="http=stottcreations.com/data">
  <Record>
    <Element>ABCDE</Element>
  </Record>
</XML>

Notice that the original xml did not have a namespace prefix (ns0), however I added it to the queries.

Also notice in the sql the locations of the single and double quotes.

XSL Tricks

 Uncategorized  Comments Off
Feb 112015
 

I have mentioned this a couple of times before:

I HATE FUNCTIODS

I have reverted to using XSL for nearly all mapping requirements (except straight across mapping)

I have created a simple map that maps pretty much straight across using XSL:

Here is one of the functiods:

This has always worked, the issue is that if I change the context, ALL of my Inline XSLT scripts are broken:

What I have done before is go into each scripting functiod and change the XSLT. – What a pain.

Here I am going to show you a ‘trick’ to save yourself the headache.

We are going to use Inline XSLT Call Template

I have always had an issue with a Call Template, “I am creating a template to use, but nothing is ‘reusable’ in my mind” For instance: my output to FName isn’t reusable in any other scenario.

We are going to show how it can be reusable!

I am going to create a mapping for the FName, but to make it reusable, I am going to ‘pass’ into the call template the source node and output name:

Now for the fun, I want to create this template so I can reuse it, so here is what I have written:

The code:

 <xsl:element name="($OutputNode)">

is essentially saying: I want the real value of this variable as an output

All I have to do is copy the contents of this script and create new functiods and just put the output node name as an argument in the scripting functiod

Here is my map

Here is the underlying XSL

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://StottCreations.com/XSL.DataDefinition">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns0:Input" />
  </xsl:template>
  <xsl:template match="/ns0:Input">
    <ns0:Output>
      <Line>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@FirstName)" />
          <xsl:with-param name="OutputNode" select="&quot;FName&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@LastName)" />
          <xsl:with-param name="OutputNode" select="&quot;LName&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@Address)" />
          <xsl:with-param name="OutputNode" select="&quot;AddressLine&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@City)" />
          <xsl:with-param name="OutputNode" select="&quot;City&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@State)" />
          <xsl:with-param name="OutputNode" select="&quot;State&quot;" />
        </xsl:call-template>
        <xsl:call-template name="MakeOutput">
          <xsl:with-param name="InputValue" select="string(Record/@Zip)" />
          <xsl:with-param name="OutputNode" select="&quot;ZipCode&quot;" />
        </xsl:call-template>
      </Line>
    </ns0:Output>
  </xsl:template>
  <xsl:template name="MakeOutput">
 <xsl:param name="InputValue" />
 <xsl:param name="OutputNode" />
 <xsl:element name="($OutputNode)">
   <xsl:value-of select="$InputValue" />
 </xsl:element>
</xsl:template>
</xsl:stylesheet>

Now if I change the context, I don’t have to remap anything!

And here is the underlying XSL (without me having to go in an change anything in the scripting functiods)

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" exclude-result-prefixes="msxsl var" version="1.0" xmlns:ns0="http://StottCreations.com/XSL.DataDefinition">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns0:Input" />
  </xsl:template>
  <xsl:template match="/ns0:Input">
    <ns0:Output>
      <xsl:for-each select="Record">
        <Line>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@FirstName)" />
            <xsl:with-param name="OutputNode" select="&quot;FName&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@LastName)" />
            <xsl:with-param name="OutputNode" select="&quot;LName&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@Address)" />
            <xsl:with-param name="OutputNode" select="&quot;AddressLine&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@City)" />
            <xsl:with-param name="OutputNode" select="&quot;City&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@State)" />
            <xsl:with-param name="OutputNode" select="&quot;State&quot;" />
          </xsl:call-template>
          <xsl:call-template name="MakeOutput">
            <xsl:with-param name="InputValue" select="string(@Zip)" />
            <xsl:with-param name="OutputNode" select="&quot;ZipCode&quot;" />
          </xsl:call-template>
        </Line>
      </xsl:for-each>
    </ns0:Output>
  </xsl:template>
  <xsl:template name="MakeOutput">
 <xsl:param name="InputValue" />
 <xsl:param name="OutputNode" />
 <xsl:element name="($OutputNode)">
   <xsl:value-of select="$InputValue" />
 </xsl:element>
</xsl:template>
</xsl:stylesheet>
Feb 042015
 

Building off the previous entry, I want to map data from the OBR3.1 into each corresponding OBX15.1

Originally, I was lazy, and just mass copied the input to the output

<OBX_ObservationResult>
  <xsl:copy-of select="./@*" />
  <xsl:copy-of select="./*" />
</OBX_ObservationResult>

So I need to create a mapping for each element so I can map the specific element. As I am too lazy to do start typing, I use the BizTalk mapper and drag the OBX_ObservationResult and link it by structure. I get this:

I then validate the map so I can see the underlying xsl:

<xsl:for-each select="OBX_ObservationResult[2]">
  <OBX_ObservationResult>
  <xsl:if test="OBX_1_SetIdObx">
    <OBX_1_SetIdObx>
      <xsl:value-of select="OBX_1_SetIdObx/text()" />
    </OBX_1_SetIdObx>
  </xsl:if>
  <xsl:if test="OBX_2_ValueType">
    <OBX_2_ValueType>

Since I already have the for-each properly defined all I really need to copy is the children of the OBX_ObservationResult logic to my real map.

Once I have got it in the map, I simply need to map the OBR3.1 data into the OBX15.1

So I look at the code that maps to the OBX15

<xsl:for-each select="OBX_15_ProducerSId">
  <OBX_15_ProducerSId>
    <xsl:if test="CE_0_Identifier">
      <CE_0_Identifier>
        <xsl:value-of select="CE_0_Identifier/text()" />
      </CE_0_Identifier>
    </xsl:if>
    <xsl:if test="CE_1_Text">
      <CE_1_Text>
        <xsl:value-of select="CE_1_Text/text()" />
      </CE_1_Text>
    </xsl:if>
    <xsl:if test="CE_2_NameOfCodingSystem">
      <CE_2_NameOfCodingSystem>
        <xsl:value-of select="CE_2_NameOfCodingSystem/text()" />
      </CE_2_NameOfCodingSystem>
    </xsl:if>
    <xsl:if test="CE_3_AlternateIdentifier">
      <CE_3_AlternateIdentifier>
        <xsl:value-of select="CE_3_AlternateIdentifier/text()" />
      </CE_3_AlternateIdentifier>
    </xsl:if>
    <xsl:if test="CE_4_AlternateText">
      <CE_4_AlternateText>
        <xsl:value-of select="CE_4_AlternateText/text()" />
      </CE_4_AlternateText>
    </xsl:if>
    <xsl:if test="CE_5_NameOfAlternateCodingSystem">
      <CE_5_NameOfAlternateCodingSystem>
        <xsl:value-of select="CE_5_NameOfAlternateCodingSystem/text()" />
      </CE_5_NameOfAlternateCodingSystem>
    </xsl:if>
    <xsl:value-of select="./text()" />
  </OBX_15_ProducerSId>
</xsl:for-each>

I want to remove the ‘dependency’ on the input of of the OBX15, I want to create it regardless. So I remove the for-each wrapping around the OBX15 node

I also need access to to OBR data, so i create a variable that I can access in my for-each loop

  <xsl:template match="OBR_ObservationRequest">
    <xsl:variable name="Anchor" select="OBR_1_SetIdObr/text()"/>
    <xsl:variable name="OBR31" select="OBR_3_FillerOrderNumber/EI_0_EntityIdentifier/text()"/>

So now I want to map from my variable into the proper location:

<CE_1_Text>
  <xsl:value-of select="$OBR31"/>
</CE_1_Text>

 

Which now creates the following output:

    <OBX_15_ProducerSId>
      <CE_1_Text>B13573501L</CE_1_Text>
    </OBX_15_ProducerSId>
  </OBX_ObservationResult>
</ns1:ORU_R01_25_GLO_DEF>

Mapping HL7 Data

 Uncategorized  Comments Off
Feb 032015
 

So I wanted to show how to map an HL7 message.
Here is the schema view:

Here is a collapsed view of the data:

Now you can’t simply use the mapping functiods to map it straight across, the sequence groups ‘appear’ to indicate what you think is a structure, however, the resulting XML from the BTAHL7 pipeline component creates a ‘flat’ structure.

So we are going to create custom XSL and going to map straight across, input to output. So I created the map and drew lines across to get the initial xsl structure defined.

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" 
                exclude-result-prefixes="msxsl var" version="1.0" 
                xmlns:ns0="http://stottcreations.com/HealthCare/HL7/2X/2.5/Segments" 
                xmlns:ns2="http://stottcreations.com/HealthCare/HL7/2X/2.5/Tables" 
                xmlns:ns3="http://stottcreations.com/HealthCare/HL7/2X/2.5/DataTypes" 
                xmlns:ns1="http://stottcreations.com/HealthCare/HL7/2X">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns1:ORU_R01_25_GLO_DEF" />
  </xsl:template>
  <xsl:template match="/ns1:ORU_R01_25_GLO_DEF">
    <ns1:ORU_R01_25_GLO_DEF>
      <xsl:value-of select="./text()" />
    </ns1:ORU_R01_25_GLO_DEF>
  </xsl:template>
</xsl:stylesheet>

So we need to start mapping the OBR segment: so let’s create a template for it.

    <ns1:ORU_R01_25_GLO_DEF>
      <xsl:apply-templates select="OBR_ObservationRequest"/>
    </ns1:ORU_R01_25_GLO_DEF>

Now in the OBR template we need to set an achor (since it is a flat structure) of the OBR1SetIdObr/text())

  <xsl:template match="OBR_ObservationRequest">
    <xsl:variable name="Anchor" select="OBR_1_SetIdObr/text()"/>

Now we actually start creating the OBR segment

    <OBR_ObservationRequest>
      <xsl:copy-of select="./@*" />
      <xsl:copy-of select="./*" />
    </OBR_ObservationRequest>

Now, we need to map any NTE segment, following the OBR Segment

    <xsl:for-each select="following-sibling::NTE_NotesAndComments[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <NTE_NotesAndComments>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </NTE_NotesAndComments>
    </xsl:for-each>

And we need to map any OBX segment following the OBR Segment also

    <xsl:for-each select="following-sibling::OBX_ObservationResult[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <OBX_ObservationResult>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </OBX_ObservationResult>
    </xsl:for-each>

The entire xsl is here:

<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var" 
                exclude-result-prefixes="msxsl var" 
                version="1.0" xmlns:ns1="http://stottcreations.com/HealthCare/HL7/2X" 
                xmlns:ns0="http://stottcreations.com/HealthCare/HL7/2X/2.5/Segments">
  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" indent="yes" />
  <xsl:template match="/">
    <xsl:apply-templates select="/ns1:ORU_R01_25_GLO_DEF" />
  </xsl:template>
  <xsl:template match="/ns1:ORU_R01_25_GLO_DEF">
    <ns1:ORU_R01_25_GLO_DEF>
      <xsl:apply-templates select="OBR_ObservationRequest"/>
    </ns1:ORU_R01_25_GLO_DEF>
  </xsl:template>
  <xsl:template match="OBR_ObservationRequest">
    <xsl:variable name="Anchor" select="OBR_1_SetIdObr/text()"/>
    <OBR_ObservationRequest>
      <xsl:copy-of select="./@*" />
      <xsl:copy-of select="./*" />
    </OBR_ObservationRequest>
    <xsl:for-each select="following-sibling::NTE_NotesAndComments[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <NTE_NotesAndComments>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </NTE_NotesAndComments>
    </xsl:for-each>
    <xsl:for-each select="following-sibling::OBX_ObservationResult[preceding-sibling::OBR_ObservationRequest[1]/OBR_1_SetIdObr/text()=$Anchor]">
      <OBX_ObservationResult>
        <xsl:copy-of select="./@*" />
        <xsl:copy-of select="./*" />
      </OBX_ObservationResult>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>

Here is the result

There is a follow up post on doing a bit more complicated mapping logic in my Mapping HL7 Data Part 2

 

I am going to write a ‘visual’ example of the article documented here: Performing Composite Operations on Oracle Database by Using BizTalk Server

I have a schema already created from Oracle that defines all of the stored procedures we are planning on calling from within BizTalk

The issue is that I need to send multiple of the same calls to the stored procedure and multiple stored procedure calls.

Now we are going to create the composite Schema Definition.

Let’s import the original schema, and for whatever reason, I needed to add the ns0 to the prefix.

 

So let’s change the root node name to Request (like it asks us to in the instruction)

And start adding the things we want to the Request node (making them repeatable), however, unlike the instructions, I am not naming it, because once I choose the correct node, it is going to rename it anyway:

Make it min 0 and max * and we are done with the first one.

And do it for the rest of the stored proc calls.

Let’s create the Response and do the same thing, except choosing all of the Response nodes. Here is the final schema

 

Now you can start mapping…

© 2016 BizTalk Blog Suffusion theme by Sayontan Sinha