Welcome to the world of Infosys Engineering! It is a half a billion plus organization that takes pride in shaping our engineering aspirations and dreams and bringing them to fruition. We provide engineering services and solutions across the lifecycle of our clients’ offerings, ranging from product ideation to realization and sustenance, that caters to a cross-section of industries - aerospace, automotive, medical devices, retail, telecommunications, hi tech, financial services, energy and utilities just to name a few major ones.

« August 2012 | Main | October 2012 »

September 27, 2012

SQL Server Integration Services: Evolution as a Data Transformation and Warehousing Utility

Typically Integration Services come with a rich set of Tasks, Containers, Transformation Functions and Data Adapters for support and maintenance of business applications. SSIS provides similar services which have evolved over the years from a simple DTS Executable utility to a full-fledged Integration Service offered with the SQL Server Database Platform. SSIS can help in solving complex business problems using ETL and BI and also manage SQL Server Databases and other SQL Server objects.

Business Scenarios for SSIS Usage

SSIS is used in a variety of Data Transformation, Migration and Warehousing scenarios. Few Typical business scenarios are:

·         Copying subset of data from a Large Volume Transactional Database to a Reporting Database to generate various Operational and Analytical Data Reports

·         Migration and Replication of Data from a Development or Test Database Server to Production Servers and vice-versa.

·         Merging Data from various heterogeneous Data Sources to s Single Destination Database Store for further usage or analysis. Further usage of such data will include using them as archive information for generating trend analysis reports and standardizing the data formats for future transactions of an application.

·         Aggregating data on a periodic basis using batch processing for Reporting application for faster report data processing.

·         SSIS also helps in cleaning and standardization of data which comes from various data sources before loading to destination database.

·         It also helps Database administrators in automating the administrative functions like backup and restore. They can be scheduled over SQL Server Agent jobs which help in scheduling memory intensive jobs at non-peak hours and hence prevent interference to regular transaction performance especially in cases of OLTP and OLAP Applications.


In summary SSIS helps in providing a Business Intelligence Tool for data transformation processes. From a simple data extraction, load and transform utility SSIS has evolved into a more flexible utility which is easier to debug since it has a graphical user interface and can perform tasks ranging from execution of workflows, data transformations to aggregate, merge and copy data and administrative tasks like data backup and restore. Also it has an application programming interface for programming the integration services tasks using the SSIS object model.


DTS was Microsoft's first ETL utility which was introduced with SQL Server 7.0. It helped improve its Business Intelligence Capabilities. However with the advent of SSIS, Microsoft introduced a new processing engine which provides in-memory buffers, data extraction capabilities and data transformation capability to modify data and make it available to other processes. It enhances the DTS capabilities in the following aspects:

·         Faster data processing and high volume data processing

·         SSIS provides enterprise level capabilities from simple import/export functionality to complex data transformations which can utilized in large scale data warehousing applications

·         It is more easily customizable and scalable as it leverages the .NET framework capabilities to build custom components if not provided out of the box, unlike DTS which did not have a common framework.

·         It provides a robust mechanism for iterative processing by using For Loop and ForEach containers not present in DTS

SSIS Performance Issues and Optimization

Since SSIS executes on large chunks of data it comes with a set of performance concerns like memory utilization during SSIS package executions, CPU load balancing, I/O processing speed and network utilization. However as per the SQL Performance team, SSIS can load 1.18 TB of data in 30 minutes and can process 4.5 million sales transaction rows per second.

Some of the design practices to ensure that SSIS packages perform up to expected levels are:

·         Ensure that all transformations occur in memory

·         Perform capacity planning by understanding resource utilization

·         Optimize the SQL Server data sources, destinations and transformation lookups using optimized SQL queries and stored procedures, indexed tables and optimized data transformation methods.

·         Logging if done in an SSIS package should be minimal to minimize memory usage.

There are various methods to troubleshoot and debug low memory condition in SSIS. Few methods can be:

·         Execute SSIS on a separate computer that is not executing an SQL Server instance.

·         During package execution set the Maximum server memory to a small value to increase available memory.

·         Wherever applicable execute SSIS tasks in sequential manner rather than parallel to handle low memory conditions.

·         Tweak the values of DefaultMaxBufferRows and DefaultMaxBufferSize to get as many records as possible.

·         Avoid unnecessary column usage in Dataflow tasks and configure data types correctly to reduce estimated size.

·         Partially blocking transformations like Merge, Merge Join and Union All and Blocking transformations such as Sort and Aggregate consume the maximum amount of memory since a separate buffer is created for their outputs and new thread is introduced in the dataflow. These transformations are asynchronous in nature and should be used carefully to avoid low memory conditions.

·         Key tools and techniques for debugging SSIS issues are SSIS logging, SSIS performance counter monitoring and SQL Server Profiler.

SSIS Improvements and Capabilities in SQL Server 2012

With the release of SQL Server 2012, further improvements have been made in troubleshooting and logging features of SSIS like capturing data flow component timing information and row counts for all paths within a data flow. Also data taps can be added to a data flow path to capture the data in CSV format during package execution which aids in troubleshooting data issues.

Also SSIS is a useful utility  to migrate data from an on premise SQL Server to SQL Azure. There is however a limitation to this since only data can be moved and not database objects like tables, stored procedures or triggers. The reason for this is that SSIS uses SMO (SQL Server Management Objects) for moving objects and as of now SMO doesn't support SQL Azure. For this reason the database objects like tables and stored procedure need to be moved using Generate and Publish Scripts wizard and then followed by using SSIS for data transformation and migration. There are other alternatives of migrating data to SQL Azure like using the BCP utility to bulk copy data from source to destination (SQL Azure). However BCP lacks SSIS's ability to convert data from one data type to another and also SSIS workflow components.

As of now Microsoft has not come out with a clear strategy of supporting all SSIS Tasks on SQL Azure. During one of the Microsoft PDC (Professional Developers Conference) on SQL Server Services, ETL in the cloud was mentioned but there was no specific mention of SSIS. Many blog writers have questioned the need of ETL in the cloud which makes the future of SSIS in the cloud bleak.

Detailed Reading and further references on various aspects of SSIS are available at MSDN, Technet, Microsoft Support, MSSQL Tips and SQLServerPedia.










Collaboration possibilities with Lync 2013 preview release

Unified Communication (UC) platform provides a real-time collaboration experience for the enterprise and its customers.  Apart from unified messaging and presence notifications, a UC platform provides business process integration, conferencing capabilities and integrated collaboration tools. Microsoft's first major UC platform release was OCS 2005 and has recently announced the release of Lync 2013 preview along with the Microsoft Office 2013 suite of products.  See my previous blog on Microsoft's earlier release, Lync 2010.

Here we check out the new features available with Lync 2013 and also will see how these developments in the unified communication platform is going to change the way enterprises collaborate.

Lync 2013 Features

Lync 2013 has added many features on top of Lync 2010.

1. Multiparty HD Video and content sharing
    - Multiple videos and contents by different participants could be displayed.
    - Identifies the active user.
2. Federation with Skype
    - Lync-to-Skype federation is going to open up a new door for enterprises looking for collaborating with a larger consumer-base.
3. New Lync meetings features for the presenter
    - Multiple programs can be shared now.
    - Power point presentations with video clips are supported.
    - PowerPoint deck can reviewed privately and can then switch to presenter mode.
4. Lync Mobile with Lync client support for Windows Phone, iOS and Android devices
   - Collaboration capabilities extended to mobile devices as well.
5. Lync for mobile clients
    - Lync clients available for Windows Phone, iPad, iPhone and Android devices.
   - Doesn't require Virtual Private Network (VPN). Connectivity possible through corporate network or through the mobile service provider data channel.
6. Full IPv6 support
7. Persistent Chat rooms and topic feeds
8. Virtual Desktop Infrastructure (VDI) Plug-in
   - A user can sign-in to Lync 2013 client running on a virtual machine and participate in real-time communication.
9. Lync 2013 SDK includes
   - Unified Communications Managed API 4.0 (UCMA 4.0)
   - Unified Communication Web API.
   - Mobility Service

Collaboration Scenarios

Ever since OCS 2005, each newer release has added on features and collaboration capability. Considering the presence of other Instant Messaging (IM) technologies like Jabber, Google Talk, AOL etc...collaboration has indeed gained popularity as an end user expectation. In terms of today's technology trends, we are in a period where mobile computing; cloud computing and social networking is concepts that are attracting a lot of attention. Smartphones, tablets and social media are new channels of communication and collaboration. In the context of Office 365, Lync Online, Lync On-premise and the newer release of Lync 2013, the following would be some of the areas of interest as regards enterprise level collaboration mechanisms.

Within the Enterprise

Within the enterprise, effective collaboration enables a productive workforce. Many of the features in Lync 2013 are geared towards providing an enhanced collaborative experience.

  - Enhancements to presenter controls with  Lync meetings
  - Lync client application for Mobile devices
  - Virtual Desktop Infrastructure (VDI) Plug-in
  - Unified integration of Lync with all office products
  - Enhancement over OneNote integration. Co-edit and sharing of notes with OneNote during meetings is possible.

Lync 2013 features enable to have collaborative software/applications for the enterprise. Team collaboration over unified communication would help geographically dispersed team members to work together. A collaborative custom application built over Lync 2013 can leverage OneNote integration and persistent chat rooms as well. Co-editing and tracking changes to a document in a Lync session by the participants will make group reviews, discussions more productive and efficient. Collaborative work would enable a group of people to work on a document or a work item together. It would also mean getting connected to your team on a single click from whichever application or environment you are currently in.Lync SDK would be supporting such application developments.

Within a mobile workforce

Establishing communication channels for mobile workforce of enterprises has always been a challenging task. This is one area where unified communication can be leveraged. Lync 2013 provides collaboration through federation that enables you to get connected to the enterprise network without the need of VPN.  An application built over UC can leverage this channel to enable the user to capture all his viewpoints and strategies from a business point of view. The same can be shared with corporate users of the enterprise on-line.

Expanding collaboration to Enterprise consumer base

There are many business scenarios where a consumer would want details on a single click in an application. Consumers get varied and a vast set of details from web as they browse for information.  Mostly these don't help them much in taking the right decision and in turn many enterprises lose the consumer to a competitor. What consumer would require is the latest information and that would be best served by the enterprise itself.  So enterprises need to find ways of direct collaboration with consumer and thus tap the potential. Unified communication enables one to build an application that can give options to a consumer to talk and share details on the information required. Lync 2013 SDK has Web APIs which could be leveraged to bring communication channel supporting text, audio and video capabilities in a web application. This channel can be leveraged by the enterprise to reach out to the huge consumer base. Plug in this communication channel to your web application, social media and smart phone applications, so that the consumer can reach out to the enterprise agent at any point of time.

A fully integrated unified communication platform within an enterprise is going to increase the productivity of the workforce.  Identifying the business scenarios for collaboration and building custom application over Lync is going to define the collaboration mechanism within the enterprise, customers, consumers and workforce. This is an area where enterprises should be more focused.

September 14, 2012

Evolution of SharePoint 2010 as an Enterprise Content Management Solution

Essentially SharePoint was designed as a collaboration platform which makes it easier for people to work together. It allows people and organizations to share information among themselves and with others, help manage documents and information libraries and publish reports for improved decision making.

However over the years SharePoint has evolved as an Enterprise Content Management System with Focus on managing Documents, Records, Web Content and Rich Media Content.

In this article we will be reviewing a few areas where SharePoint 2010 has evolved from earlier versions of SharePoint as an ECM Platform and also areas which require further improvements.

Web Content Management Solutions have a set of objectives which needs to be reviewed while designing the solution. Some of these objectives are:

  • Utilizing corporate web sites as a source for sales leads
  • Allow Customer Self-Service
  • Centralizing content Life cycle across all digital assets
  • Management of Content Life Cycle
  • Providing Content control to content owners
  • Promoting thought leadership
  • Complex Approval Process Requirements to adhere to regulatory and compliance needs
Keeping these objectives in mind some of the Key Feature enhancements and capabilities provided in SharePoint 2010 Content to make it a viable ECM option are:

  • Enhanced Web Authoring Experience which includes editing content, applying customized styles and themes and using the New SharePoint UI to change the page layouts and structure. SharePoint 2010 has an improved Rich Text Editor (RTE) which provides "Word like" editing experience like rich formatting of text, live preview of formatting options, easy embedding of images and videos directly into the RTE and drag and drop capability to place images and video wherever required. SharePoint 2010has enhanced its theming capabilities to apply a new set of colors to your site. The New Ribbon Interface of SharePoint 2010 allows text formatting options like styles, fonts etc. which is easy to use.
  • Document Sets is another feature which has helped enhance the ECM capabilities of SharePoint 2010 as they make it faster and easier to work on Project related documents and artifacts.Features which make document sets more useful then folder archiving and management in earlier versions are:
    • A Welcome page webpart which displays the contents of the document set and can also be customized to display information other than documents.
    • On creating a document set some default documents can be automatically provisioned.
    • Shared metadata enforces the same metadata on all items inside the document set.
  • SharePoint 2010 provides an enhanced Content Query Web part (CQWP) which helps which supports filtering metadata on items being queried or value passed to the page in the url query string. This feature helps in supporting dynamic content viewing.For example if a page is used to display News or Announcement in an intranet portal and we need to display all the news which is related to current news or announcement, the CQWP will help us to filter the required information from the page.
  • The New Managed Metadata service of SharePoint 2010 helps in representing corporate taxonomy. It helps in defining a hierarchical collection of terms and their further usage as attributes for Items. The terms can also be helpful in tagging content in SharePoint, driving dynamic navigation and Search Engine Optimization.
  • Improved reliability of Content Deployment feature in SharePoint 2010 which helps in deploying content from authoring/staging environment to production environment.For improving reliability better logging information is available which provides insight into Content deployment jobs and their issues. Also database snapshots can be used to work on authoring a site while Content deployment jobs are in progress.
  • Improved Flexibility in publishing approval process depending on type of WCM deployment. Also workflows can be modeled using Microsoft Visio 2010 and imported into SharePoint Designer 2010 and then reused to apply on content types and site templates.
  • SharePoint 2010 provides a new range of Web Analytics capabilities to monitor different aspects of site usage, thereby providing an understanding of a web site's performance. One of the important features added in SharePoint 2010 is "Search Insights" which helps in monitoring what visitors are searching on a site, what the top search queries are, which search queries are failing etc., which can then be used to enhance fine tune content and meta tags to enhance user discovery and experience.
  • SharePoint 2010 has made significant improvements in social collaboration capabilities such as blogs, wikis and social networking capabilities like discussion forums, ratings and comments.
  • A New Offline Client - SharePoint Workspace, helps in managing SharePoint content offline.
  • Improved LOB (Line-Of-Business) Integration capabilities using Business Connectivity Services (BCS).
  • Improved Search Capabilities due to integration with FAST Search Server 2010.

Although significant improvements have been made in SharePoint 2010, there is still a long way to go to make it a mature CMS Platform. Part of it can be attributed to the fact that SharePoint was not primarily designed as a Content Management Platform. A few areas where traditional Content Management Platforms score over SharePoint 2010 are:
  • Low upfront and Total Cost of Ownership.
  • Ease of development, administration and end user adoption.
  • Managing multiple Browsers, devices and languages.
  • Extensive dependency of SharePoint on IT resources compared to other CMS Platforms in terms of tools to enable capabilities like content targeting, multi-site and multi-device deployments. SharePoint business users need extensive training to use the CMS capabilities to manage complex websites and applications compared to other CMS platforms
  • SharePoint offline capabilities are still not comparable with other WCM platforms.
  • Content targeting in SharePoint is still weak.
  • SharePoint 2010 is not a good option for high-volume transactional content applications, the reason being that SharePoint has been strategically built with the objective of allowing users to share documents and information on corporate intranets and internet portals which do not require any high volume transaction capability.

According to Forrester Research, enterprises need not adopt SharePoint 2010 as an ECM Platform just because it has been successful in collaboration and intranet applications. There are certain areas where solution objectives may be achieved with customization but that comes at a cost. Forrester suggests that organizations should invest in SharePoint 2010 only if they are looking for a solution with a wide reach rather than a targeted point solution like a KMPortal or public facing blogs.

For further information of using SharePoint 2010 as a Content Management solution, refer to this CMS Report article here and ECM blogs on MSDN.

Subscribe to this blog's feed

Follow us on

Blogger Profiles

Infosys on Twitter