SQL Server integration services (SSIS)

 SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of Data Migration tasks.

It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL).

Used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

Features of SSIS

Connections – A connection includes the information necessary to connect to a particular data source. Allowing the details of the connection to be changed or configured at run time.

Event handlers – A workflow can be designed for a number of events in the different scopes where they might occur. In this way, tasks may be executed in response to happenings within the package – such as cleaning up after errors.

Parameters – Parameters allow you to assign values to properties within packages at the time of package execution.

Precedence constraints – Precedence constraints link executables, containers, and tasks in packages in a control flow, and specify conditions that determine whether executables run. An executable can be a For Loop, Foreach Loop, or Sequence container; a task; or an event handler.

Tasks – A task is an atomic work unit that performs some action. There are a couple of dozen tasks that ship in the box, ranging from the file system task (that can copy or move files) to the data transformation task.

Variables – Tasks may reference variables to store results, make decisions, or affect their configuration.

SSIS – Design

Control Flow – You can create the control flow in a package by using the Control Flow tab in SSIS Designer. When the Control Flow tab is active, the Toolbox lists the tasks and containers that you can add to the control flow.

For more reference: https://msdn.microsoft.com/en-us/library/ms137681.aspx

Data Flow – Adding a Data Flow task to the control flow of a package is the first step in implementing a data flow in a package. A package can include multiple Data Flow tasks, each with its own data flow. Data Flow is doing the ETL part in SSIS package.

For more reference: https://msdn.microsoft.com/en-us/library/ms140080.aspx

Parameters – Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. Package parameters allow you to modify package execution without having to edit and redeploy the package.

For more reference: https://msdn.microsoft.com/en-us/library/hh213214.aspx

Event Handlers – At run time, containers and tasks raise events. You can create custom event handlers that respond to these events by running a workflow when the event is raised. For example, you can create an event handler that sends an e-mail message when a task fails.

For more reference: https://msdn.microsoft.com/en-us/library/ms139744.aspx

Package Explorer – Use the Package Explorer tab of SSIS Designer to see a hierarchical view of all of the elements in a package: configurations, connections, event handlers, executable objects such as tasks and containers, log providers, precedence constraints, and variables. If a package contains a Data Flow task, the Package Explorer tab includes a node that contains a hierarchical view of the data flow components.

For more reference: https://msdn.microsoft.com/en-us/library/ms190114.aspx

SSIS Toolbox

Favorites

Data Flow Task – The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and lets the user transform, clean, and modify data as it is moved. Addition of a Data Flow task to a package control flow makes it possible for the package to extract, transform, and load data.

For more reference: https://msdn.microsoft.com/en-us/library/ms141122.aspx

Execute SQL Task – The Execute SQL task runs SQL statements or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially.

For more reference: https://msdn.microsoft.com/en-us/library/ms141003.aspx

Common

Analysis Service Processing Task – The Analysis Services Processing task processes Analysis Services objects such as tabular models, cubes, dimensions, and mining models.

For more reference: https://msdn.microsoft.com/en-us/library/ms141779.aspx

Bulk Insert Task – The Bulk Insert task provides an efficient way to copy large amounts of data into a SQL Server table or view. For example, suppose your company stores its million-row product list on a mainframe system, but the company’s e-commerce system uses SQL Server to populate Web pages. You must update the SQL Server product table nightly with the master product list from the mainframe. To update the table, you save the product list in a tab-delimited format and use the Bulk Insert task to copy the data directly into the SQL Server table.

To ensure high-speed data copying, transformations cannot be performed on the data while it is moving from the source file to the table or view.

For more reference: https://msdn.microsoft.com/en-us/library/ms141239.aspx

Data Profiling Task – The Data Profiling task computes various profiles that help you become familiar with a data source and identify problems in the data that have to be fixed.

You can use the Data Profiling task inside an Integration Services package to profile data that is stored in SQL Server and to identify potential problems with data quality.

For more reference: https://msdn.microsoft.com/en-us/library/bb895263.aspx

Execute Package Task – The Execute Package task extends the enterprise capabilities of Integration Services by letting packages run other packages as part of a workflow.

For more reference: https://msdn.microsoft.com/en-us/library/ms137609.aspx

Execute Process Task – The Execute Process task runs an application or batch file as part of a SQL Server Integration Services package workflow. Although you can use the Execute Process task to open any standard application, such as Microsoft Excel or Microsoft Word, you typically use it to run business applications or batch files that work against a data source.

For more reference: https://msdn.microsoft.com/en-us/library/ms141166.aspx

Expression Task – The Expression Task creates and evaluates expressions that set variable values at runtime, using the Expression Builder. When you edit the task, the Expression Builder is launched.

For more reference: https://msdn.microsoft.com/en-us/library/hh213137.aspx

File System Task – The File System task performs operations on files and directories in the file system. For example, by using the File System task, a package can create, move, or delete directories and files. You can also use the File System task to set attributes on files and directories. For example, the File System task can make files hidden or read-only.

For more reference: https://msdn.microsoft.com/en-us/library/ms140185.aspx

FTP Task – The FTP task downloads and uploads data files and manages directories on servers. For example, a package can download data files from a remote server or an Internet location as part of an Integration Services package workflow.

For more reference: https://msdn.microsoft.com/en-us/library/ms137656.aspx

Script Task – The Script task provides code to perform functions that are not available in the built-in tasks and transformations that SQL Server Integration Services provides.

For more reference: https://msdn.microsoft.com/en-us/library/ms141752.aspx

Send Mail Task – The Send Mail task sends an e-mail message. By using the Send Mail task, a package can send messages if tasks in the package workflow succeed or fail, or send messages in response to an event that the package raises at run time. For example, the task can notify a database administrator about the success or failure of the Backup Database task.

For more reference: https://msdn.microsoft.com/en-us/library/ms142165.aspx

Web Service Task – The Web Service task executes a Web service method.

For more reference: https://msdn.microsoft.com/en-us/library/ms140114.aspx

XML Task – The XML task is used to work with XML data. Using this task, a package can retrieve XML documents, apply operations to the documents by using Extensible Stylesheet Language Transformations (XSLT) style sheets and XPath expressions, merge multiple documents, or validate, compare, and save the updated documents to files and variables.

For more reference: https://msdn.microsoft.com/en-us/library/ms141055.aspx