BizTalk Value Cross Reference

If you dont have idea on Biztalk Cross Reference data. Please go through my previous blog on Biztalk Cross Reference

Value CROSS REFERENCE:

Scenario:

Field	Common Value	Application1 ID	Application 2 ID	Application 3 ID
Type	tax	TA	Bounded	ST

Solution:

1. Create XML files that represent cross reference data.

   listOfAppType.xml

   

   listOfAppInstance.xml

   

   listOfValueXRef.xml

   

   listOfValueXRefData.xml

   

   SetupFile.xml

   

2. Now you have all the data that you need for your Value cross Reference. Now deploy these data to the BizTalk Management Database.

   Give the following Command in your command prompt.

   btsxrefimport –file=SetupFile.xml

3. Using the cross reference in your map.

   From the Tool box drop the Get Common Value Functoid and connect the type field from your source schema to it.

   

   Double Click the Get Common ID Functoid and add 2 more inputs as shown below

   

   Re arrange the input parameters order as follows. Otherwise you will get GetCommonValue() functiod failed as the method takes the parameters as follows:

   GetCommonValue(string valueXRef, string appType, string appValue);Ex:

   Ex:

   CrossReferencing.GetCommonValue("Type", "Application1", "TA")

   

   Now drop the Get Application Value Functoid and connect the output of Get Common Value Functoid to this one.

   

   Configure the Get Application Value Functoid Inputs as shown below.

   

   Re arrange the input parameters order as follows. Otherwise you will get GetAppValue() functiod failed as the method takes the parameters as follows:

   GetAppValue(string valueXRef, string appType, string commonValue)

   Ex:

   strCommonValue = CrossReferencing.GetCommonValue("Type", "Application1", "TA");

   strCompany2AppValue = CrossReferencing.GetAppValue("Type", "Application2", strCommonValue);

   

   The Output of this Functoid will be the equivalent Application2 value for the Application1 Input.

   Similarly Map for the Application1 and Application3.

Output:

Input message:

Output message:

BizTalk ID Cross Reference

If you dont have idea on Biztalk Cross Reference data. Please go through my previous blog on Biztalk Cross Reference

ID Cross Reference:

Scenario:

Field	Common ID	Application1 Value	Application2 Value	Application03 Value
Status	1	Open	Entered	Started
Status	2	Closed	Done	Finished

Solution:

1. Create XML files that represent cross reference data.

   listOfAppType.xml

   

   listOfAppInstance.xml

   

   listOfIDXRef.xml

   

   listOfIDXRefData.xml

   

   SetupFile.xml

   

2. Now you have all the data that you need for your ID cross Reference. Now deploy these data to the BizTalk Management Database.

   Give the following Command in your command prompt.

   btsxrefimport –file=SetupFile.xml

3. Using the cross reference in your map.

   From the Tool box drop the Get Common ID Functoid and connect the Status field from your source schema to it.

   

   Double Click the Get Common ID Functoid and add 2 more inputs as shown below

   

   Re arrange the input parameters as follows otherwise GetCommonID() functiod failed as the method takes the parameters as follows:

   GetCommonID(string idXRef, string appInstance, string appId);

   Ex: CrossReferencing.GetCommonID("Status", "Application1Instance", "Open");

   

   Now drop the Get Application ID Functoid and connect the output of Get Common ID Functoid to this one.

   

   Configure the Get Application ID Functoid Inputs as shown below.

   

   Re arrange the input parameters order as follows. Otherwise you will get GetAppID() functiod failed as the method takes the parameters as follows:

   GetAppID(string idXRef, string appInstance, string commonId)

   Ex:
   strCommonID = CrossReferencing.GetCommonID("Status", "Application1Instance", "Open");
   strApp22AppID = CrossReferencing.GetAppID("status", "Application2Instance", strCommonID);

   

   The Output of this Functoid will be the equivalent Application2 value for the Application1 Input.

   Similarly Map for the Application1 and Application3.

   Output:

   Input message:

   

   Output message:

   

Read about Biztalk Value Cross reference here.

Windows Azure Biztalk Services: Windows Biztalk Adapter Service Setup wizard ended prematurely

While installing windows biztalk adapter service, you may get the following error.

Microsoft BizTalk Adapter Service Setup Wizard ended prematurely because of an error.  Your system has not been modified.  To install this program at a later time, run Setup Wizard again. 

This is due to insufficient privileges to the access "C:\Windows\System32\inetsrv\config". This may be due to User access control(UAC) in your machine or User doesn't have full permissions to that folder. To resolve this issue

1. Stop UAC and grant permission to that user to config folder
2. Open the command prompt in run as administrator mode and run the Msiexec command as follows.

msiexec /i BizTalkAdapterService.msi /l*vx install_log.txt

Windows Azure Biztalk Services: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

While deploying Biztalk Services to Windows Azure, you may get the following exception.

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

To resolve this issue download the SSL Certificate from the Azure portal and import to local machine Trusted Certificates.

Steps to download and import certificate to local machine:

1. Go to biztalk service from management portal
2. Click on Dashboard
3. Download SSL Certificate
   
4. In run type certmgr.msc 
5. Go to Trusted root certification authorities -> Certificates
6. Right click on Certificates=>All tasks => Import
   
7. Select downloaded certificate file and click on next=>next to import the certificate
8. You will get an alter message that Imported successfully.

Windows Azure Biztalk SErvices: Unable to load the binding: 'sqlBinding'

This is an issue with the installation of  Microsoft Biztalk Apater Pack. Some times it will not set the apater configuaration in the Machine.config. We can set the configuaration mannually to resolve this issue.

Please find the article at http://msdn.microsoft.com/en-us/library/dd787990.aspx#BKMK_SQLSetupBinding to troubleshoot this issue. 

Windows Azure Biztalk Adapter Service: There was no endpoint listening at 'XXX' that could accept the message. This is often caused by an incorrect address or SOAP action

When I am working with Windows Azure Biztalk Services. I was encountered one issue as follows:

There was no endpoint listening at https://xxx.servicebus.windows.net/biztalkadapterdemo/event/ that could accept the message. This is often caused by an incorrect address or SOAP action. See InnerException, if present, for more details.

 After lots of searching online and looking at logs etc it turned out to be a simple solution to just restart the WAS service (Windows Process Activation Service)

Windows Azure Biztalk Adapter Service: ConnectionException: Login Failed for user 'XXX'

When you are working with the Windows Azure Biztalk Adapter Services and SQL LOB Type. You may get the following exception.

 ConnectionException: Login Failed for user 'XXX' 

The reason is the provided user is unable to authenticate SQL Database. Verify your login details for having permissions to that data base or not.

For Windows Azure Biztalk Services, you can authenticate through the following 4 different options:

FIXED USER: This is the SQL authentication that we provided in the machine.
FIXED WINDOWS CREDENTIALS: You can provide a Domain, Username and Password to connect to the SQL Database.
CUSTOM SOAP HEADER: The client as a part of message is going to sent a UserName and Password to connect to Sql Database. This will come as part of the SOAP header.
MESSAGE CREDENTIAL: Client will send username and Password in WS-Security header of the message.

Enable full permissions for local administrator in Widows8/ 8.1

In Windows 8.1: I'm an administrator but I don't have administrator privileges?  I observed that, even after changing the UAC(User Access control) to Never Notify, still some programs are not run under full privileges. The problem is Security policy in Windows 8/8.1. Every administrator other than built in administrator must run all the applications in Admin approval mode. Steps to disable the admin approval mode is find below.

1. Type secpol.msc in the Start Menu and press Enter.
2. Double click on Local Policies then double click on Security Options.
3. Scroll to the bottom to this entry -User Account Control: Run all administrators in Admin approval mode.
4. Double click that line.
5. Set it to disabled then press OK.
6. Reboot.

UAC(User Access Control) options are 

Enabled: (Default) Admin Approval Mode is enabled. This policy must be enabled and related UAC policy settings must also be set appropriately to allow the built-in Administrator account and all other users who are members of the Administrators group to run in Admin Approval Mode. 

Disabled: Admin Approval Mode and all related UAC policy settings are disabled. Note: If this policy setting is disabled, the Security Center notifies you that the overall security of the operating system has been reduced.

Warning: Metro apps will not work after disabling Admin approval mode. You need enable and restart machine again to run metro apps.

Biztalk Cross Reference

Reference Data:

Reference data means, it is an object within a system that is restricted to specific list of values. In integration scenarios when you are passing a message from one system to another these lists of reference data are different between the systems. (Where a particular field of the source maps to another field in destination. But their values aren’t so agreeable)

If we use an EAI (Enterprise Application Integration) pattern, you can have one input and more than one output flow. So, in this case, you must translate the property Value or Id in a Reference Value or Id.

In this case consider the example diagram:

Example Scenario:

We have a field called status in Application1 which field named mapped to status in Application2 and status in Application3. But the possible list of values are different.

Field	Application 1	Application 2	Application 3
Status	Open	Entered	Started
Status	Closed	Done	Finished

How to solve this issue?

There are many possible ways.
Write an inline script / xslt in a Scripting Functoid

1.        Use an External dll that takes an input and returns an equivalent output
2.        Use of BizTalk Server Cross Reference Functoids.
3.        Custom Functoids and Lots of more options….

Explained here for pros and cons of each solution.

This article provide solution by using Cross Reference Functiods. The cross referencing database functoids use data from tables stored in the BizTalkMgmtDb SQL Server database. The functoids available and XML files that represents the reference data listed below.

Functiods available in Biz talk:

Get Application ID	Retrieves an identifier for an application object.
Get Application Value	Retrieves an application value.
Get Common ID	Retrieves an identifier for a common object.
Get Common Value	Retrieves a common value.

All these functiods are listed under Database functiods group.

XML files that represents the cross reference data:

SetUp-Files	This will have the path to all other files
listOfAppType	This will have the list of applications. In our case it is “Application1″ ,“Application2″ and “Application3”
listOfAppInstance	For each Application created, an instance should be created
listOfIDXRef	This XML file will hold the IDs for which we create Data. In our case it is the “Status”
listOfIDXRefData	This file will hold the data for Common ID, “Application1″ data “Application2″ Data, and “Application3” data
listOfValueXRef	This XML file will hold the IDs for which we create Data. In our case it is the “type”
listOfValueXRefData	This file will hold the data for Common value, “Application1″ data “Application2″ Data, and “Application3” data
listOfMessageDef	This XML file hold the information related to message codes
listOfMessageText	This XML file hold the information related to message binding to the message code

You can find in MSDN for more information about document structure and how to import data for Cross referencing functoids.

Note: we can have any name for file but the format of data should be as per documentation.

Solution Approach:

1.        Create a XML files that represents the cross reference data.
2.        These XML files (data) are imported to a set of Tables inside BizTalk Management Database (BizTalkMgmtDb) using BizTalk Server Cross Reference Import tool (btsxrefimport.exe)

Cross Reference Types:

There are 2 types of Cross Reference – 

1.        ID Cross Reference
2.    Value Cross Reference

Deserializing CSV file to Collection

Every one know that CSV file stores the tabular data in plain text format.A CSV file consists of any number of records, separated by line breaks of some kind; each record consists of fields, separated by some other character or string, most commonly a literal comma or tab. Usually, all records have an identical sequence of fields. I have a requirement like I want to read the CSV file and Convert to Collection of objects. Steps to convert the CSV file to collection as follows.

1. Read all the lines from the CSV file
2. Take the first line as the header and remaing lines as records
3. Convert the data to data table by parsing first line to data columns and remaining lines to data rows
4. Convert data table to collection

Sample code to implement step 1 to 3

 public static DataTable ToDataTable(string csvFileNameIncludingPath)
        {
            var dt = new DataTable();

            var s = File.ReadAllLines(csvFileNameIncludingPath);
            string[] tableData = s.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
            var col = from cl in tableData[0].Split(",".ToCharArray())
                      select new DataColumn(cl.TrimStart('"').TrimEnd('"')); // In my case all the field values are included in the double quotes ex: "FirstName". So used TrimStart and TrimEnd methods to remove double quotes
            dt.Columns.AddRange(col.ToArray());

            (from st in tableData.Skip(1)
             select dt.Rows.Add(st.Split(",".ToCharArray()).Select(str => str.TrimStart('"').TrimEnd('"')).ToArray())).ToList(); // In my case all the field values are included in the double quotes ex: "jhon". So used TrimStart and TrimEnd methods to remove double quotes

            return dt;
        }

Sample code to implement step 4

 public static List<T> ToCollection<T>(this DataTable dt, IList<KeyValuePair<string, string>> changedNames = null) // changeNames parameter used for mapping the column name change with object propertyname
        {
            List<T> lst = new List<T>();
            Type tClass = typeof(T);
            PropertyInfo[] pClass = tClass.GetProperties();
            List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
            T cn;
            foreach (DataRow item in dt.Rows)
            {
                cn = (T)Activator.CreateInstance(tClass);
                foreach (PropertyInfo pc in pClass)
                {
                    // Can comment try catch block. 
                    try
                    {
                        DataColumn d = dc.Find(c => c.ColumnName == pc.Name || (changedNames != null && changedNames.Any(x => x.Key == c.ColumnName)));
                        var ds = dc.Where(x => x.ColumnName == pc.Name);
                        if (d != null)
                            pc.SetValue(cn, item[pc.Name], null);
                    }
                    catch
                    {
                    }
                }
                lst.Add(cn);
            }
            return lst;
        }

I hope this will helps you.

DataSet Deserialization from Webservice

I observed that few of the webservices or REST services returns the DataSet with the xml read mode as DiffGram. But our requirement is to deserialize into collection of object of sepecified type. The deserialization of dataset is different from the normal xml or json deserialization. Please find the below steps to deserialize dataset.

Step1: Create the response to DataSet object as follows.

private DataSet GetDataSet(string url)
        {
            DataSet ds = new DataSet();
            using (var client = new WebClient())
            {
                var xml = client.DownloadString(sURL);
                using (var reader = XmlReader.Create(new StringReader(xml)))
                {
                    ds.ReadXml(reader, XmlReadMode.DiffGram);
                }
            }
            return ds;
        }

Step 2: Use the following helper method to Convert Data Table to Collection

 public static List<T> ToCollection<T>(this DataTable dt)
        {
            List<T> lst = new System.Collections.Generic.List<T>();
            Type tClass = typeof(T);
            PropertyInfo[] pClass = tClass.GetProperties();
            List<DataColumn> dc = dt.Columns.Cast<DataColumn>().ToList();
            T cn;
            foreach (DataRow item in dt.Rows)
            {
                cn = (T)Activator.CreateInstance(tClass);
                foreach (PropertyInfo pc in pClass)
                {
                    // Can comment try catch block. 
                    try
                    {
                        DataColumn d = dc.Find(c => c.ColumnName == pc.Name);
                        if (d != null)
                            pc.SetValue(cn, item[pc.Name], null);
                    }
                    catch
                    {
                    }
                }
                lst.Add(cn);
            }
            return lst;
        }

Step 3: Use the methods as follows to Convert DataTable to collection

var ds= GetDataSet(url);
ds.Tables[DataTables.LotTable].ToCollection<Lot>();

I hope this helps you..

Cannot compare elements of type 'System.Collections.Generic.List

We may get an exception when we are working with lamda expression as follows.

Cannot compare elements of type 'System.Collections.Generic.List`1'. Only primitive types, enumeration types and entity types are supported.

The solution to resolve this issue is, make sure don't have any null condition checks in the where clause.

Get Bin Directory Path using C# for web application as well as class library

We may get the requirement while coding in C# like we need to get the bin path  of class library or Web application.

the following is the sample code which works for class library as well as web application.

string binPath = Path.GetDirectoryName((new System.Uri(Assembly.GetExecutingAssembly().CodeBase)).LocalPath)

Application pool always getting stopped

I have a problem like SecurityTokenServiceApplicationPool always getting stopped when I am opening Sharepoint manager 2013.
After research for this problem on google and gone through the EventViewer and found that this is the problem with the user credentials for the applicationpool running under the specified user. Password for my user got expired.

Solution: Make sure password for the user is correct and it is not expired. If it is expired please update with new password.