Course ID: CLETLIS01

Introduction to Integration Services

  • Defining SQL Server Integration Services
  • Exploring the need for migrating diverse data
  • The role of business intelligence (BI)

[accordion_item in="true" id_parent="faq" title="SSIS Architecture and Tools"]

  • Managing heterogenous data
    • Leveraging the Extract, Transform and Load (ETL) capabilities of SSIS
    • Running wizards for basic migrations
    • Creating packages for complex tasks
  • Illustrating SSIS architecture
    • Distinguishing between data flow pipeline and package runtime
    • Executing packages on the client side or hosted in the SSIS service
    • Bullet proofing a package with package environment configurations
  • Simplifying deployments
    • Working in the new 2012 project mode
    • Deploying packages to the SSISDB
    • Running packages from SQL Server
    • Leveraging package parameters

[accordion_item in="false" id_parent="faq" title="Implementing Tasks and Containers"]

  • Utilizing basic SSIS objects
    • Configuring connection managers
    • Adding data flow tasks to packages
    • Reviewing progress with data viewers
    • Assembling tasks to perform complex data migrations
    • Migrating multiple files with FOREACH container
  • Operating system level tasks
    • Copying, moving and deleting files
    • Transferring files with the FTP task
    • Communicating with external sources
    • Sending messages through mail
  • Processing XML
    • Iterating XML nodes
    • Writing XML files from databases

[accordion_item in="false" id_parent="faq" title="Extending Capabilities with Scripting"]

  • Writing expressions
    • Making properties dynamic with variables
    • Utilizing expressions in loop iterations
  • Script Task
    • Extending functionality with the Script Task
    • Debugging, breakpoints, watches

[accordion_item in="false" id_parent="faq" title="Transforming with the Data Flow Task"]

  • Performing transforms on columns
    • Converting and calculating columns
    • Transforming with Character Map
  • Profiling, combining and splitting data
    • Merge, Union, Conditional Split
    • Multicasting and converting data
  • Manipulating row sets and BLOB data
    • Aggregate, sort, audit and look up data
    • Importing and exporting BLOB data
    • Redirecting error rows
  • Performing database operations
  • Executing a SQL task
  • Bulk inserting data from text files
  • [/accordion_item]
    [accordion_item in="false" id_parent="faq" title="Error Handling, Logging and Transactions"]

    • Organizing package workflow
      • Defining success, failure, completion and expression precedence constraints
      • Handling events and event bubbling
    • Designing robust packages
      • Choosing log providers
      • Adapting solutions with package configurations
      • Auditing package execution results
      • Accessing package data with ADO.NET

    [accordion_item in="flase" id_parent="faq" title="What is this course about?"]

    This course provides comprehensive coverage of Microsoft SQL Server Integration Services (SSIS). You learn how to migrate and transform data among heterogeneous data sources such as text files, Oracle, Access, XML, ODBC and OLE DB.

    The numerous hands-on exercises in this course are designed to illustrate real-world problems and provide practical solutions that you can apply immediately in the workplace.

    [accordion_item in="false" id_parent="faq" title="Who will benefit from this course?"]

    It is essential for organizations to securely manage data migration among a myriad of platforms to enhance their business intelligence capabilities.

    In this course, attendees learn to leverage SQL Server Integration Services to extract, transform, load and integrate data from a variety of sources.

    Typical participants include database developers and analysts who need to migrate data among diverse platforms.

    Database administrators who need to manage Integration Services packages stored on SQL Server and database professionals working in mixed environments of DBMSs such as Oracle, Access and SQL Server will benefit from this course.

    [accordion_item in="false" id_parent="faq" title="What background knowledge do I need?"]

    You should have a basic knowledge of relational database management systems and the SQL language. Specific experience with Microsoft SQL Server is helpful, but not required for this course.

    You should also have some knowledge of basic programming concepts but specific knowledge of a particular language is not required.

    Familiarity with the Windows interface and basic Windows security is recommended.

    [accordion_item in="false" id_parent="faq" title="Do I need to know SQL?"]
    A basic knowledge of SQL is required. You should be able to write a SELECT statement to query a table.
    [accordion_item in="false" id_parent="faq" title="How much of this class is focused on scripting and/or programming?"]

    Most tasks in this course can be accomplished with no programming.

    You will learn to use programming to extend the capabilities of SSIS even if you have never programmed before.

    All of the necessary information to complete the exercises will be given in the course.

    [accordion_item in="false" id_parent="faq" title="What is the role of SSIS in Business Intelligence?"]
    SSIS is one of the components in Microsoft’s Business Intelligence platform. The other two consist of Reporting Services and Analysis Services. SSIS allows you to migrate data for decision support purposes. Once migrated, data can be analyzed with Analysis Services and reports can be built using Reporting Services.
    [accordion_item in="false" id_parent="faq" title="Does this course cover the other components in Microsoft's Business Intelligence platform?"]
    The focus of this course is on SSIS. For more information on other BI components, please refer to Course 146, SharePoint® Business Intelligence: Hands-On. For more information on Analysis Services, see Course 139, SQL Server® Analysis Services for Business Intelligence, and for additional information on Reporting Services, see Course 140, SQL Server® Reporting Services: Hands-On.
    [accordion_item in="false" id_parent="faq" title="I am using Oracle as my main database platform. Will this course be helpful to me?"]
    Yes! Integration Services can be used to migrate data from virtually any source. For example, it can be used to migrate from Sybase to Oracle or even from Oracle 9i to 10g. Specific examples will be demonstrated in class.
    [accordion_item in="false" id_parent="faq" title="What platform is used for the hands-on exercises?"]
    This course uses Microsoft SQL Server 2012 running on Windows Server.[/accordion_item]

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>