Microsoft SSIS Real-time Scenarios (Package Design)

Explore the call column transformation in SSIS, creating or replacing columns with expressions on input data. See two demos: add a static column and use variables in the data pipeline.

The lecture shows adding a static column and deriving a new column in the SSIS data flow pipeline, using the derived column transformation to cast and concatenate values.

Learn how to add user and system variables to the data flow, create derived columns with expressions, and merge with input columns using package name and start time.

Explore module 2 of Microsoft SSIS real-time scenarios, executing securely stored procedures. Learn to call stored procedures with or without parameters, returning a scalar value or a data set.

Configure and execute a stored procedure without parameters in SSIS by using a direct input SQL statement, the appropriate connection manager, and no parameter mapping.

Learn to call a stored procedure with parameters in SSIS that returns a scalar value, pass a date, capture the count in an integer variable, and display the result.

Demonstrate calling a stored procedure with parameters in ssis, returning a multi-row result set. Consume the data with a for each loop and script task to format and display results.

Learn how to implement SSIS flexibility with expressions across transformations and adapters to evaluate and set runtime values, including dynamic expressions and data flows.

Explore using precedence constraints and expressions to control SSIS workflow, routing execution at runtime based on random number, selecting paths when number is greater than five or less than five.

Demonstrates creating dynamic SQL statements in SSIS using expressions and variables to build and execute queries that return random salesperson data.

Extract the file name and its part from a full path using SSIS variables and expressions, demonstrating replacing the name with blank and using the right function to isolate filename.

Separate numeric and string values in a column using conditional split and type casting to route data into two data flows, while handling nulls and ignoring failures.

In this scenario, SSIS data flow separates numeric and string values from a mixed column into two streams using a conditional split and type casting.

implement SSIS flexibility by using expressions with variables and default values, evaluate expressions in the expression builder, and validate package results to prevent errors.

Explore two SSIS scenarios using import column and export column transformations to convert files to blobs for database storage and to export files back from blobs.

Import files as binary blobs into the table with a data flow task and import column transformation, using a directory variable and file name mapping.

Scenario 2 demonstrates exporting files back from blob using the export column transformation in a dataflow task, mapping the blob to a full filename.

Extend SSIS capabilities with script tasks and components in C# or VB.NET to implement dataflow and flow logic for retrieving files from directories and loading delimited files to SQL Server.

Zip a folder using a script task to create a compressed file and unzip to an output folder, producing date-stamped vendor folders with the .net compression library.

Script task retrieves a list of specific files from a multilevel directory using a search pattern and passes it to subsequent tasks.

Load a delimited file into a sql server database table using a script task, building a data table from the csv and mapping columns for bulk copy.

Explore scenario 4 in Microsoft SSIS real-time scenarios: implement a script task in C# using WebClient to download a file from http(s) URLs, with error handling and logging.

Convert a JSON file to CSV within a SQL Server Integration Services package using a script task, detailing header creation, data conversion, and logging for database load.

Explore script task validation of a file’s header and trailer in SSIS for real-time scenarios, ensuring the header defines columns and the trailer confirms record counts before loading data.

Extend SSIS functionalities using C# or VB.NET scripts to handle multilevel directory loading, delimited file imports into secure database tables, and header-trailer validation, as showcased in module five.

Explore how to use foreach and for loop containers in SSIS to process collections and repeat tasks, with scenarios that iterate files and date ranges.

Explore SSIS real-time loop container iterating over a collection of files stored in an object, extracting file names, executing scripts, and handling errors with a file system task.

Use a loop container in SSIS to repeat a task for dates between start date and end date, using counter variable to increment, and build and execute an insert command.

Explore four SSIS scenarios for loading delimited flat files—comma separated values and pipe separated values—and raw files into SQL Server database tables using the raw file source adapter.

Load a comma separated values flat file into a destination table using a data flow with a flat file source, column mapping, data type conversion, and a package variable.

Configure a flat file connection manager for a tab-delimited file, set the delimiter, map columns, and load records into the destination table via a data flow task.

Load a pipe-delimited PSV file into a database table using a flat file connection manager and a data flow task, with header-aware column mapping and a PSV-specific delimiter.

Learn to load a proprietary flat file into a database using SSIS raw file source and destination, with built-in data types and no parsing or transformation.

Explore module eight data loading patterns, including incremental loading and slowly changing dimension type two, by building packages that transform, stage, and load data with data flow and control flow.

Scenario 1 demonstrates extracting numbers from alphanumeric values in a data column using a SSIS dataflow task, then loading the numeric results to the destination.

Explore a generic ETL workflow in SSIS: extract data from any source, apply data conversion, sort and deduplicate, then map and load into any destination.

Truncate the destination table explicitly, then perform a full load every time in SSIS, using a data flow with conversions and mappings to the destination.

Merge data from multiple sources, including database and flat file, using sort and merge join transformations and left outer joins, then load into the destination table.

Merge data from multiple sources, including a data database and a flat file, and load the unified data into the destination using SSIS. Transformations replace nulls to handle missing values.

Pivot the data into a metrics form by turning years 2019 and 2020 into columns, summing sales amount by salesperson, and loading the results into the destination table.

Explore scenario 5: pivoting data with the SSIS pivot transformation to load pivoted sales data into a destination table, with aggregation by year and personality.

Explore a custom slowly changing dimension type 2 implementation in SSIS, maintaining history with started and end dates and a current flag, via lookups, updates, and union all.

Implement incremental data load in a single package using a framework with util tracker, switching between full and incremental loads via precedence constraints and SSIS expressions, using ADO.NET without CDC.

Implement incremental/delta data load with an ADO.NET connection using a custom approach, toggling between incremental and full loads via stored procedures and a package tracker.

Implement incremental data load with an OLE DB connection in SSIS, using dynamic SQL and control flow expressions to switch between full and delta loads based on a time column.

Import data from SQL Server tables into a flat file, applying transformation with a script component in a data flow task, mapping columns and using a delimited flat file destination.

Learn to implement package restart in SSIS with checkpoints, and use the Execute Process Task to run third-party scripts or custom executables.

learn how to implement checkpoints in an SSIS package to restart from the failed task, preserving progress via a checkpoint file and settings like filename and safety checkpoints.

Execute Python and R scripts in an SSIS package using the execute process task, providing the interpreter, script file, and arguments, and handling standard input, output, and error.