Mastering SQL Server 2016 Integration Services (SSIS)-Part 1

Learn to migrate data using the Import-Export Wizard in SQL Server, exporting the department data from Adventure Works 2014 to a flat file with SSIS package creation.

Invoke import export wizard from SQL Server Management Studio to export department table to an Excel file, using Windows authentication and Adventure Works 2014 as source, and save SSIS package.

Invoke import export wizard from Visual Studio to generate ssis package exporting SQL Server data to Excel via dtsx, then run in 32-bit mode to avoid 64-bit driver issues.

Learn to manually build an SSIS package to export data from SQL Server to a flat file CSV, using control flow, data flow tasks, and destination mappings.

Discover how the character map transformation standardizes data in SSIS, converting department names to uppercase and group names to lowercase during a dataflow from SQL Server to a flat file.

Learn how the copy column transformation duplicates source columns in SSIS, creating copy outputs from an OLE DB source to a flat file destination.

Explore the derived column transformation in SSIS to create a full name by concatenating first, middle, and last names as a new column and write to a flat file.

Learn how to apply the percentage sampling transformation in an SSIS data flow, copying 20% of records from a source to two Excel destinations as selected and unselected outputs.

Demonstrate using the row sampling transformation to select a fixed number of records randomly in SSIS, then write the selected rows to a flat file.

Master the sort transformation in SSIS by sorting data on country region code, state province code, and state name, from an OLE DB source to a CSV destination.

Use the union all transformation in SSIS to combine records from multiple sources into a single destination, demonstrated with two sources and a flat file output.

Learn how to use the multicast transformation in SSIS to distribute source data to multiple destinations. The demo shows routing the same input to flat files and Excel via multicast.

Explore how to use the conditional split transformation in SSIS to route the Sales Order Detail records to multiple Excel destinations based on line total thresholds.

Group by order id with the aggregate transformation to sum line totals and compute min, max, average, and count from the Sales Order Details table in Adventure Works.

Master the audit transformation in SSIS by logging package name, execution start time, machine and user details, and task name, then exporting audit data to Excel for execution tracking.

Master exporting a column from a SQL Server table to the file system with the SSIS export column transformation, including data conversion to DT_TEXT and specifying a file path.

Explore the Import Column Transformation to move images from the file system into a SQL Server table, using a flat file source, image path mapping, and an OLEDB destination.

Explore the merge transformation in SSIS to combine sorted data from two sources into one destination. Compare it with union all and configure sources, sorting, and an Excel destination.

Master the merge join transformation in SSIS to combine two sorted sources on order id and output the joined data to an Excel destination using inner or outer joins.

Explore how to use the look up transformation by joining the sales order details with the production.product table on product id to retrieve name, number, color, and list price.

Discover how to use the cache transform in SSIS to preload a lookup table into memory, speeding lookups and reducing SQL Server reads.

Explore the fuzzy lookup transformation in SSIS for handling non-matching lookup data. Compare it with the standard lookup, mapping occupation titles to ids via pattern matching.

Use fuzzy grouping in SSIS to remove duplicates from a flat file of occupation titles, using a tuned similarity threshold to create clean titles in the occupation table.

Use the RowCount transformation in SSIS to count rows in a data flow, store the count in a package-wide variable, and display it via a script task for conditional decisions.

Learn to use the OLEDB Command transformation in SSIS to perform row-wise insert, update, and delete operations, mapping parameters and applying in-pipeline data manipulations.

Explore pivot transformations in SSIS by turning normalized data into a wide, cross-tab layout using pivot key, set key, and pivot value, with a flat file source and data viewer.

This lecture demonstrates the unpivot transformation in SSIS, converting a flat file with day columns into normalized day of week and expenses rows using a dataflow task and multicast.

Explore the term extraction transformation in SSIS by building a dataflow from a flat file to a SQL Server table, identifying and counting duplicated terms.

Configure the term Look-Up transformation to map descriptions from the flat-file source to term extraction results. Display frequency by sentence and populate the destination table with term and sentence.

Master the bulk insert task in SSIS control flow to load records from employees.csv into the employees table, configuring first/last rows, batch size, delimiters, and constraints.

Learn to organize SSIS packages with sequence containers to group bulk insert, file upload, and database object transfer tasks for readability and maintainability.

Master the execute process task in SSIS to run exe or batch files, pass arguments, set working directory and window style, and copy folders with xcopy including subdirectories.

Learn to use the execute package task to call a child package from a parent package, using project or external references, and verify execution with message boxes.

Discover how to pass parameters from a parent SSIS package to a child package using parameter bindings, create and map parent and child parameters, and verify values in script task.

Mastering SQL Server 2016 Integration Services (SSIS) part 1 demonstrates building and using the filesystem task to copy files and directories, create folders, set attributes, and parameterize paths with variables.

Learn to use the SSIS web service task to call a web service, configure HTTP connections and WSDL, pass input parameters, and capture XML results from add and subtract methods.

Demonstrates using the XML task in SSIS to query inventory.xml with XPath, perform valuation and node list operations, extract author names, and merge XML files into a new destination.

Learn to use the data profiling task in SSIS to create graphical reports from the Adventure Works person table, analyzing name length and title nulls with the data profile viewer.