Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you to get started with SSIS (SQL Server Integration Services)

Getting started with SSIS

Getting started with SSIS - Part 1: Introduction to SSIS

Feb 2 2012 12:00AM by Sudeep Raj   

SSIS is a tool used for ETL. Before we talk on SSIS, let me walk you through what is ETL.

ETL stands for Extract Transform and Load. These are simple day-to-day words we use in our daily lives. The figure below depicts ETL in real world scenario.

ETL simplified

E - Extract data from various homogeneous or non-homogeneous source systems. Data could be stored in any of the following forms though not limited to them: Flat file, Database, XML, Web queries etc. When we can have sources of such variety, the job of extraction is to fetch the data from these sources and make it available for the next step.

T - Transform Data: As already discussed that the data are coming from various sources and we cannot assume that the data is structured in the same way across all the sources. Therefore, we need to transform the data to a common format so that the other transformations can be done on them. Once we have the data we need to perform various activities like:

  • Data cleansing
  • Mandatory check
  • Data type check
  • Check for foreign key constraints
  • Check for business rules and apply business rules
  • Creation of surrogate keys
  • Sorting the data
  • Aggregating the data
  • Transposing the data
  • Trim the data to remove blanks.

The list can go on as the business requirements get complex day by day and hence the transformations get complex. While transformations are on, we need to log the anomalies in data for reporting and corrective action to be taken.

L Load Data: Once the transformations are done and the data takes the form as per the requirement, we have to load the data to the destination systems. The destinations can also be as varied as the sources. Once the data reaches the destination, it is consumed by other systems, which either stores it as historical data, generate reports out of it, build modes to take business decisions etc.

SSIS stands for SQL Server Integration Services. Microsoft introduced Business Intelligence Suite, which includes SSIS, SSAS (SQL Server Analysis Server) and SSRS (SQL Server Reporting Services).

Now what’s this Business Intelligence (BI)? Let me take some time to explain that. As the name suggests, it helps Business run across the globe. It provides the business with data and ways to look into the data and make business decisions to improve the business.

So, how do the 3 products work in the BI world or how are they organized? To start any business analysis we need data, and as I explained earlier, ETL would be used here to get the data from varied sources and put the data to tables or create Cubes for data warehouse. To do this, we make use of SSIS.

Once we have the data with us, SSAS comes into picture to organize the data and store them to cubes.

Next, we need to report the data so that it makes sense to the end user. This is where SSRS comes into picture for report generation.

The order of SSIS and SSAS could change, as both can come first. Having said this, SSIS makes the backbone of this entire domain as all the data is assembled using SSIS.

Now we dive in to what exactly SSIS is. Is it another coding language? The answer to the question is NO. SSIS is not another coding language. In fact very little coding is required in SSIS that too in very few cases. SSIS is a tool used for ETL. It is based on the paradigm shift where we need to focus more onto the business requirement and less on the actual coding to achieve the goals.

SSIS is a visual tool with drag and drop feature, which enables us to create SSIS, packages to perform ETL in a very short amount of time. The development time is widely reduced as compared to legacy systems where each aspect of ETL had to be coded and then of course, tested. Once the package is created, the visual look is good enough to give us an idea of what the ETL is doing.

SSIS provides us many tasks and transforms which help us build our ETL packages. A few examples would be Execute SQL Task which enables us to execute any T-SQL script from SSIS, Send Mail Task which enables us to send mail to any recipient. Likewise, there are lots of tasks and transforms available in SSIS to take care of most of the ETL scenarios that you can think of.

Note: I will explain what is Tasks and transforms in later chapters.

However, what if I have a scenario that cannot be accommodated in the existing tasks and transforms? SSIS has an answer to that too. In that case you can use the entire .NET library and code in either C# or VB to achieve your requirement. Though such scenarios would be very few and even if required will be of very basic nature where you need to know the basics of any programming language to get going. Let’s say that you are not satisfied with the logging provided by SSIS, you can always go ahead and write code to log the errors or activities the way you choose. Taking this a step further, if you want this new type of logging in many packages, you could convert your code to create a custom component and add it to all SSIS packages. It will now work as any other task or transform for you. Is that not great!!!

Note: C# is available only in SSIS 2008 and not 2005.

Let us peep into a simple SSIS package and get to know how SSIS looks:

BIDS Environment for SSIS development

This is how an SSIS package looks and feels. I will get into the details of what you see above in the coming chapters.


Sudeep Raj
12 · 13% · 4303
21



Submit

24  Comments  

  • A Great Start and its going to help a lot. "Like"

    commented on Feb 4 2012 1:59AM
    Manas Ranjan Dash
    39 · 5% · 1484
  • Thanks Manas, Soon you will be getting the other parts, All the best!!

    commented on Feb 4 2012 2:44AM
    Sudeep Raj
    12 · 13% · 4303
  • Thanks, very helpful!

    commented on Feb 6 2012 1:40AM
    Paras Doshi
    17 · 10% · 3265
  • Good start. I will follow the course. Looking forward SSAS after this course.

    commented on Feb 7 2012 1:27AM
    koziet
    2770 · 0% · 4
  • Thanks.i will follow the course daily. And looking forward.

    commented on Feb 7 2012 2:54AM
    santosh
    289 · 0% · 148
  • Great Start. Like

    commented on Feb 7 2012 3:17AM
    krtyknm
    1926 · 0% · 10
  • Thank you. I will follow it through.

    commented on Feb 7 2012 7:37AM
    vdhandap
    2853 · 0% · 3
  • Good one looking for something like this in simple explanation's , in last if you could provide the examples and materials which you are explaining us that will be very help full. Thanks a lot.

    Regards Jkr

    commented on Feb 7 2012 3:12PM
    Jayakrishna
    1115 · 0% · 23
  • Good work Sudeep!!!

    Shaji

    commented on Feb 7 2012 4:29PM
    sukhan
    1542 · 0% · 13
  • Good one Sudeep,

    Expect next part will be in details with tool and how to use in each case one by one

    Thank You for Sharing !!

    commented on Feb 7 2012 11:22PM
    Kirti M.Darji
    10 · 16% · 5010
  • Thanks, I just started learning SSIS. Your posts on SSIS will help me a lot. I will follow all step by step

    commented on Mar 4 2012 12:07PM
    Ashish Jain
    448 · 0% · 88
  • Good explanation. Thanks

    commented on Mar 15 2012 1:37AM
    Deepak
    1210 · 0% · 21
  • Excellent!

    Thank you!

    commented on Apr 4 2012 3:34AM
    Adam Tokarski
    58 · 3% · 1021
  • recently shifting into support on SSIS and DTS from development what is future career in this job? Or any certification? What can I learn further to do ahead

    commented on May 11 2012 8:37AM
    himanshuk
    1417 · 0% · 15
  • Hi himanshu, SSIS does have a good future, you need to learn about DataWarehousing along with SSIS. Master the various tasks and transforms available. There are 2 ceritfications available for MSBI none for SSIS only. You can take them. Also learn about SSAS and SSRS.

    commented on May 14 2012 4:51AM
    Sudeep Raj
    12 · 13% · 4303
  • Hi, Sudeep da, thank you very much for such kind of great article. I think, this will be very helpful for all beginner like me. Plz add more for parametrized package.

    commented on Nov 18 2012 6:17AM
    abamurad
    2853 · 0% · 3
  • Good One. Thank you

    commented on Nov 22 2012 9:56PM
    tamil
    2853 · 0% · 3
  • Good one,looking forword with all tools with explanation I will follow the course. Ur explanation is too good.

    Keep it up.

    Thanks.

    commented on Nov 26 2012 4:05AM
    Abhijeet Desai
    328 · 0% · 129
  • This looks like an in depth tutorial, but the images are missing from a few of the pages.

    Thanks...

    commented on Dec 17 2012 9:56AM
    Bradford
    2853 · 0% · 3
  • Good thanking you

    commented on Dec 19 2012 12:33AM
    Arun Ponnurangam
    642 · 0% · 54
Previous 1 | 2 Next

Your Comment


Sign Up or Login to post a comment.

"Getting started with SSIS - Part 1: Introduction to SSIS" rated 5 out of 5 by 21 readers
Getting started with SSIS - Part 1: Introduction to SSIS , 5.0 out of 5 based on 21 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]