Archive

Archive for December, 2011

ESB Toolkit 2.1 using Enterprise Library 5.0

December 16, 2011 4 comments

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=4.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35’ in the context ‘Default’ at location ‘C:\Windows\assembly\GAC_MSIL\Microsoft.Practices.EnterpriseLibrary.Common\4.1.0.0__31bf3856ad364e35\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:

Solution:

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=4.1.0.0,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=4.1.0.0,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=2.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ applicationName=”ESB” description=”ESB SSO Configuration” contactInfo=”someone@microsoft.com” userGroupName=”BizTalk Application Users” adminGroupName=”BizTalk Server Administrators”/></sources></enterpriseLibrary.ConfigurationSource>

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

Pure C# ETL (Extract Transform Load)

December 15, 2011 1 comment

With regards to ETL (Extract Transform Load) the default tool to use for the job is Microsoft SQL Server Intergration Services (SSIS) hands down.  The way SSIS works is, it stores the information about the source and target in it’s metadata, all the column size, types and length are stored in it. The problem arises when data type or size changes and the metadata gets out of sync. In order to fix it, you need to open the package and refresh either the source or destination shape so it could pickup the updated metadata information.

We had this project that we need to build an ETL solution that has ff requirements:  process a complex xml file (multi-level and self-referencing structure), should support multiple version of data,  configurable in runtime and not too sensitive with the data type.  I end up building a flexible ETL solution using C#  with the help of Unity Framework, AutoMapper, LinqToSql and stored procedure for bulk insert.

Library information:

1. Unity – dependency injection framework.

2. Automapper – object auto-mapping

The processing step  is broken down into 6 parts:

1. When xml comes in it will be Deserialize into an object.

2. Using reflection loop thru the object (every properties).

3. Get the type of the object and create data processor using Unity if there’s no instance, if there’s an instance invoke the existing one.

4. Call the data processor to convert from deserialize format to the data entities using AutoMapper and add the converted instance to it’s internal collection (collection of objects).

5. After looping, instruct every data processor to commit the data.

6. Get Processing and error information.

 Implementation:

Step 1. Deserialization

1.1 First you need a schema for the xml, then use the XSD.exe tool to generate the class that can be used in deserialization process.

1.2 To deserialize an xml to the object use the following code:

/// <summary>
/// Deserialize Xml
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="path"></param>
/// <returns></returns>
protected T DeserializeFile <T> (string path)
{
T item;
using (StreamReader sr = new StreamReader(path))
{
XmlSerializer xs = new XmlSerializer(typeof(T));
item = (T)xs.Deserialize(sr);
}
return item;
}

Step 2,3& 4. Loop thru every properties using Reflection, for every object create a data processor using Unity then instruct it to add it to it’s internal collection. Each data processor would hold a collection equal to it’s type:

To loop to every property of the object using reflection use the following code:


private DataProcessorManager mDataProcessorManager;

/// <summary>
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="item"></param>
/// <param name="parentID"></param>
protected virtual void Parse<T>(T item, string parentID)
{
if (item != null)
{
Type obj = item.GetType();
//Check if object is an array
if (item.GetType().IsArray)
{
Array array = item as Array;
//Loop every object and process it one by one
foreach (var o in array)
{
//Process the object and retrieves the reference GUID
ProcessSegment(o, parentID);

string refNo = o.GetPropertyValue("GUID");
//Check whether the object contains other properties that is an object or array of an object aside
//from primitive types or types to be skipped.
foreach (PropertyInfo p in o.GetType().GetProperties())
{
if (IsPropertyToBeProcessed(p))
{
//Get Object property
var prop = p.GetValue(o, null);
//Recursive Call
if (prop != null)
{
Parse(prop, refNo);
}
}
}
}
}
else
{
//Process the object and retrieve the GUID
ProcessSegment(item, parentID);
string refNo = item.GetPropertyValue("GUID");
//Check whether the object contains other properties that is an object or array of an object aside
//from primitive types or types to be skipped.
foreach (PropertyInfo p in obj.GetProperties())
{
if (IsPropertyToBeProcessed(p))
{
//Get Object property
var prop = p.GetValue(item, null);
//Recursive Call
if (prop != null)
{
Parse(prop, refNo);
}
}
}
}
}
}

/// <summary>
/// </summary>
/// <param name="item"></param>
/// <param name="refNo"></param>
protected virtual void ProcessSegment(object item, string refNo)
{
//Get the type of object to be processed
Type itemType = item.GetType();

//Top Level (Skip)
if (!mToBeSkippedTypeList.Exists(i=> i.Equals(itemType)))
{
//Check whether there's a data processor for that object
mDataProcessorManager.ConvertAndAdd(item, refNo);
}
}

/// <summary>
/// Check whether this object is what we need to process, exclude
/// primitive types like Int32, String and etcs.
/// </summary>
/// <param name="propertyInfo"></param>
/// <returns></returns>
protected bool IsPropertyToBeProcessed(PropertyInfo propertyInfo)
{
bool toBeProcessed = true;
if (propertyInfo == null)
{
toBeProcessed = false;
return toBeProcessed;
}
//Check whether the object is not primitive int, byte etc. and not on the list of type
//to be skipped.
if (mToBeSkippedTypeList.Exists(i => i.Equals(propertyInfo.PropertyType)))
{
toBeProcessed = false;
}
return toBeProcessed;
}

To create an instance of data processor using Unity:


//Resolve Processor Using Unity
try
{
//Try to create a data processor using Unity, basically all DataProcess inherits from DataProcessorFactory
DataProcessorFactory f = mUnityContainer.Resolve<DataProcessorFactory>(itemType.Name);
//In this method call AutoMapper for explicit mapping, those property names that doesn't match
f.SetMapper();
mProcessorCollection.Add(itemType, f);
}
catch (ResolutionFailedException unityExc)
{
//Add the itemType to unsupported object
if (!mListOfUnsupportedTypes.Contains(itemType))
{
mListOfUnsupportedTypes.Add(itemType);
}
isSuccess = false;
}

DataProcessorFactory abstract methods that each data process should implement.

It means 1 data processor = 1 object type.


/// <summary>
/// This method is intended for converting a source object to destination.
/// This should be implemented by derived class since converting may differ per object.
/// In this method, AutoMapper is called for conversion and the converted object
/// is added to the list/collection that will be processed when Execute method is called.
/// </summary>
/// <typeparam name="TSource">Type of object to be converted and added</typeparam>
/// <param name="item"></param>
/// <param name="refNo">Reference Number of parent object in case of hierarchical processing</param>
/// <returns>Returns true if successful</returns>
public abstract bool ConvertAndAdd<TSource>(TSource item, string refNo);

/// <summary>
/// Perform processing on objects that is/are on the collection
/// This is where data persisting occurs.
/// </summary>
/// <returns></returns>
public abstract bool Execute();

/// <summary>
/// This is where the AutoMapper configuration is set.
/// Since AutoMapper can only map using the same property name; and the identity of
/// the object is always in the GUID element I have to map the ID of the Destination to GUID of source object
/// </summary>
public virtual void SetMapper()
{
}

To commit the data you just need to call the Execute method of every data processor instance.

BizTalk upgrade issue – Missing Assembly.cs file

December 8, 2011 Leave a comment

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.

SSIS – Centralize Connection Repository

December 2, 2011 1 comment

Maintaining a central repository for all connection information for SSIS packages is important especially if you have multiple packages that connects to one or more different systems. Keeping track and performing changes on the connections will be hard if not done properly.

This blog entry will describe on how to use SQL Configuration Type to store the connection info in combination with the traditional File ConfigurationType (dtsConfig) to store package specific configurations.

1. Setting up the configuration store

A table with definition below needs to be created on an new/existing database, this will store the connection information.

SSIS Configurations
Column Name Type Description
ConfigurationFilter

Nvarchar(255)

This field is used by SSIS to indentify a set of property/values pairs that are part of the same configuration entry in the Package Configurations Organizer.
ConfiguredValue

Nvarchar(255)

It stores the value that is used to update the package property specified in PackagePath column
PackagePath

Nvarchar(255)

The path that point to the property being configured
ConfiguredValueType

Nvarchar(255)

the SSIS data type of the property being configured

2. Environment Variable

In this step, we would create an environment variable which will store the connectionString information to the configuration store (see step1).

1. On the Start menu, click Control Panel.

2. In Control Panel, double-click System.

3. In the System Properties dialog box, click the Advanced tab, and then click Environment Variables.

Figure 1 – System Properties

4. In the Environment Variables dialog box, in System variables frame, click New.

In the New System Variables dialog box, type SSISConfiguration in the Variable name box, and Data Source={DatabaseServer};Initial Catalog={DatabaseName};Provider=SQLNCLI10.1;Integrated Security=SSPI;Auto Translate=False; in the Variable value box.

5. Click OK.

6. Click OK to exit the System Properties dialog box.

3. SSIS Package Settings

It is assumed that the package is already in the final phase of development and the only thing needs to be done is to update it to retrieve settings from the configuration store and use it during runtime.

 3.1 Add Configuration Store connection

Since we are going to retrieve connection settings from configuration store that is a table we need to define a new OLE DB connection that does that.

1. In the Connection Managers, right click New OLE DB Connection.

2. Click New, Set the valid credentials to connect then on the Connect to a database server(this is the connection to cofiguration store) , Select <{Database}>.

3. In the Connection Managers rename the added connection to OLEDB_SSISConfiguration.

 3.2 Configure Configuration Store Connection

In the following steps we are going update the connection to configuration store to use the environment variable we defined step (3.1)

1. Click SSIS Menu, Package Configurations. Package Configurations Organizer opens.

2. Click Enable package configurations.

3. Click Add, Click Next.

4. In the Configuration Type, Select Environment variable.

In the Environment variable drop-down, Select SSISConfiguration.

Click Next.

Note:
If the SSISConfiguration is not on the list try re-opening the BIDS (Business Intelligence Development Studio)

5. In the Select Property Page in the Connection Managers,

Click OLEDB_SSISConfiguration,

Click Properties, Select ConnectionString.

Click Next.

6. Type OLEDB_SSISConfiguration in the Configuration name and click Finish.

7. It should look like this:

3.3 Configure existing connection to use configuration store.

 After the connection to configuration store is properly setup the next step is to update existing connections in the package to retrieve settings from it.

Important:
Repeat the steps per connectionin the Package. If the package has connection to 2 database, the steps below needs to be repeated 2x.Example below illustrates an SSIS package that has connection to a database: SampleDB.

1. Click SSIS Menu, Package Configurations.

Package Configurations Organizer opens.

2. Click Add, Click Next.

3. In the configuration Type, Select SQL Server.

In the Connection, Select OLEDB_SSISConfiguration

In the Configuration Table, Select [dbo].[SSISConfigurations]

In the Configuration Filter, Set it to the <name of the connection>,

Ex. OLEDB_SampleDB.

Click Next.

4. In the Connection Managers, Select the connection (name of collection in step 3), Properties, Select ConnectionString.

Click Next.

Important:
Select only one ConnectionString in this step since the entire step will be repeated per connection. This makes sure that the settings defined in configuration store can be reused in other packages.

5. In the Configuration Name, type the name of connection (in step 3). Click Finished.

6. Repeat steps for other connections (if applicable).

7. Update the SSISConfiguration Table if SQL Login is used since SSIS doesn’t save sensitive information such as password.

Note:
Set the connection manager to DelayValidation=TRUE if during the execution the validation fails. For some reason, it uses the value in the package and not the values from the SQL Configuration store (Database).
If you encountered this error:“Cannot insert configuration information into the configuration table. (Microsoft.DataTransformationServices.Wizards)   Could not complete wizard actions.”

It means that one of the properties exceed the length limit of the SSIS configuration table, normally it’s the connection string that causes the problem. Try to shortened the connection string by truncating the Application Name property or try to giving it a short name.

%d bloggers like this: