SQL Server Integration Services (SSIS) Training for Business Intelligence

SSIS Training Course Outline

• Introduction to Integration Services

  • Defining SQL Server Integration Services (SSIS)
  • Exploring the need for migrating diverse data
  • The role of Business Intelligence (BI)

• SSIS Architecture and Tools

  Managing heterogeneous data

  • Leveraging the Extract, Transform and Load (ETL) capabilities of SSIS
  • Running wizards for basic migrations
  • Creating packages for complex tasks
  • Explore other supported data sources, such as Hadoop and NoSQL

  Illustrating SSIS architecture

  • Distinguishing between data flow pipeline and package runtime
  • Executing packages on the client side or hosted in the SSIS service
  • Bulletproofing a package with package environments
  • Package restartability with checkpoint files

  Simplifying deployments

  • Distinguishing between project and package deployment modes
  • Deploying packages to the SSISDB
  • Running packages from SQL Server
  • Leveraging package parameters

• 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

• 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 and watches

• 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 and 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

• 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