Home > SSIS, Technical > Data Integration Services

Data Integration Services

Data, Data, every where,

And all the boards did shrink;

Data, Data, every where,

Nor any drop to drink.[1]

Of what use is the ocean of data, if it can not quench our thirst for decision-making- information.

Introduction [2]

Data can help us to understand the past better and get direction for the future. So we need to transform data into meaningful and actionable information.

A Real-World Scenario

Consider a departmental store which has details of its products and prices stored in a database. It would like to display them in its websites real time. There may be various promotions or offers on the products. This will affect the prices regularly. So all the products needs to be validated for right prices.

Some times, data conversion might be required. Also, we would love to see the products to be sorted according to certain categories. Different store owners will ask for the updated data. All this and much more tasks needs be done repeatedly as a job. Ok, let us hire a leader who can integrate all these tasks.

Challenges of Data Integration

At one level, the problem of data integration in our real-world scenario is extraordinarily simple. Get data from multiple sources, cleanse and transform the data, and load the data into appropriate data stores for analysis and reporting.

The most pertinent challenges are:

•      Multiple sources with different formats.

•      Structured, semi-structured, and unstructured data.

•      Data feeds from source systems arriving at different times.

•      Huge data volumes.

In an ideal world, even if you somehow manage to get all the data we need in one place, new challenges start to surface, including:

•      Data quality.

•      Making sense of different data formats.

•      Transforming the data into a format that is meaningful to business analysts.

Not only do you need to achieve all of these results, but also you need to achieve them as fast as possible. In extreme cases, such as online businesses, you must integrate data on a continuous basis.

Thus different (especially nonstandard) data sources need to be included in the Extract, Transform, and Load (ETL) process and more complex operations (such as data and text mining) need to be performed on the data.

SQL Server Integration Services (SSIS) is an effective toolset for both the traditional demands of ETL operations, as well as for the evolving needs of general-purpose data integration.

SSIS is part of the SQL Server Business Intelligence (BI)[3] platform that enables the development of end-to-end BI applications.

SSIS is used not only for large datasets, but also for complex data flows. As the data flows from source(s) to destination(s), you can split, merge, and combine the stream of data with other data streams, and also manipulate it on the fly.

back to [1] http://en.wikipedia.org/wiki/The_Rime_of_the_Ancient_Mariner

back to [2]  Most of the content is from  http://download.microsoft.com/download/a/c/d/acd8e043-d69b-4f09-bc9e-4168b65aaa71/ssis2008Intro.doc

back to [3] SQL Server Integration Services, Analysis Services, and Reporting Services all use a common Microsoft Visual Studio® based development environment called the SQL Server Business Intelligence (BI) Development Studio. BI Development Studio provides an integrated development environment (IDE) for BI application development.

Categories: SSIS, Technical
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: