Logo

Maxtrain.com - [email protected] - 513-322-8888 - 866-595-6863

AW-SSIS16

Microsoft SQL Server Integration Services – 2016

Alert Me

Description

Microsoft SQL Integration ServicesMicrosoft SQL Integration Services is a platform for building enterprise-level data integration and data transformations solutions. Use Integration Services to solve complex business problems by copying or downloading files, loading data warehouses, cleansing and mining data, and managing SQL Server objects and data.

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.

Integration Services includes a rich set of built-in tasks and transformations, graphical tools for building packages, and the Integration Services Catalog database, where you store, run, and manage packages.

You can use the graphical Integration Services tools to create solutions without writing a single line of code. You can also program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

In SQL Server 2016, SSIS introduces new capabilities that let you easily deploy to a centralized SSIS Catalog (i.e. SSISDB user database). In order to provide high availability for the SSISDB database and its contents – projects, packages, execution logs, and so on – you can add the SSISDB database to an Always On Availability Group, just like any other user database. When a failover occurs, one of the secondary nodes automatically becomes the new primary node.

Microsoft SQL Integration Services Server Data Tools

Working in SQL Server Data Tools (SSDT), you can perform the following tasks:

  • Run the SQL Server Import and Export Wizard to create basic packages that copy data from a source to a destination.

  • Create packages that include complex control flow, data flow, event-driven logic, and logging.

  • Test and debug packages by using the troubleshooting and monitoring features in SSIS Designer, and the debugging features in SQL Server Data Tools (SSDT).

  • Create configurations that update the properties of packages and package objects at run time.

  • Create a deployment utility that can install packages and their dependencies on other computers.

  • Save copies of packages to the SQL Server msdb database, the SSIS Package Store, and the file system.

     

    Microsoft SQL Integration Services Server Management Studio

    SQL Server Management Studio provides the Integration Services service that you use to manage packages, monitor running packages, and determine impact and data lineage for Integration Services and SQL Server objects.

    Working in SQL Server Management Studio, you can perform the following tasks:

    • Create folders to organize packages in a way that is meaningful to your organization.

    • Run packages that are stored on the local computer by using the Execute Package utility.

    • Run the Execute Package utility to generate a command line to use when you run the dtexec command prompt utility (dtexec.exe).

    • Import and export packages to and from the SQL Server msdb database, the SSIS Package Store, and the file system.

Outline

Chapter 1: SSIS Overview

  • SSIS Overview     
  • Import/Export Wizard   
  • Exporting Data with the Wizard 
  • Common Import Concerns
  • Quality Checking Imported/Exported Data
  • Chapter 1 Lab   
  • Answers to Exercises  

Chapter 2: Working with Solutions and Projects

  • Working with SQL Server Data Tools 
  • Understanding Solutions and Projects
  • Working with the Visual Studio Interface
  • Chapter 2 Lab   
  • Answers to Exercises  

Chapter 3: Basic Control Flow

  • Introduction to the Control Flow   
  • Working with Tasks
  • Understanding Precedence Constraints 
  • Annotating Packages 
  • Grouping Tasks 
  • Package and Task Properties
  • Connection Managers 
  • Favorite Tasks 
  • Chapter 3 Lab   
  • Exercise 1 – Precedence Constraints and Execute SQL Task
  • Answers to Exercises  

Chapter 4: Common Tasks

  • SSIS Tasks in the Common Section
  • Analysis Services Processing
  • Bulk Insert Task 
  • Data Profiling Task 
  • Execute Package Task
  • Execute Process Task
  • Expression Task 
  • File System Task
  • FTP Task 
  • Hadoop Tasks 
  • Script Task Introduction
  • Send Mail Task 
  • Web Service Task 
  • XML Task 
  • Chapter 4 Lab 
  • Answers to Exercises 

Chapter 5: Data Flow Sources and Destinations

  • The Data Flow Task
  • The Data Flow SSIS Toolbox
  • Working with Data Sources
  • SSIS Data Sources
  • Working with Data Destinations
  • SSIS Data Destinations
  • Chapter 5 Lab   
  • Answers to Exercises  

Chapter 6: Data Flow Transformations

  • Transformations Overview
  • Transformations 
  • Configuring Transformations 
  • Chapter 6 Lab   
  • Answers to Exercises  

Chapter 7: Making Packages Dynamic

  • Features for Making Packages Dynamic
  • Package Parameters
  • Project Parameters
  • Variables 
  • SQL Parameters 
  • Expressions in Tasks 
  • Expressions in Connection Managers
  • After Deployment 
  • How it all fits together
  • Chapter 7 Lab   
  • Answers to Exercises  

Chapter 8: Containers

  • Containers 
  • Sequence Containers
  • For Loop Containers
  • Foreach Loop Containers
  • Chapter 8 Lab
  • Answers to Exercises  

Chapter 9: Troubleshooting and Package Reliability

  • Understanding MaximumErrorCount  
  • Breakpoints 
  • Redirecting Error Rows
  • Logging 
  • Event Handlers 
  • Using Checkpoints
  • Transactions 
  • Chapter 9 Lab   
  • Answers to Exercises  

Chapter 10: Deploying to the SSIS Catalog

  • SSIS Deployment Overview
  • The SSIS Catalog 
  • Deploying Projects 
  • Working with Environments
  • Executing Packages in SSMS 
  • Executing packages from the command line
  • Deployment Model Differences
  • Chapter 10 Lab   
  • Answers to Exercises  

Chapter 11: Installing and Administering SSIS

  • Installing SSIS  
  • Upgrading SSIS 
  • Managing the SSIS Catalog
  • Viewing Built-in SSIS Reports
  • Managing SSIS Logging and Operation Histories
  • Automating Package Execution
  • Chapter 11 Lab   
  • Answers to Exercises  

Chapter 12: Securing the SSIS Catalog

  • Principals 
  • Securables
  • Grantable Permissions
  • Granting Permissions
  • Configuring Proxy Accounts 
  • Chapter 12 Lab   
  • Answers to Exercises

 

PreRequisites

Students should be comfortable with SQL Server Management Studio and writing basic queries. Additionally, they should be comfortable opening applications and moving between multiple applications.

Audience

This course is intended for database professionals who have a general familiarity with Microsoft SQL Server and writing basic queries prior to the class. A basic knowledge of programming is beneficial.

$1795.00 List Price

4 Days Course

Class Dates

Request a Date or a Private Class below.


MAX Educ. Savings
Categories: ,
0
Connecting
Please wait...
Send a message

Sorry, we aren't online at the moment. Leave your message and we'll respond to you as soon as we're back in the office!

Your name
* Email
* How can we help?
    Start Chat Now

    Hello and welcome!

    I'm here if you have any questions.

    * Your name
    * How can we help?
    We're online!
    Feedback

    Help us help you better! Feel free to leave us any additional feedback.

    How do you rate our support?
      Loading ...