0345 4506120

SQL Server Integration Services for Business Intelligence

Course Details

Name SQL Server Integration Services for Business Intelligence
Description
URL
Location:
Virtual Classroom
Start Date:
Working Days:
Price:
£1737.00 +vat
was £1899.00
Availability:
Exam:
Residential:
Course ID:
437552
Offer

Overview

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

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 analysed with Analysis Services and reports can be built using Reporting Services.

In this SSIS training course, you gain the skills to improve business intelligence capabilities by automating complex migration tasks, auditing the success or failure of migration processes, transforming data amongst heterogeneous data sources — such as text files, Oracle, Access, XML, ODBC and OLE DB — and effectively and securely managing data migration across various platforms.

In this SSIS training course, you’ll gain the skills to migrate and transform data with SQL Server Integration Services. You learn to solve data management problems by creating dynamic packages for migrating, processing, and reporting on data for business intelligence. Specifically, you learn to deploy packages to an SSIS server, assemble tasks to perform complex data migrations and enhance package functionality with scripting.

Software

  • This course uses Microsoft SQL Server 2016, running on Windows Server 2016
  • This course is applicable to those using earlier versions of SQL Server

Target Audience

SQL Server developers, administrators and analysts who need to migrate and transform data amongst diverse platforms.

Who will benefit from this course?

It is essential for organisations to securely manage data migration amongst 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 amongst diverse platforms. Database administrators who need to manage Integration Services packages stored on SQL Server and database professionals working in mixedenvironments of DBMSs such as Oracle, Access and SQL Server will benefit from this course.

After-course Computing Sandbox

You'll be given remote access to a preconfigured virtual machine for you to redo your hands-on exercises, develop/test new code, and experiment with the same software used in your course.

Learning Objectives

You Will Learn How To:

  • Solve data management problems by migrating and processing data
  • Perform nonstandard imports and exports using the Script component
  • Assemble tasks to perform complex data migrations
  • Extract, Transform, and Load (ETL) data
  • Incrementally migrate data from SQL Server data sources using Change Data Capture (CDC)
  • Deploy packages to an SSIS catalogue

Pre-Requisites

Recommended Experience

Familiarity with relational database management systems, programming concepts, and SQL language

This is an intermediate-level SQL training course. We recommend ome knowlede of relational databases management systems, programming concepts, and SQL language prior to taking this course.

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.

Course Content

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

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

Organising 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

Virtual

Description:

Online AnyWare™ immerses you in a dynamic online training environment where you access and participate in an actual classroom. You interact with both your instructor and your peers—just as if you were together with them in the classroom—as you master the essential knowledge and skills in today's most important IT and management topics.

With AnyWare Online Training, you can...

  • Access your course from any Internet-accessible location
  • Gain scheduling flexibility—choosing from a wide selection of course dates
  • Immerse yourself in an actual classroom experience
  • Benefit from AnyWare's unique real-time interactivity
  • Achieve the same results that you expect from an instructor-led classroom course

Frequently Asked Questions

What are the technological requirements for participating in a live, online course delivered via Online AnyWare?

To participate fully in a virtual AnyWare course, you will need the following:

  • An audio-enabled computer
  • A minimum screen resolution of 1024 x 768
  • An Intel Pentium® 4 computer, or equivalent, with a 2.5 GHz or faster processor; or an Intel Mac
  • A minimum of 1 GB of RAM
  • A reliable high-speed Internet connection, either via cable modem, DSL or corporate LAN (a wired Internet connection is strongly recommended)
  • Internet Explorer 7 or higher; or Mozilla Firefox 2 or higher
  • Support for Java and ActiveX within your browser with the latest version of Java installed
  • Adobe Flash version 8 or higher
  • Access to www.logmein.com
  • Ability to install the LogMeIn ActiveX control, which may require local administrative privileges
  • Learning Tree AnyWare will support a dual-monitor setup, which allows concurrent access to the classroom and the computer used for the hands-on exercises. This setup may enhance your training experience; however, it is not a requirement for full participation in any of our courses.

Do I need to install any special software?

None! All you need is a high-speed, flash-enabled Internet browser—Internet Explorer 7 or higher, Mozilla Firefox 2 or higher, or Safari.

Can I participate in a course via Online AnyWare using any type of operating system?

You can participate in a class on a PC or Mac. However, the hands-on exercise material in Course 2320 and Course 984 is not compatible with the Mac operating system at this time. Please contact our AnyWare support team if you are planning to participate in either of these courses using a Mac operating system.

Do I need my own microphone, earphones and/or headset?

Prior to the course, we will provide a microphone-enabled headset for you to use during the course. The headset is yours to keep after the course.

Is the virtual classroom experience really the same quality when it's delivered over the Internet?

Absolutely! Online AnyWare is designed to provide you with the same experience you would receive in the classroom. You connect to an actual classroom where you participate online in live, instructor-led training that is facilitated either at one of our Education Centres or on site at a client's location. As an online participant, you engage with the instructor and interact with your classmates just as if you were in the actual classroom. And you perform all of the same hands-on exercises, so the knowledge you learn and the skills you gain are no different than if you were taking the course as an in-class participant.

Why is Online AnyWare so much more effective than off-the-shelf, self-paced e-learning programmes?

The most important benefit of live, instructor-led training is the ability to interact with your instructor and fellow classmates. It is through such interaction that real learning takes place. Courses taken via AnyWare offer the same benefits. In fact, over half the time spent in courses is dedicated to interactive activities—where real learning happens! You'll have your specific questions answered on the spot, receive immediate clarification of anything that is confusing, perform hands-on exercises by remotely accessing an in-class PC, and share your experiences in real time with your fellow classmates. Most importantly, you'll receive expert advice on how best to apply what you learn to your own projects.

How is the classroom experience actually delivered?

Adobe Connect is the foundation technology for our AnyWare training delivery solution used to deliver our IT and management courses. You'll acquire new skills in a safe environment—your computer's files and applications are never engaged, so there is no risk of damage. A hard copy of the course materials and a microphone-enabled headset will be shipped to you prior to the course.

Using your Internet browser, you remotely access a dedicated in-class PC so that you can participate in hands-on exercises and develop new skills just as if you were in the actual classroom where your instructor is coaching in real time.

How do I connect to the classroom?

One week prior to the class start date, you will receive a "Getting Ready" e-mail with a URL and your login credentials. We'll ask you to complete a three-step validation process prior to the class start date to test your system, two-way audio connection, and Internet connection to the classroom and to your dedicated, in-class PC on which you'll be performing your hands-on exercises.

A few days before your class starts, you'll also receive a call from one of our customer service representatives who will answer any questions you may have. When it's time for the course to begin, just log in—from anywhere—and join your classmates, live in the classroom. During your course, our AnyWare support technicians are available to assist you as needed. It's that easy!

What are the course hours?

Start and end times vary for each individual course. Please confirm the start and end times during registration, as your course may be delivered from a different time zone than your own. On the first day of class, plan to log in to your classroom 30 minutes prior to the start of the class.

Our Customers Include