SQL Server Integration Services training (SSIS) is a component of SQL Server that facilitates data extract, transformation, and loading (ETL).
SSIS can also be used to automate updates to databases and SQL server cubes.
SSIS can interact with a wide range of data formats, including all major modern databases, Excel, delimited text files, fixed-width text files, and XML.
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 catalog
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
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
Bulletproofing a package with package environments
Package restartability with checkpoint files
Simplifying deployments
Deploying packages to the SSISDB
Running packages from SQL Server
Leveraging package parameters
Configuring connection managers
Adding data flow tasks to packages
Reviewing progress with data viewers
Assembling tasks to perform complex data migrations
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