Software Architecture – Backup all database of SQL Server via Job/schedule

August 2, 2010

Problem Definition:
In development environment, often it is required to take backup of all databases on weekly basis. During whole week, development is going on by different teams on different databases, so it is very vital to have a backup mechanism for all the database.
So objective is to take backup of all databases in a SQL Server Instance on first day of week during lunch time that is 2:00 pm

Solution:
In SQL Server, Jobs and schedules are used to run any action with specific time plan.
SQL Server agent must be running to run the jobs.

First step is to create a job, by doing a right click on job node in object explorer of SQL Server.





Click on steps to define a action to be performed by putting following T-SQL in command text

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'F:\DBWeeklyBackup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb')  
	
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Step window will look like as:


Click on schedule to define the time plan for job to be run as.

Define the notification for job as:


Test Job by clicking on “Start Job At Step”

It will create backup of all databases in SQL Server at F:\DBWeeklyBackup\ with date appended at the end.


Software Architecture – Design Problem Part 2 – DTO- MS Visual Studio 2010 – Net 4-0 – WCF – NHibernate – Silverlight

July 31, 2010

This is second post with reference to Design Architecture discussion on Project based on NHibernate + WCF+Sliverlight.
See the first Post last post here.

In my last post I had identified that we need DTO Objects.
DTO-Data Transfer Objects are customized object that can have different object structure then Objects used in OR Mapping/Nhibernate. When a request for object comes from client (Silverlight project) to server (ASP .Net project) having Service via WCF wire, at Server side, query is run on OR Mapping Object, utiizing full benefits of Lazy loading, business rules are applied, and finally data is ready to be send to client. At this point data is not in customized according to client needs and filtering of properties for objects is required to reduce the size of data being transferred. That where DTO objects comes in action.

Benefits of DTO objects:

1. It is always better from software design perspective to not expose object of data layer direct to client end that is silverlight via WCF.

2. Most important benefit is: filtering of object properties or Change in structure of objects that needs to be transferred across WCF wire. Let suppose you have invoice creation form having fields to be filled as like client, consignee, agent, shipper, sales person, operation manager and many other. So when loading invoice form data from all these entities like agents ……. will travel on WCF wire. Each entity may have a lot of properties, but on client side we only need id and name of all these entities. So we can make various versions of entities according to need for architecture.

3. With DTO approach, UI (silverlight) is only dependent on DTO objects, so the data layer can be changed to any tech like linq2sql,linq2entities or any other thing. Change in Data layer is very flexible with DTO.

Elements of Design Example:
You can download sample here.
Rename the file as sample.zip and then extract.

Solution Explorer will look like as:



This Example will fetch 2 different DTO objects with sample look and feel as below.


There is one entity class for nhibernate named as “Customer” with HBM set in Mapping.hbm.xml.

There are 2 DTO Classes. First DTO with properties as ClientID,ClientCode, Name.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace WCFNHibernateSilverLightSample.Web.DTOEntities
{
    [DataContract(IsReference = true)] 
    public class DTOCustomer
    {
        #region Fields
        private int _ClientID;
        private string _ClientCode;
        private string _Name;
        #endregion
        #region Constructors
        /// <summary>
        /// Initializes a new instance of the GeneralClientMaster class 
        /// </summary> 
        public DTOCustomer()
        {
        }     
        #endregion

        #region Properties
        /// <summary>
        /// Gets or sets the ClientID for the current GeneralClientMaster
        /// </summary>
        [DataMember]
        public virtual int ClientID
        {
            get { return _ClientID; }
            set { _ClientID = value; }
        }

        /// <summary>
        /// Gets or sets the ClientCode for the current GeneralClientMaster
        /// </summary>
        [DataMember]
        public virtual string ClientCode
        {
            get { return _ClientCode; }
            set { _ClientCode = value; }
        }
        [DataMember]
        public virtual string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }

        #endregion

    }

    
}

Second DTO class has extra parameter of Address as:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Runtime.Serialization;

namespace WCFNHibernateSilverLightSample.Web.DTOEntities
{
    [DataContract(IsReference = true)]
    public class DTOCustomerVersionWithAddress
    {
        #region Fields
        private int _ClientID;
        private string _ClientCode;
        private string _Name;
        private string _Address;
        #endregion
        #region Constructors
        /// <summary>
        /// Initializes a new instance of the GeneralClientMaster class 
        /// </summary> 
        public DTOCustomerVersionWithAddress()
        {
        }
        #endregion

        #region Properties
        /// <summary>
        /// Gets or sets the ClientID for the current GeneralClientMaster
        /// </summary>
        [DataMember]
        public virtual int ClientID
        {
            get { return _ClientID; }
            set { _ClientID = value; }
        }

        /// <summary>
        /// Gets or sets the ClientCode for the current GeneralClientMaster
        /// </summary>
        [DataMember]
        public virtual string ClientCode
        {
            get { return _ClientCode; }
            set { _ClientCode = value; }
        }
        [DataMember]
        public virtual string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
        [DataMember]
        public virtual string Address
        {
            get { return _Address; }
            set { _Address = value; }
        }

        #endregion

    }
 
}

A converter class will be required as below to transform “customer” to “DTOCustomer” and “DTOCustomerVersionWithAddress”.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WCFNHibernateSilverLightSample.Web.DTOEntities.DTOConvertor
{
    public class DTOConvertor
    {
        #region CustomerConvertor

        public static DTOCustomer ConvertFromCustomerToDTOCustomer(Customer cust)
        {
            DTOCustomer dtocus = new DTOCustomer();
            dtocus.ClientID =  cust.ClientID;
            dtocus.ClientCode =  cust.ClientCode;
            dtocus.Name =  cust.Name;            
            return dtocus;            
        }
        public static DTOCustomerVersionWithAddress ConvertFromCustomerToDTOCustomerVersionWithAddress(Customer cust)
        {
            DTOCustomerVersionWithAddress dtocus = new DTOCustomerVersionWithAddress();
            dtocus.ClientID = cust.ClientID;
            dtocus.ClientCode = cust.ClientCode;
            dtocus.Name = cust.Name;
            // Note here Transformation of properties from Nhibernate Object to DTO Objects
            // It is really a simple example. Very advance level tranformation can be done.
            dtocus.Address = cust.PostalAddress + " Tel: " + cust.Phone1 + " Fax: " + cust.Fax1 + " Email : " + cust.Email;
            return dtocus;
        }
        #endregion CustomerConvertor
    }
}

The WCF Service code will look like as

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Channels;
using NHibernate;
using WCFNHibernateSilverLightSample.Web.DTOEntities;
using WCFNHibernateSilverLightSample.Web.DTOEntities.DTOConvertor;

namespace WCFNHibernateSilverLightSample.Web
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "GetDataService" in code, svc and config file together.
    public class GetDataService : IGetDataService
    {
        public IList<DTOCustomer> GetCustomer()
        {
            IList<Customer> list;
            IList<DTOCustomer> listDTO =  new List<DTOCustomer>();
            NHibernate.ISession session = NHibernateHelper.OpenSession();
            ICriteria query = session.CreateCriteria(typeof(Customer));
            list = query.List<Customer>();

            foreach(Customer c in list)
            {
                listDTO.Add(DTOConvertor.ConvertFromCustomerToDTOCustomer(c));
            }
            return listDTO;
        }
        public IList<DTOCustomerVersionWithAddress> GetCustomerWithAddress()
        {
            IList<Customer> list;
            IList<DTOCustomerVersionWithAddress> listDTO = new List<DTOCustomerVersionWithAddress>();
            NHibernate.ISession session = NHibernateHelper.OpenSession();
            ICriteria query = session.CreateCriteria(typeof(Customer));
            list = query.List<Customer>();

            foreach (Customer c in list)
            {
                listDTO.Add(DTOConvertor.ConvertFromCustomerToDTOCustomerVersionWithAddress(c));
            }
            return listDTO;
        }
    }
}

You can download sample here.
Rename the file as sample.zip and then extract.


Software Architecture – Microsoft SQL Server Express Edition with Advance Features -Licensing Query

July 29, 2010

While Exploring Licensing option for PCMS ERP Pegasus Cargo Management System, i had passed a query to Microsoft-Pakistan.

My Query to Microsoft Licensing Team – Islamabad – Pakistan

We are evaluating to use Microsoft SQL Server Express edition with advance features for our product.

With reference to your link below,
http://msdn.microsoft.com/en-us/library/cc645993.aspx
We have some questions as:

1. Is there any limitation of number of clients connecting to SQL Server express edition with advance features?

2. Secondly, table with title as “Scalability and Performance” in the above link suggests that there is a limitation of “Maximum memory utilized” as “1 GB” for SQL Server Express edition with advance features.. What is the meaning of this limitation, can you please explain it in more detail? Does this memory refer to the size of memory that can be used in system where SQL server will be installed or it is the memory being used by SQL server process in task manager of OS.


Is there any limitation of number of clients connecting to SQL Server express edition with advance features?

Microsoft Answer:- Yes, generally SQL Server allows 32,767 concurrent / simultaneous connections, however for your instance you can check by running the @@MAX_CONNECTIONS function. This function would return you the maximum number of connections on that particular instance, however it can be different from the currently configured maximum number of connections on that particular instance so this is configurable (sp_configure) but staying within the maximum connection limit.

Secondly, table with title as “Scalability and Performance” in the above link suggests that there is a limitation of “Maximum memory utilized” as “1 GB” for SQL Server Express edition with advance features.. What is the meaning of this limitation, can you please explain it in more detail? Does this memory refer to the size of memory that can be used in system where SQL server will be installed or it is the memory being used by SQL server process in task manager of OS.

Microsoft Answer:- This is the maximum memory which SQL Server express can use, regardless of the system memory.


Software Architecture – Design Problem – MS Visual Studio 2010 + .Net 4.0 + WCF + NHibernate + Silverlight

July 28, 2010

Environment: MS Visual Studio 2010 + .Net 4.0 + WCF + NHibernate + Silverlight

This post assumes that you have initial knowledge of WCF, NHibernate, Silverlight.

See the sample code for this solution working here.
Rename file as up.zip for extracting.

Sample Project:

Entities Relation / Tables relation is like:

Region >> Country >> City >> Area

So each region has many countries, Each country has many cities. Each City has many regions.
A typical Silverlight solution is created with 2 projects (One for silverlight(Client side project), other for ASP .Net Web project (server side ) having a WCF Service for data transfer from server side to client side.

Problem Definition:

Lets suppose you want to get list of City with lazy=”false” and fetch=”join”
for all entities So we will get following queries in SQL Profiler:

SELECT this_.CityID as CityID3_2_, this_.CountryID as CountryID3_2_, this_.CityCode as CityCode3_2_, this_.Description as Descript4_3_2_, this_.Active as Active3_2_, country2_.CountryID as CountryID1_0_, country2_.CountryCode as CountryC2_1_0_, country2_.Description as Descript3_1_0_, country2_.Active as Active1_0_, country2_.RegionId as RegionId1_0_, regions3_.RegionID as RegionID0_1_, regions3_.RegionCode as RegionCode0_1_, regions3_.Description as Descript3_0_1_, regions3_.Active as Active0_1_ FROM GeneralCityMaster this_ left outer join GeneralCountryMaster country2_ on this_.CountryID=country2_.CountryID left outer join GeneralRegionMaster regions3_ on country2_.RegionId=regions3_.RegionID

Again get list of City with lazy=”false” and fetch=”select”
for all entities So we will get following queries in SQL Profiler:

SELECT this_.CityID as CityID3_0_, this_.CountryID as CountryID3_0_, this_.CityCode as CityCode3_0_, this_.Description as Descript4_3_0_, this_.Active as Active3_0_ FROM GeneralCityMaster this_

For each country in list, there will be an select from country table as:

exec sp_executesql N'SELECT country0_.CountryID as CountryID1_0_, country0_.CountryCode as CountryC2_1_0_, country0_.Description as Descript3_1_0_, country0_.Active as Active1_0_, country0_.RegionId as RegionId1_0_ FROM GeneralCountryMaster country0_ WHERE country0_.CountryID=@p0',N'@p0 int',@p0=1

For each region in list, there will be an select from region table as:

exec sp_executesql N'SELECT regions0_.RegionID as RegionID0_0_, regions0_.RegionCode as RegionCode0_0_, regions0_.Description as Descript3_0_0_, regions0_.Active as Active0_0_ FROM GeneralRegionMaster regions0_ WHERE regions0_.RegionID=@p0',N'@p0 int',@p0=21

Two Problems:
There are 2 problems with above implementation.

Lazy load is false, so on server side that is ASP .Net / WCF Project, if we need some processing to be done, it is loading all data, no lazy initialization helps us.

Secondly, in both above cases data is joined from all the corresponding tables even if it is not required. Suppose we want to get data of cities only but unfortunately data of country and region is also fetched and transfer of such a heavy data across the WCF wire is very heavy activity.

If you will change lazy=”true” for entities, then WCF will pass following error.

{System.Net.WebException: The remote server returned an error: NotFound.
   at System.Net.Browser.BrowserHttpWebRequest.InternalEndGetResponse(IAsyncResult asyncResult)
   at System.Net.Browser.BrowserHttpWebRequest.<>c__DisplayClass5.<EndGetResponse>b__4(Object sendState)
   at System.Net.Browser.AsyncHelper.<>c__DisplayClass2.<BeginOnUI>b__0(Object sendState)}

In order to see the real problem, configure WCF Log as explained
as explained at here

On looking into



Exeption will look like as:

There was an error while trying to serialize parameter http://tempuri.org/:GetCitiesResult. The InnerException message was 'Type 'CountryProxy' with data contract name 'CountryProxy:http://schemas.datacontract.org/2004/07/' is not expected. Consider using a DataContractResolver or add any types not known statically to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer.'.  Please see InnerException for more details.

Stack trace will look like as:

System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.SerializeParameterPart(XmlDictionaryWriter writer, PartInfo part, Object graph)
System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.SerializeParameter(XmlDictionaryWriter writer, PartInfo part, Object graph)
System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.SerializeBody(XmlDictionaryWriter writer, MessageVersion version, String action, MessageDescription messageDescription, Object returnValue, Object[] parameters, Boolean isRequest)
System.ServiceModel.Dispatcher.OperationFormatter.SerializeBodyContents(XmlDictionaryWriter writer, MessageVersion version, Object[] parameters, Object returnValue, Boolean isRequest)
System.ServiceModel.Dispatcher.OperationFormatter.OperationFormatterMessage.OperationFormatterBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
System.ServiceModel.Channels.BodyWriterMessage.OnBodyToString(XmlDictionaryWriter writer)
System.ServiceModel.Channels.Message.ToString(XmlDictionaryWriter writer)
System.ServiceModel.Diagnostics.MessageLogTraceRecord.WriteTo(XmlWriter writer)
System.ServiceModel.Diagnostics.MessageLogger.LogInternal(MessageLogTraceRecord record)
System.ServiceModel.Diagnostics.MessageLogger.LogMessageImpl(Message&amp; message, XmlReader reader, MessageLoggingSource source)
System.ServiceModel.Diagnostics.MessageLogger.LogMessage(Message&amp; message, XmlReader reader, MessageLoggingSource source)
System.ServiceModel.Dispatcher.DispatchOperationRuntime.SerializeOutputs(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
System.ServiceModel.Dispatcher.ChannelHandler.DispatchAndReleasePump(RequestContext request, Boolean cleanThread, OperationContext currentOperationContext)
System.ServiceModel.Dispatcher.ChannelHandler.HandleRequest(RequestContext request, OperationContext currentOperationContext)
System.ServiceModel.Dispatcher.ChannelHandler.AsyncMessagePump(IAsyncResult result)
System.ServiceModel.Dispatcher.ChannelHandler.OnAsyncReceiveComplete(IAsyncResult result)
System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
System.Runtime.InputQueue`1.AsyncQueueReader.Set(Item item)
System.Runtime.InputQueue`1.EnqueueAndDispatch(Item item, Boolean canDispatchOnThisThread)
System.Runtime.InputQueue`1.EnqueueAndDispatch(T item, Action dequeuedCallback, Boolean canDispatchOnThisThread)
System.ServiceModel.Channels.SingletonChannelAcceptor`3.Enqueue(QueueItemType item, Action dequeuedCallback, Boolean canDispatchOnThisThread)
System.ServiceModel.Channels.HttpChannelListener.HttpContextReceived(HttpRequestContext context, Action callback)
System.ServiceModel.Activation.HostedHttpTransportManager.HttpContextReceived(HostedHttpRequestAsyncResult result)
System.ServiceModel.Activation.HostedHttpRequestAsyncResult.HandleRequest()
System.ServiceModel.Activation.HostedHttpRequestAsyncResult.BeginRequest()
System.ServiceModel.Activation.HostedHttpRequestAsyncResult.OnBeginRequest(Object state)
System.Runtime.IOThreadScheduler.ScheduledOverlapped.IOCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
System.Runtime.Fx.IOCompletionThunk.UnhandledExceptionFrame(UInt32 error, UInt32 bytesRead, NativeOverlapped* nativeOverlapped)
System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)

This exception reflects that when WCF serializer tries to load countries, it fails because of lazy loading being true as country will be a proxy object, so it will pass the exception.

Solution:
After reviewing the suggestions provided by Sowmy Srinivasan’s Blog at:
http://blogs.msdn.com/b/sowmy/archive/2006/03/26/561188.aspx

and discussion on Johan Danforth’s Blog at:
http://weblogs.asp.net/jdanforth/archive/2008/12/22/nhibernate-and-wcf-is-not-a-perfect-match.aspx

I have introduced 3 new classes as:

1. HibernateDataContractSurrogate which implements IDataContractSurrogate which replaces proxy object with forcefully created object by the DataContractSerializer of WCF during serialization.

MSDN Reference:
http://msdn.microsoft.com/en-us/library/ms574969.aspx

2. ReferencePreservingDataContractSerializerOperationBehavior
Which is derived from DataContractSerializerOperationBehavior will extend the functionality provided by DataContract behavior.

3. ReferencePreservingDataContractFormatAttribute which provide attribute that will apply the behavior created above with name
ReferencePreservingDataContractSerializerOperationBehavior

So in short we have modified the behavior of data contract by using IDataContractSurrogate to replace proxy object with forcefully created object.

After this above change of introducing 3 classes, old exception of countryproxy will disappear, but a new exception will be there as below.

There was an error while trying to serialize parameter http://tempuri.org/:GetCitiesResult. The InnerException message was 'Type 'WCFNHibernateSilverLightSample.Web.City' with data contract name 'City:http://schemas.datacontract.org/2004/07/WCFNHibernateSilverLightSample.Web' is not expected. Consider using a DataContractResolver or add any types not known statically to the list of known types - for example, by using the KnownTypeAttribute attribute or by adding them to the list of known types passed to DataContractSerializer.'.  Please see InnerException for more details.

Trace as:

System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.SerializeParameterPart(XmlDictionaryWriter writer, PartInfo part, Object graph)
System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.SerializeParameter(XmlDictionaryWriter writer, PartInfo part, Object graph)
System.ServiceModel.Dispatcher.DataContractSerializerOperationFormatter.SerializeBody(XmlDictionaryWriter writer, MessageVersion version, String action, MessageDescription messageDescription, Object returnValue, Object[] parameters, Boolean isRequest)
System.ServiceModel.Dispatcher.OperationFormatter.SerializeBodyContents(XmlDictionaryWriter writer, MessageVersion version, Object[] parameters, Object returnValue, Boolean isRequest)
System.ServiceModel.Dispatcher.OperationFormatter.OperationFormatterMessage.OperationFormatterBodyWriter.OnWriteBodyContents(XmlDictionaryWriter writer)
System.ServiceModel.Channels.BodyWriter.WriteBodyContents(XmlDictionaryWriter writer)
System.ServiceModel.Channels.BodyWriterMessage.OnBodyToString(XmlDictionaryWriter writer)
System.ServiceModel.Channels.Message.ToString(XmlDictionaryWriter writer)
System.ServiceModel.Diagnostics.MessageLogTraceRecord.WriteTo(XmlWriter writer)
System.ServiceModel.Diagnostics.MessageLogger.LogInternal(MessageLogTraceRecord record)
System.ServiceModel.Diagnostics.MessageLogger.LogMessageImpl(Message&amp; message, XmlReader reader, MessageLoggingSource source)
System.ServiceModel.Diagnostics.MessageLogger.LogMessage(Message&amp; message, XmlReader reader, MessageLoggingSource source)
System.ServiceModel.Dispatcher.DispatchOperationRuntime.SerializeOutputs(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage41(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc&amp; rpc)
System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
System.ServiceModel.Dispatcher.ChannelHandler.DispatchAndReleasePump(RequestContext request, Boolean cleanThread, OperationContext currentOperationContext)
System.ServiceModel.Dispatcher.ChannelHandler.HandleRequest(RequestContext request, OperationContext currentOperationContext)
System.ServiceModel.Dispatcher.ChannelHandler.AsyncMessagePump(IAsyncResult result)
System.ServiceModel.Dispatcher.ChannelHandler.OnAsyncReceiveComplete(IAsyncResult result)
System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
System.Runtime.InputQueue`1.AsyncQueueReader.Set(Item item)
System.Runtime.InputQueue`1.EnqueueAndDispatch(Item item, Boolean canDispatchOnThisThread)
System.Runtime.InputQueue`1.EnqueueAndDispatch(T item, Action dequeuedCallback, Boolean canDispatchOnThisThread)
System.ServiceModel.Channels.SingletonChannelAcceptor`3.Enqueue(QueueItemType item, Action dequeuedCallback, Boolean canDispatchOnThisThread)
System.ServiceModel.Channels.HttpChannelListener.HttpContextReceived(HttpRequestContext context, Action callback)
System.ServiceModel.Activation.HostedHttpTransportManager.HttpContextReceived(HostedHttpRequestAsyncResult result)
System.ServiceModel.Activation.HostedHttpRequestAsyncResult.HandleRequest()
System.ServiceModel.Activation.HostedHttpRequestAsyncResult.BeginRequest()
System.ServiceModel.Activation.HostedHttpRequestAsyncResult.OnBeginRequest(Object state)
System.Runtime.IOThreadScheduler.ScheduledOverlapped.IOCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
System.Runtime.Fx.IOCompletionThunk.UnhandledExceptionFrame(UInt32 error, UInt32 bytesRead, NativeOverlapped* nativeOverlapped)
System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)

Solution for the above exception is provided at:
http://msdn.microsoft.com/en-us/library/system.servicemodel.serviceknowntypeattribute.aspx

Introduce ServiceKnownType attribute in service contract with a helper class,
So the Service will look like as:

// NOTE: You can use the "Rename" command on the "Refactor" menu to change the interface name "IGetDataService" in both code and config file together.
    [ServiceContract]
    [ServiceKnownType("GetKnownTypes", typeof(Helper))]
    public interface IGetDataService
    {

        [OperationContract]        
        [ReferencePreservingDataContractFormat]
        IList<City> GetCities();
    }



// This class has the method named GetKnownTypes that returns a generic IEnumerable.
    static class Helper
    {
        public static IEnumerable<Type> GetKnownTypes(ICustomAttributeProvider provider)
        {
            System.Collections.Generic.List<System.Type> knownTypes =
                new System.Collections.Generic.List<System.Type>();
            // Add any types to include here.
            knownTypes.Add(typeof(City));
            knownTypes.Add(typeof(Country));
            knownTypes.Add(typeof(Regions));
            knownTypes.Add(typeof(AreaMaster));
            
            return knownTypes;
        }
    }

So now the first problem has been solved. So now in business classes we can use objects with lazy loading working, only required object will be initialized. We have lazy loading true in OR Mapping, so on Server side “WCFNHibernateSilverLightSample.Web” project will access the object model with fully operational benefits of lazy loading.

See the sample code for this solution working here.

But second problem still exists. To solve the second problem, DTO (Data Transfer Objects) are used. DTO object are simple entity objects have the only those properties that needs to be transferred via WCF wire, So OR Mapping Nhibernate object will be different from DTO objects. Once the data is fetched in OR Mapping Nhibernate object, a transformation is done to DTO object and light weight DTO objects are passed with WCF to client end silverlight project.

I will do one more post to demonstrate this second problem in next post.

Both problems must be solved in a Good designed WCF/Sliverlight/Nhibernate project.

Happy Coding.


Software Architecture – To Analyze Licensing needs for PCMS- Pegasus Cargo Management System

July 19, 2010

Topic: To Analyze Licensing needs for PCMS- Pegasus Cargo Management System
By: Shahzad Sarwar
Dated: 19th July 2010

Pegasus Cargo Management System-PCMS:
Pegasus Cargo Management System is one of the FIRST software in the cargo industry, which is totally integrated with accounts. Designed by experts from cargo and software industry it provides one stop solution for data entry and decision support. The implementation of detailed function level checks ensures reliability & date integrity of system. The embedded security through appropriate Access Controls allows complete control of user access and data manipulation. PCMS provides detailed and extensive MIS reports helping the management to take informed decisions.

Conclusions:
• PCMS-Pegasus Cargo Management System needs .Net framework 3.5 runtime and SQL server on a Windows Machine.
• Microsoft .Net 3.5 runtime is shipped free on all latest windows version. If not available , it can be installed by downloading from Microsoft site for free.
• PCMS-Pegasus Cargo Management System is targeted for small to medium size organizations where database needs are not very high tech. Advance features of SQL Server are not required for application to run.
• With reference to section 2.1, most close versions for consideration should be SQL Server Express edition and SQL Server Workgroup Edition.
• SQL Server Express edition with advance features will be launching pad for PCMS ERP. As it is free and have all the features required to run PCMS.
• For Details about SQL Server Express edition with Advance features, see tables (2.2, 2.3).
• After starting along SQL Server Express edition with Advance features, down the roads, when database size exceeds 10 GB limit, we can propose clients to switch to SQL Server Workgroup edition.
• For detailed analysis of SQL Server Express edition with advance features, see section (3.1, 3.2, 3.3, 3.4, 3.5, 3.6) in Red Color.
• For detailed analysis of SQL Server Workgroup edition, see sections (3.1, 3.2, 3.3, 3.4, 3.5, 3.6) in Pink Color.
• Pricing for licenses shown in section 4.


Software Architecture – Case Study – Why and Why not SQL Server Replication

June 7, 2010

 Cases Study Related To Replication

                                                             By: Shahzad Sarwar

                                                            To: Dubai Office

                                                            Dated: 7th June 2010

Problem Definition:

Ships of shipping companies move around in sea, which have a system where check list (as Questions/answer) are supposed to be saved. These check list identify the health, safety and operational status of ship and its different components. On regular intervals, these checklists are filled in ships.

There will be one centralized server, where data of these check lists from different ships will be pushed. So central server will have consolidated view of data from all the ships moving in the Sea.

Solutions:

Lets first analyze what are the objective of replication, Why to use and Why to not use replication in eyes of MSDN-the most valid source of information.

1         Objectives of Replication:

Replication can be used effectively for many different purposes, as discussed in the following sections.

1.1      Separating Data Entry and Reporting

If you have worked in an environment in which the same database is used for data entry and reporting, you probably know that things aren’t always rosy. Constantly reading and modifying data in the same set of tables just doesn’t work very well if you care about data integrity. Transactions that run against a set of tables prevent reading the locked data rows and pages, or perhaps prevent even an entire table from being read by a report. In such an environment, you are bound to see blocking locks. Although there are ways to avoid blocking (please see my earlier article about transactions and locking), it is best to separate data entry and reporting databases. Transactional replication works well by delivering data changes from the data entry server to the reporting server.

1.2      Distributing Load Across Servers

As your organization grows, you might find yourself in a situation in which a single database server is utilized by too many users. If CPU utilization on your database servers is constantly over 80 percent and you have tuned database design and queries appropriately, chances are you could benefit by spreading the user base over multiple servers. For instance, a server named South could serve all employees working in the southern United States, and a server called North could serve all Northerners. If you need to combine all data for reporting, you could use replication to move transactions from North and South to a server named Central_Reporting.

1.3      Providing High Availability

Occasionally, you might consider using replication for high availability; that is, to replicate transactions from the main server to a standby server. If the main server fails, you can then point your data sources to the standby server. Be aware that using replication for high availability takes careful planning and testing. Replication does not provide any sort of automatic fail-over. SQL Server supports other methods of providing high availability, such as clustering and log-shipping, which might be more appropriate for your environment.

1.4      Transporting Data

Another common use for replication is to simply move data changes from publishers to subscribers. This method is particularly useful for moving transactional data to a data warehousing server, in which it is transformed and aggregated for OLAP reporting. SQL Server provides other ways of transporting data: DTS, BCP, BULK INSERT statements, and others. Be sure to carefully consider the alternatives before implementing replication because other solutions might be cheaper or even faster than replication.

2         Why and Why not Replication

2.1      Why Use Database Replication?

Imagine that a client has asked you to develop a contact-management solution that the company’s field sales staff can use to monitor sales and orders. Each sales representative has a laptop computer that can be connected to the company’s network.

A traditional approach to building this solution is to separate the tables from the other objects in the database so that the data can reside in a back-end database on a network server, or on the Internet or an intranet, while the queries, forms, reports, macros, and modules reside in a separate front-end database on the user’s computer. The objects in the front-end database are based on tables that are linked to the back-end database. When sales representatives want to retrieve or update information in the database, they use the front-end database.

Database replication enables you to take a new approach to building this solution by creating a single database that contains both the data and objects, and then making replicas of the database for each sales representative. You can make replicas for each user and synchronize each replica with the Design Master on a network server. Sales representatives update the replicas on their computers during the course of a work session, and users synchronize their replicas with the Design Master on the server as needed.

In addition, you can choose to replicate only a portion of the data in the Design Master, and you can replicate different portions for different users by creating partial replicas. In the scenario involving sales representatives who use replica databases, each individual salesperson typically needs only the sales data related to his or her own territory. Replicating all sales data for all sales representatives would involve unnecessary processing and duplication of data. By using partial replicas, you can duplicate only the data that each salesperson actually needs. A complete set of data is still contained in the Design Master, but each replica handles only a subset of that data.

2.2      When Database Replication Should Not Be Used?

Although database replication can solve many of the problems inherent in distributed-database processing, it is important to recognize that there are situations in which replication is less than ideal. You may not want to use replication if:

2.2.1      There are large numbers of record updates at multiple replicas. 

Solutions that require frequent updates of existing records in different replicas are likely to have more record conflicts than solutions that simply insert new records in a database. Record conflicts occur when any changes are made to the same record by users at different locations at the same time. Solutions with many record conflicts require more administrative time because the conflicts must be resolved manually. This is true even if different fields are updated within the same record.

2.2.2      Data consistency is critical at all times. 

Solutions that rely on information being correct at all times, such as funds transfers, airline reservations, and the tracking of package shipments, usually use a transaction method. Although transactions can be processed within a replica, there is no support for processing transactions across replicas. The information exchanged between replicas during synchronization is the result of the transaction, not the transaction itself.

3         Solution points:

 

  • As consolidation of all ships data to central database is primary objective, so general case supports implementation as replication.

 

  • With reference to point 2.2.1, as all ships have separate records of data of checklist, so we don’t have multiple updates of same record by different replicas.  So we are on safe side for replication.

 

  • With reference to point 2.2.2, as we don’t have data consistency issues with data, as logically data from different ships don’t have relationship between them.

 

  • Point under query is, “Is data really worth transferring via replication?” or simple Import or export via XML file and transfer by ftp will serve the purpose. Volume of data and frequency of data transfer will be a deciding factor.

 

References:

                 Microsoft MSDN

                 Google


Software Architecture- To study ETL (Extract, transform, load) tools specially SQL Server Integration Services

March 27, 2010

1           Objective:

 To study ETL (Extract, transform, load) tools specially SQL Server Integration Services.

2           Problem Definition:

There are few databases which moved from location to location

during day time, later on at closing of the day or after some specific time interval, data from few tables of these moving databases is needs to be pushed to central database tables after applying few business rule like avoiding duplication of data.

Traditional solution to problem is to build a small software in .Net that pull data from moving databases and apply business rules and push data to central database server.

This solution might work for small problem domain. But actually this is a bigger domain called ETL.

3           Solution:

4           What is ETL, Extract Transform and Load?

ETL is an abbreviation of the three words Extract, Transform and Load. It is an ETL process to extract data, mostly from different types of system, transform it into a structure that’s more appropriate for reporting and analysis and finally load it into the database. The figure below displays these ETL steps.

ETL architecture and steps

But, today, ETL is much more than that. It also covers data profiling, data quality control, monitoring and cleansing, real-time and on-demand data integration in a service oriented architecture (SOA), and metadata management.

  • ETL – Extract from source

In this step we extract data from different internal and external sources, structured and/or unstructured. Plain queries are sent to the source systems, using native connections, message queuing, ODBC or OLE-DB middleware. The data will be put in a so-called Staging Area (SA), usually with the same structure as the source. In some cases we want only the data that is new or has been changed, the queries will only return the changes. Some ETL tools can do this automatically, providing a changed data capture (CDC) mechanism.

  • ETL – Transform the data

Once the data is available in the Staging Area, it is all on one platform and one database. So we can easily  join and union tables, filter and sort the data using specific attributes, pivot to another structure and make business calculations. In this step of the ETL process, we can check on data quality and cleans the data if necessary. After having all the data prepared, we can choose to implement slowly changing dimensions. In that case we want to keep track in our analysis and reports when attributes changes over time, for example a customer moves from one region to another.

  • ETL – Load into the data warehouse

Finally, data is loaded into a data warehouse, usually into fact and dimension tables. From there the data can be combined, aggregated and loaded into datamarts or cubes as is deemed necessary.

ETL Tools:

 

ETL tools are widely used for extracting, cleaning, transforming and loading data from different systems, often into a data warehouse. Following is list of tools available for ETL activities.

No. List of ETL Tools Version  ETL Vendors
1. Oracle Warehouse Builder (OWB) 11gR1 Oracle 
2. Data Integrator & Data Services  XI 3.0 SAP Business Objects
3. IBM Information Server (Datastage) 8.1 IBM
4. SAS Data Integration Studio 4.2 SAS Institute
5. PowerCenter 8.5.1 Informatica 
6. Elixir Repertoire 7.2.2 Elixir
7. Data Migrator 7.6 Information Builders
8. SQL Server Integration Services 10 Microsoft 
9. Talend Open Studio 3.1 Talend
10. DataFlow Manager 6.5 Pitney Bowes Business Insight
11. Data Integrator 8.12 Pervasive
12. Open Text Integration Center 7.1 Open Text
13. Transformation Manager 5.2.2 ETL Solutions Ltd.
14. Data Manager/Decision Stream 8.2 IBM (Cognos)
15. Clover ETL 2.5.2 Javlin 
16. ETL4ALL 4.2 IKAN
17. DB2 Warehouse Edition 9.1 IBM
18. Pentaho Data Integration 3.0 Pentaho 
19 Adeptia Integration Server 4.9 Adeptia

 

http://www.etltool.com/etltoolsranking.htm

4.1        Microsoft Technology Solution:

Microsoft SQL Server Integration Services is a platform for building high performance data integration solutions, including packages that provide extract, transform, and load (ETL) processing for data warehousing.

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.

Integration Services Architecture:

Of the components shown in the previous diagram, here are some important components to using Integration Services succesfully:

4.1.1      SSIS Designer

SSIS Designer is a graphical tool that you can use to create and maintain Integration Services packages. SSIS Designer is available in Business Intelligence Development Studio as part of an Integration Services project.

4.1.2      Runtime engine

The Integration Services runtime saves the layout of packages, runs packages, and provides support for logging, breakpoints, configuration, connections, and transactions.

4.1.3      Tasks and other executables

The Integration Services run-time executables are the package, containers, tasks, and event handlers that Integration Services includes. Run-time executables also include custom tasks that you develop.

4.1.4      Data Flow engine and Data Flow components

The Data Flow task encapsulates the data flow engine. The data flow engine provides the in-memory buffers that move data from source to destination, and calls the sources that extract data from files and relational databases. The data flow engine also manages the transformations that modify data, and the destinations that load data or make data available to other processes. Integration Services data flow components are the sources, transformations, and destinations that Integration Services includes.

4.1.5      API or object model

The Integration Services object model includes managed application programming interfaces (API) for creating custom components for use in packages, or custom applications that create, load, run, and manage packages. Developer can write custom applications or custom tasks or transformations by using any common language runtime (CLR) compliant language.

4.1.6      Integration Services Service

The Integration Services service lets you use SQL Server Management Studio to monitor running Integration Services packages and to manage the storage of packages.

4.1.7      SQL Server Import and Export Wizard

The SQL Server Import and Export Wizard can copy data to and from any data source for which a managed .NET Framework data provider or a native OLE DB provider is available. This wizard also offers the simplest method to create an Integration Services package that copies data from a source to a destination.

4.1.8      Other tools, wizards, and command prompt utilities

Integration Services includes additional tools, wizards, and command prompt utilities for running and managing Integration Services packages.

4.1.9      Integration Services Packages

A package is an organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that you assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. You then save the completed package to SQL Server, the SSIS Package Store, or the file system. The package is the unit of work that is retrieved, executed, and saved.

4.1.10 Command Prompt Utilities (Integration Services)

Integration Services includes command prompt utilities for running and managing Integration Services packages.

  1. dtexec is used to run an existing package at the command prompt.
  2. dtutil is used to manage existing packages at the command prompt.

 

4.2      Typical Uses of Integration Services

Integration Services provides a rich set of built-in tasks, containers, transformations, and data adapters that support the development of business applications. Without writing a single line of code, you can create SSIS solutions that solve complex business problems using ETL and business intelligence, manage SQL Server databases, and copy SQL Server objects between instances of SQL Server.

The following scenarios describe typical uses of SSIS packages.

4.2.1      Merging Data from Heterogeneous Data Stores

Data is typically stored in many different data storage systems, and extracting data from all sources and merging the data into a single, consistent dataset is challenging. This situation can occur for a number of reasons. For example:

  • Many organizations archive information that is stored in legacy data storage systems. This data may not be important to daily operations, but it may be valuable for trend analysis that requires data collected over a long period of time.
  • Branches of an organization may use different data storage technologies to store the operational data. The package may need to extract data from spreadsheets as well as relational databases before it can merge the data.
  • Data may be stored in databases that use different schemas for the same data. The package may need to change the data type of a column or combine data from multiple columns into one column before it can merge the data.

Integration Services can connect to a wide variety of data sources, including multiple sources in a single package. A package can connect to relational databases by using .NET and OLE DB providers, and to many legacy databases by using ODBC drivers. It can also connect to flat files, Excel files, and Analysis Services projects.

Integration Services includes source components that perform the work of extracting data from flat files, Excel spreadsheets, XML documents, and tables and views in relational databases from the data source to which the package connects.

Next, the data is typically transformed by using the transformations that Integration Services includes. After the data is transformed to compatible formats, it can be merged physically into one dataset.

After the data is merged successfully and transformations are applied to data, the data is usually loaded into one or more destinations. Integration Services includes destination for loading data into flat files, raw files, and relational databases. The data can also be loaded into an in-memory recordset and accessed by other package elements.

4.2.2      Populating Data Warehouses and Data Marts

The data in data warehouses and data marts is usually updated frequently, and the data loads are typically very large.

Integration Services includes a task that bulk loads data directly from a flat file into SQL Server tables and views, and a destination component that bulk loads data into a SQL Server database as the last step in a data transformation process.

An SSIS package can be configured to be restartable. This means you can rerun the package from a predetermined checkpoint, either a task or container in the package. The ability to restart a package can save a lot of time, especially if the package processes data from a large number of sources.

You can use SSIS packages to load the dimension and fact tables in the database. If the source data for a dimension table is stored in multiple data sources, the package can merge the data into one dataset and load the dimension table in a single process, instead of using a separate process for each data source.

Updating data in data warehouses and data marts can be complex, because both types of data stores typically include slowly changing dimensions that can be difficult to manage through a data transformation process. The Slowly Changing Dimension Wizard automates support for slowly changing dimensions by dynamically creating the SQL statements that insert and update records, update related records, and add new columns to tables.

Additionally, tasks and transformations in Integration Services packages can process Analysis Services cubes and dimensions. When the package updates tables in the database that a cube is built on, you can use Integration Services tasks and transformations to automatically process the cube and to process dimensions as well. Processing the cubes and dimensions automatically helps keep the data current for users in both environments; users who access information in the cubes and dimensions, and users who access data in a relational database.

Integration Services can also compute functions before the data is loaded into its destination. If your data warehouses and data marts store aggregated information, the SSIS package can compute functions such as SUM, AVERAGE, and COUNT. An SSIS transformation can also pivot relational data and transform it into a less-normalized format that is more compatible with the table structure in the data warehouse.

4.2.3      Cleaning and Standardizing Data

Whether data is loaded into an online transaction processing (OLTP) or online analytic processing (OLAP) database, an Excel spreadsheet, or a file, it needs to be cleaned and standardized before it is loaded. Data may need to be updated for the following reasons:

  • Data is contributed from multiple branches of an organization, each using different conventions and standards. Before the data can be used, it may need to be formatted differently. For example, you may need to combine the first name and the last name into one column.
  • Data is rented or purchased. Before it can be used, the data may need to be standardized and cleaned to meet business standards. For example, an organization wants to verify that all the records use the same set of state abbreviations or the same set of product names.
  • Data is locale-specific. For example, the data may use varied date/time and numeric formats. If data from different locales is merged, it must be converted to one locale before it is loaded to avoid corruption of data.

Integration Services includes built-in transformations that you can add to packages to clean and standardize data, change the case of data, convert data to a different type or format, or create new column values based on expressions. For example, the package could concatenate first and last name columns into a single full name column, and then change the characters to uppercase.

An Integration Services package can also clean data by replacing the values in columns with values from a reference table, using either an exact lookup or fuzzy lookup to locate values in a reference table. Frequently, a package applies the exact lookup first, and if the lookup fails, it applies the fuzzy lookup. For example, the package first attempts to look up a product name in the reference table by using the primary key value of the product. When this search fails to return the product name, the package attempts the search again, this time using fuzzy matching on the product name.

Another transformation cleans data by grouping values in a dataset that are similar. This is useful for identifying records that may be duplicates and therefore should not be inserted into your database without further evaluation. For example, by comparing addresses in customer records you may identify a number of duplicate customers.

4.2.4      Building Business Intelligence into a Data Transformation Process

A data transformation process requires built-in logic to respond dynamically to the data it accesses and processes.

The data may need to be summarized, converted, and distributed based on data values. The process may even need to reject data, based on an assessment of column values.

To address this requirement, the logic in the SSIS package may need to perform the following types of tasks:

  • Merging data from multiple data sources.
  • Evaluating data and applying data conversions.
  • Splitting a dataset into multiple datasets based on data values.
  • Applying different aggregations to different subsets of a dataset.
  • Loading subsets of the data into different or multiple destinations.

Integration Services provides containers, tasks, and transformations for building business intelligence into SSIS packages.

Containers support the repetition of workflows by enumerating across files or objects and by evaluating expressions. A package can evaluate data and repeat workflows based on results. For example, if the date is in the current month, the package performs one set of tasks; if not, the package performs an alternative set of tasks.

Tasks that use input parameters can also build business intelligence into packages. For example, the value of an input parameter can filter the data that a task retrieves.

Transformations can evaluate expressions and then, based on the results, send rows in a dataset to different destinations. After the data is divided, the package can apply different transformations to each subset of the dataset. For example, an expression can evaluate a date column, add the sales data for the appropriate period, and then store only the summary information.

It is also possible to send a data set to multiple destinations, and then apply different sets of transformation to the same data. For example, one set of transformations can summarize the data, while another set of transformations expands the data by looking up values in reference tables and adding data from other sources.

4.2.5      Automating Administrative Functions and Data Loading

Administrators frequently want to automate administrative functions such as backing up and restoring databases, copying SQL Server databases and the objects they contain, copying SQL Server objects, and loading data. Integration Services packages can perform these functions.

Integration Services includes tasks that are specifically designed to copy SQL Server database objects such as tables, views, and stored procedures; copy SQL Server objects such as databases, logins, and statistics; and add, change, and delete SQL Server objects and data by using Transact-SQL statements.

Administration of an OLTP or OLAP database environment frequently includes the loading of data. Integration Services includes several tasks that facilitate the bulk loading of data. You can use a task to load data from text files directly into SQL Server tables and views, or you can use a destination component to load data into SQL Server tables and views after applying transformations to the column data.

An Integration Services package can run other packages. A data transformation solution that includes many administrative functions can be separated into multiple packages so that managing and reusing the packages is easier.

If you need to perform the same administrative functions on different servers, you can use packages. A package can use looping to enumerate across the servers and perform the same functions on multiple computers. To support administration of SQL Server, Integration Services provides an enumerator that iterates across SQL Server Management Objects (SMO) objects. For example, a package can use the SMO enumerator to perform the same administrative functions on every job in the Jobs collection of a SQL Server installation.

SSIS packages can also be scheduled using SQL Server Agent Jobs.

5           Conclusion:

  • Software Industry has may of ETL tools, but Comsoft being traditional Microsoft shop, should prefer SSIS, SQL Server Integration services as ETL tool for general operations.
  • As we are using SQL server as backend database, so SSIS is already available in our development environment. No Need to buy any extra tool.
  • This Document will provide a technology direction statement and introduction to ETL implementation in Comsoft.
  • This document is just for knowledge sharing, no need to change our implementation for pushing and pulling data via .Net  as discussed with Sujjet in last  voice call, as our problem domain is very limited. But for future direction and bigger problems we might consider SQL Server Integration services as ETL tools .

 

Reference:

ETL:

http://www.etltools.net

http://www.etltool.com

 http://etl-tools.info/

SSIS:

http://www.microsoft.com/sqlserver/2008/en/us/integration.aspx

 


Follow

Get every new post delivered to your Inbox.