Archive

Archive for the ‘.NET Development’ Category

Pure C# ETL (Extract Transform Load)

December 15, 2011 2 comments

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.

Event Log – System.Security.SecurityException: The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security

November 10, 2011 2 comments

Event Log – System.Security.SecurityException: The source was not found, but some or all event logs could not be searched. Inaccessible logs: Security

This is a common error specially if you’re using Windows Server 2008 and higher, error means that the application/program that’s trying to write to event log with source not registered properly. Take the sample code below as an example:

EventLog.WriteEntry(“<Source>”,”Message”);

During the deployment you can register the source by entering the ff command in the command prompt (Run as Administrator):

eventcreate /ID 1 /L APPLICATION /T INFORMATION /SO <Source> /D “Registering”

If for some  reason you don’t know the source is and it’s throwing the error do the following:

Start -> Run -> regedit.exe Navigate to My Computer > HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\EventLog

in the Permissions, and grant the identity wherein the application/server is running with read/write permissions.

You will see the source that you need to register in the Event Log.

VS Setup – The version of the .NET Framework launch condition ‘.NET Framework 4’ does not match the selected .NET Framework bootstrapper package

November 2, 2011 Leave a comment

I recently upgraded existing solution built using VS2008 to VS2010 and encountered the warning:

The version of the .NET Framework launch condition ‘.NET Framework 4’ does not match the selected .NET Framework bootstrapper package

To resolve it:

1. The .NET Framework version you have in Launch Condition should match with the 2. Prerequisites in Setup Properties.

 

Launch Condition:

Prerequisites:

SSIS How to copy files from one server to another

November 1, 2011 Leave a comment

The common question is how to copy/move/delete files across shared folder using SSIS.
Quick answer is to use the File System Task for the actual file operations, but how about the permissions?
By default it assumes that the account the you’ll use to execute the package (SSIS) either the SQL Server Agent identity (using sql job) or account that executes DTExec have appropriate permissions on the source/destination server.

To get the identity of SQL Server Agent:

1. In the command prompt, type services.msc, this would open the list of all services on the server.

2.  In the list of services, look for SQL Server Agent, Right Click -> Properties. Go to Logon Tab

Using Proxy. If it’s other identity than the Agent:

1. In the Security -> Credentials -> Create new credentials (username and password)

2. then go to SQL Agent -> Proxies -> SSIS Package Execution -> Create a new Proxy and assign the credentials. Then on the sql job itself assign the Run as = proxy.

Using NET USE command, If you need to connect to multiple resources (shared folder) or doesn’t use SQL Server Agent.

Issue the NET USE command before the File System Task. Example syntax:

net use RPAULO\\SharedFolder /user:RPAULO\SSISUser p@assword

for NET USE command refrence, click here.


SSRS – Calling Reporting Services using WCF and using the HTML output for email notification

October 27, 2011 6 comments

Recently we had a project that requires us to use Reporting Server for email notification. In short,  the HTML output of the Report is assigned to the body of the email notification.

1) First step is to create a proxy library utility containing reference to Reporting Server Web Service. To do this Click Add Service Reference and set url to:

 http://{ReportServerName}/ReportServer/ReportExecution2005.asmx, In the collection type set it to System.Collections.Generic.List

Note: Version of SSRS is 2005

Update!: Problem with Authorization:

“The HTTP request is unauthorized with client authentication scheme ‘Negotiate’. The authentication header received from the server was ‘NTLM’.”

To fix problem with Authorization issue in LoadReport method:

1. Added new method called GetDefaultBinding.

2.Also a new parameter in Export method named  useDefaultBinding with value set to false was added. If this value is set to true, it would use the binding from GetDefaultBinding and will use DefaultNetworkCredentials.

Note: Network credential is not being used when useDefaultBinding is set to true.

Updated code is as follows:

  /// <summary>
    /// Utility class that renders and exports a SQL Reporting Services report into the specified output format.
    /// </summary>
    public static class ReportExporter
    {
        private static string GetExportFormat(ExportFormat f)
        {
            switch (f)
            {
                case ExportFormat.XML: return "XML";
                case ExportFormat.CSV: return "CSV";
                case ExportFormat.Image: return "IMAGE";
                case ExportFormat.PDF: return "PDF";
                case ExportFormat.MHTML: return "MHTML";
                case ExportFormat.HTML4: return "HTML4.0";
                case ExportFormat.HTML32: return "HTML3.2";
                case ExportFormat.Excel: return "EXCEL";
                case ExportFormat.Word: return "WORD";

                default:
                    return "PDF";
            }
        }

        /// <summary>
        /// Exports a Reporting Service Report to the specified format using Windows Communication Foundation (WCF) endpoint configuration specified.
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="format"></param>
        /// <param name="output">rendering output result in bytes</param>
        /// <param name="extension">output format file extension</param>
        /// <param name="mimeType">output MIME type</param>
        /// <param name="encoding">output encoding</param>
        /// <param name="warnings">warnings (if any)</param>
        /// <param name="streamIds">stream identifiers for external resources (images, etc) that are associated with a given report</param>
        /// <param name="dataSourceCredentials"></param>
        public static void Export(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters,
            ExportFormat format, out byte[] output, out string extension, out string mimeType, out string encoding, out Warning[] warnings, out string[] streamIds, DataSourceCredentials[] dataSourceCredentials=null, bool UseDefaultBinding=false)
        {

            Binding binding;
            if (UseDefaultBinding)
            {
                binding = GetDefaultBinding();
            }
            else
            {
                binding = GetBinding();
            }

            using (var webServiceProxy = new ReportExecutionServiceSoapClient(binding, new EndpointAddress(url)))
            {
                webServiceProxy.ClientCredentials.Windows.AllowedImpersonationLevel = System.Security.Principal.TokenImpersonationLevel.Impersonation;

                if (UseDefaultBinding)
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
                }
                else
                {
                    webServiceProxy.ClientCredentials.Windows.ClientCredential = clientCredentials;
                }

                // Init Report to execute
                ServerInfoHeader serverInfoHeader;
                ExecutionInfo executionInfo;

                try
                {

                    ExecutionHeader executionHeader = webServiceProxy.LoadReport(null, report, null, out serverInfoHeader, out executionInfo);

                    // Attach Report Parameters
                    webServiceProxy.SetExecutionParameters(executionHeader, null, parameters, null, out executionInfo);

                    //Set Credentials
                    if(dataSourceCredentials!=null)
                        webServiceProxy.SetExecutionCredentials(executionHeader, null, dataSourceCredentials, out executionInfo);

                    // Render
                    webServiceProxy.Render(executionHeader, null, GetExportFormat(format), null, out output, out extension, out mimeType, out encoding, out warnings, out streamIds);
                }
                catch (FaultException faultExc)
                {
                    throw new Exception(string.Format("Encountered error while generating the report: '{0}', Report Url: '{1}', Web Service Url: '{2}' Message: '{3}'",  reportName, report, url, faultExc.Message ), faultExc);
                }
            }
        }

        /// <summary>
        /// Export a Reporting Services to HTML
        /// </summary>
        /// <param name="url">Address of Report Web Service</param>
        /// <param name="clientCredentials">Network Credential to use to connect to the web service</param>
        /// <param name="reportName">Friendly name of Reporting Services Report to execute</param>
        /// <param name="report">Reporting Services Report to execute</param>
        /// <param name="parameters">report parameters</param>
        /// <param name="dataSourceCredentials"></param>
        /// <returns></returns>
        public static string ExportToHTML(string url, System.Net.NetworkCredential clientCredentials, string reportName, string report, ParameterValue[] parameters, DataSourceCredentials[] dataSourceCredentials = null, bool UseDefaultBinding = false)
        {
            Warning[] warn;
            string[] p;

            byte[] output;
            string extension, mimeType, encoding;

            //Get HTML
            ReportExporter.Export(url,
                                  clientCredentials,
                                  reportName,
                                  report,
                                  parameters.ToArray(),
                                  ExportFormat.HTML4,
                                  out output,
                                    out extension,
                                    out mimeType,
                                    out encoding, out warn, out p, dataSourceCredentials, UseDefaultBinding
                                    );

            System.Text.Encoding enc = System.Text.Encoding.GetEncoding("UTF-8");
            string html = enc.GetString(output);
            return html;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
              {
                  MaxArrayLength = 2147483647,
                  MaxBytesPerRead = 2147483647,
                  MaxDepth = 2147483647,
                  MaxNameTableCharCount = 2147483647,
                  MaxStringContentLength = 2147483647
              };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Windows;
            return binding;
        }

        /// <summary>
        /// Returns the binding to use, eliminates the app.config
        /// </summary>
        /// <returns></returns>
        internal static Binding GetDefaultBinding()
        {
            BasicHttpBinding binding = new BasicHttpBinding();
            binding.MaxBufferPoolSize = 0;
            binding.MaxReceivedMessageSize = 5242880;
            binding.HostNameComparisonMode = HostNameComparisonMode.StrongWildcard;
            binding.TextEncoding = System.Text.Encoding.UTF8;
            binding.MessageEncoding = WSMessageEncoding.Text;
            binding.TransferMode = TransferMode.Buffered;
            binding.UseDefaultWebProxy = true;
            binding.ReaderQuotas = new System.Xml.XmlDictionaryReaderQuotas
            {
                MaxArrayLength = 2147483647,
                MaxBytesPerRead = 2147483647,
                MaxDepth = 2147483647,
                MaxNameTableCharCount = 2147483647,
                MaxStringContentLength = 2147483647
            };
            binding.Security.Mode = BasicHttpSecurityMode.TransportCredentialOnly;
            binding.Security.Transport.ClientCredentialType = HttpClientCredentialType.Ntlm;
            return binding;
        }
        #endregion
    }

Updated Unit Test to use default bindings:

[TestMethod]
        public void GetHTMLReportTest()
        {

            //Set network credentials
            string userName = "NotUsed";
            string password = "NotUsed";
            string domain = "NotUsed";
            System.Net.NetworkCredential credentials = new System.Net.NetworkCredential(userName, password, domain);
            string outputPath = @"C:\TestFolder";

            //Url
            string path = "/MyReport/Invoice";

            //Parameters
            List<ParameterValue> parameters = new List<ParameterValue>();
            parameters.Add(new ParameterValue { Name = "AcctId", Value = "0001" });

            //Get HTML
            string html = ReportExporter.ExportToHTML("http://HypV-Rpaulo/ReportServer/ReportExecution2005.asmx",
                                  credentials,
                                  "My invoice report",
                                  path ,
                                  parameters.ToArray(),UseDefaultBinding:true
                                  );

            Assert.AreEqual(true, html.Length > 0, "No HTML generated");
            Console.WriteLine(html);
            System.IO.File.WriteAllText(outputPath + "result.html", html);

        }

The library can be used in BizTalk (deployed in GAC) , SSIS 2005 or 2008(deployed in GAC, .NET version is <=3.5 and is copied to %Windir%\Microsoft.NET\Framework\v2.0.50727 folder to be able to referenced in a SSIS Package..

Entity Framework – The version of SQL Server in use does not support datatype ‘datetime2’

October 26, 2011 Leave a comment

I came across this error when I’ve generated the EDMX model (Entity Framework) using a database source that is SQL Server 2008R2 and ran it in different environment that has different SQL Version (2005).

To fix this issue:

Manually open the EDMX file using XML Editor or notepad, replace the value of ProviderManifestToken from 2008 to 2005.

ASP.NET ReportViewer Native compiler return value: ‘[BC2001] file’ error

July 28, 2011 6 comments

Recently restored one of our servers and I encountered this error while trying to view a report:

An unexpected error occurred while compiling expressions. Native compiler return value: ‘[BC2001] file …. 

Solution:

1. Solving this seems so easy, first check the identity of the AppPool where the website is running:

2. From the command prompt type: %windir% and look for the temp folder. Add read/write permission to temp folder of windows to user in step 1.

Note: In other cases  the temp folder is located somewhere else, so to check where it’s located go to System Properties -> Advance -> Environment Variables. In the System Variables look  for the TEMP or TMP:

 

 

 

%d bloggers like this: