0 to SSIS in 60 minutes

Just found the pragmaticworks website allows a free access to an extensive amount of webseminars on the BI, let’s start with the SSIS

Terms and Acronyms

Joe introduces first some acronyms and definitions, the most important regarding SSIS are in bold

BI>>> Business Intelligence     DW>>> Data Warehouse     SSIS>>> SQL Server Integration Services     BIDS>>> Business Intelligence Development Stud.     ETL>>> Extract, Transform & Load   Package>>> object (XML file) that contain the business logic to manage workflows and process data     Container>>> object to group tasks     Task>>> component that performs an operation   Transformation>>> component to modify and manipulate data     Fact>>> A business measurement     Measure>>> A quantifiable business process    Dimension>>> Breakdown measures according to an area of interest     Attribute>>> Characteristic that makeup a dimension member         SCD>>> Slowly Changing Dimensions      Natural Key>>> Unique key from data source     Surrogate Key>>> Alternate unique key in the data warehouse     Cube>>> Data structure that groups measures, dimensions, KPIs…

What is SSIS?

SSIS stands for SQL Server Integration Services; it’s a platform for data integration and workflow applications

It can be used as a Tool for:

• Data extraction, transformation, and loading (ETL) and/or data extraction, loading then transforming (ELT)

• To make DML operation (Inserts, Updates, Deletes). Can also execute DDL operations (Create/Drop Table)

• To automate maintenance and tasks for SQL Server database administration

• To update OLAP cube partitions, slowly changing dimensions

SSIS task groupings

The SSIS tasks (component to perform operation) can be grouped at high level as:

Control Flow Data Flow
1. Data Tasks 1. Sources
2. Database Object Transfer Tasks 2. Destinations
3. Analysis Services Tasks 3. Transformations
4. File and Network Protocol Tasks
5. Script and Program Tasks
6. Package Execution Tasks
7. WMI Tasks
8. Database Maintenance Plan Tasks
9. Other Maintenance Tasks

The bold are the one addressed in the demo by Joe

T-SQL to SSIS mapping

There is a direct mapping of task/operation in T-SQL to the task available in SSIS:

Demo

The plan is to replicate the demo content; I strongly suggest doing that to familiarize with the SSIS environment and tasks.

A post will come on that and I will share the SSIS packages content, it looks like Jose didn’t do that😉

Reference

Main resource: http://pragmaticworks.com/Resources/webinars/Default.aspx

Thanks to Koen for the info about PragmaticWorks training course

Author blog: http://sqljoe.wordpress.com/

video: http://sqllunch.com/videos/lunch44.wmv

slideshow: http://sqljoe.files.wordpress.com/2010/12/learning-ssis-under-1-hour-sql-lunch.ppsx

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s