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

Load non-relational data from csv files into the Adventure Works 2017 relational database, mapping east and west data to business entity, sales.store, person.address, and business entity address.

Create an SSIS package in Visual Studio 2019 to load CSV data into Adventure Works relational tables, using flat file connections, sort/merge join, union all, and a stored procedure insert.

Insert 696 records into the sales.store and person.address tables through multicast, using a state province lookup to populate IDs and store results in a raw file destination for later steps.

Learn how to insert data into the business entity address using a raw file source, retrieve address type id, and perform a lookup with cache in SSIS.

Run the SSIS package to load 695 records from excel files into tables: business entity, address, sales store, and business entity address, then verify results with queries and cleanup script.

Explore how SSIS packages behave without transaction support, demonstrating how partial data can persist across multiple tables and how to test rollback versus commit under transaction options.

Learn to execute an SSIS package with transaction support using MSDTC, with package-level and activity-level transaction options, and understand commit and rollback behavior across related tables.

Master the checkpoints concept in SSIS to restart from the last failed point. Learn to use file cache to preserve cache between runs and avoid cache-related failures.

Create package level parameters for address type name, raw file path, and stored procedure names to make the SSIS package dynamic when deployed to the integration services catalog.

Deploy the SSIS project from Visual Studio to the SQL Server Integration Services Catalog (SSIS DB) via the deployment wizard, using a catalog folder such as Sales Store Demo ETL.

Deploy SSIS projects via ISPAC files to the Integration Services Catalog, using project deployment or package deployment, and deploy with the Integration Services Deployment Wizard to a server or folder.

Deploy an SSIS project from one SQL Server Integration Services catalog to another using the deployment wizard, highlighting project and package deployment from QA to production.

Compare project deployment and package deployment in SSIS, and learn to deploy individual packages to the SSIS catalog via Visual Studio or IS Deployment Wizard, noting Azure Data Factory limitations.

Execute a deployed SSIS package from the Integration Services Catalog, adjust parameters and connection settings, redeploy after fixes, and verify success with a review report and data checks.

Execute an SSIS package from the SSIS catalog by creating and running a SQL Server Agent job, configuring parameters and permissions, and monitoring with the all executions report.

Shows how to create development and QA environments, map environment variables to package parameters, and configure an SSIS job to run with a chosen environment.

Publish your SSIS packages to the Integration Services Catalog, creating data feeds. Use the data feed publishing wizard to configure Data Streaming Destination, OLEDB provider, linked server, and SQL view.

Publish the output of an SSIS DTSX package as a data feed by manually creating a linked server, configuring SSISOLEDB, and building a view via open query.

Demonstrates using the OData Source Component in SSIS to consume SharePoint Online restful service data feeds, filter and sort OData collections, and load results into Excel.

Demonstrates reading data from multiple Excel sheets in an ssis package, then updating production.product with revised standard cost, list price, and stock levels from the sample data.

Install the access database engine distributable (32-bit) to enable reading Excel sheets from a single file, then configure the connection manager in SSDT to build your first SSIS package.

Develop an SSIS package to read data from multiple sheets of a single Excel file using a for each loop, data flow with Excel source, lookup, and OLEDB command updates.

Use a temporary table to optimize SSIS data loads from multiple sheets, loading via a data flow into temp product and updating production.product in one go.

Learn to implement slowly changing dimension type 2 in SSIS by tracking product list price history from Excel to SQL Server, updating end dates and inserting new records.

This SSIS demonstration shows slowly changing dimension type 2 in action, updating a product price and ending the previous record with the current date, while creating a new historical record.

Install VMware Workstation Player to run the Hadoop virtual machine, then use SSIS 2016 with the Hadoop Connection Manager to transfer files to and from the Hadoop file system.

Download Hadoop 2.5 and Hortonworks HDP, import the 11.1 GB OVA into VMware Workstation, and allocate 8–12 GB RAM to the VM for Hadoop with SSIS.

Access the Hadoop VM and Files view from SSIS, prepare the demo folder, and learn how to upload, download, and copy files within Hadoop.

Learn to connect SSIS to a Hadoop filesystem and copy files to, from, or within HDFS using the Hadoop Filesystem Task and a Hadoop connection setup.

Copy files from HDFS to your local system using the copy from HDFS task in SSIS, demonstrating the download of Employee.csv from the Hadoop filesystem to the desktop.

Demonstrates copying files within the Hadoop filesystem using the SSIS Hadoop filesystem task, duplicating Employee.csv to Employee2.csv in the demo folder and tracking with a Job ID.

Mastering sql server 2016 integration services part 2 demonstrates using the hdfs file source and hdfs file destination within a dataflow task to copy files between hdfs and local destinations.

Upload a flat file to the Hadoop file system using SSIS HDFS File Source and HDFS File Destination within a data flow task to move Emp.csv into the demo folder.

Download files from the Hadoop file system to your local system using SSIS with HDFS file source and HDFS file destination transformations.

Demonstrates working with the Hadoop Hive task in SSIS by copying a local csv to HDFS, dropping and creating a Hive table, and loading data into the table.

Explore how to use the Hadoop Pig task to run Pig scripts, load departments.csv from demo2, and store results into samples2/demos2 with a new departments.csv.