SQL Server Administration: (SQL Server Integration Services)

Explore sql server integration services and its role in ETL for reporting services, with guidance on installing sql 2016 and creating packages to transform and export data for reports.

Rafael guides you through installing SQL Server 2016, setting up reporting services, and installing data tools on a Windows 10 environment, with prerequisites and configuration steps explained.

Install SQL Server 2016 on a Windows 10 desktop, verify prerequisites, and configure SQL Server Data Tools and reporting services to enable SSRS reporting capabilities.

Corrects guidance by advising to download the community free version of Visual Studio instead of the enterprise trial, as it provides the same application and reporting capabilities for the course.

Install SQL Server 2016, reboot, then download and install SQL Server Management Studio and SQL Server Data Tools with Visual Studio 2015 to enable SSRS reporting services.

Download and restore the AdventureWorks 2016 sample database, connect to the SQL Server 2016 instance, and explore the reporting services configuration manager and Visual Studio for building reports.

Learn how SSIS, the SQL Server Integration Services platform, enables data integration and ETL by creating packages that move data between heterogeneous sources and SQL Server.

Explore how to export data from one SQL Server to another using the SSIS import-export wizard, covering data migration, mapping, and the behind-the-scenes SSIS package.

Learn to export data from SQL to Excel using SSIS and the import/export wizard, map data types, and manage automatic data conversion in a heterogeneous environment.

Learn to use Visual Studio to create SQL Server Integration Services packages for migrating data between sources and destinations. Explore control flow and data flow, connection managers, and ETL concepts.

Learn to create a ssis package in sql server integration services that migrates data between sql databases using ole db connection managers in visual studio, with annotations and mappings.

Learn to prevent duplicates by truncating the department table with an execute SQL task in control flow, using a precedence constraint to run it before data flow.

Learn to export data from SQL to a flat file using an SSIS package, including setting up a mountains table, mapping columns, and configuring a delimited flat file destination.

Explore using ftp control flow to download and upload files to a local directory. Configure an ftp connection, select remote paths, and receive files into a data folder.

Learn to split the continents dataset with a conditional split transformation in an SSIS package, routing Africa, Asia, Europe, and others to separate destinations for data warehousing.

Truncate the destination tables before the conditional split, using four execute SQL tasks to truncate Europe, Asia, Africa, and other tables, then run the package.

Explore how the derived column transformation creates new columns from existing data, including concatenating names and computing age from birth dates, within an SSIS package.

Use the character map transformation in SSIS to convert a text column to upper or lower case, creating a new column, and verify results with a data viewer.

Combine two sorted data sets into one table with the merge transformation; inputs must share the same column count and data types. Merge Asia and Europe sources in a package.

Explore union all in SSIS to merge data from more than two sources without sorting. Compare it with merge, learn when to use derived columns and downstream tasks.

Use the multicast transformation in SQL Server Integration Services to duplicate data and distribute it to multiple destinations, such as a flat file and a SQL table, across heterogeneous environments.

Use the aggregate transformation in SSIS to group by vendor ID and sum total due, illustrating practical production data aggregation.

Use the term extraction transformation in SSIS to pull nouns from text, output terms and scores, using the English dictionary and frequency thresholds to refine results.

Discover how the audit transformation in SSIS adds execution time, machine name, username, and other columns to tables. Learn how to enable, map, and view these columns.

Discover how the ssis data conversion task acts as an intermediary between source and destination data types, converting sql data to excel compatible formats for successful loading.

Learn to back up a database in a control flow with the backup database task, configure a connection, select databases, and set a disk destination with compression.

Demonstrates how the for each loop container automates importing multiple text files from a folder into a sql table using a data flow task.

create a robust ssis package using a for each loop to read multiple text files and load data into a sql table, with variables and data flow tasks.

Learn to automate SSIS packages with a SQL Server Agent job, using a for each container to process the data flow, schedule execution, and monitor updated product data.