Microsoft SQL Server Integration Services (SSIS) is a component of the

Microsoft SQL Server Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which ...

database software that can be used to perform a broad range of

data migration Data migration is the process of selecting, preparing, extracting, and transforming data and permanently transferring it from one computer storage system to another. Additionally, the validation of migrated data for completeness and the decommis ...

tasks. SSIS is a platform for

data integration Data integration involves combining data residing in different sources and providing users with a unified view of them. This process becomes significant in a variety of situations, which include both commercial (such as when two similar companies ...

and

workflow application A workflow application is a software application which automates, to at least some degree, a process or processes. The processes are usually business-related but can be any process that requires a series of steps to be automated via software. Some ...

s. It features a

data warehousing In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for reporting and data analysis and is considered a core component of business intelligence. DWs are central repositories of integr ...

tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data. First released with Microsoft SQL Server 2005, SSIS replaced

Data Transformation Services Data Transformation Services (DTS) is a Microsoft database tool with a set of objects and utilities to allow the automation of extract, transform and load operations to or from a database. The objects are DTS packages and their components, and the ...

, which had been a feature of SQL Server since Version 7.0. Unlike DTS, which was included in all versions, SSIS is only available in the "Standard", "Business Intelligence" and "Enterprise" editions. With Microsoft "Visual Studio Dev Essentials" it is now possible to use SSIS with Visual Studio 2017 free of cost so long as it is for development and learning purposes only.

Features

The SSIS Import/Export Wizard lets the user create packages that move data from a single data source to a destination with no transformations. The Wizard can quickly move data from a variety of source types to a variety of destination types, including text files and other SQL Server instances. Developers tasked with creating or maintaining SSIS packages use a visual development tool based on Microsoft Visual Studio called the SQL Server Business Intelligence Development Studio (BIDS). It allows users to edit SSIS packages using a drag-and-drop user interface. A scripting environment for writing programming code is also available in the tool. A package holds a variety of elements that define a workflow. Upon package execution, the tool provides color-coded real-time monitoring. (Note: In more recent versions MS SQL Server, BIDS has been replaced with "SQL Server Data Tools - Business Intelligence" (SSDT-BI).) ; Connections: A connection includes the information necessary to connect to a particular data source. Tasks can reference the connection by its name, 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 (SQL Server 2012 Integration Services): Parameters allow you to assign values to properties within packages at the time of package execution. You can have project parameters and package parameters. In general, if you are deploying a package using the package deployment model, you should use configurations instead of parameters. ; Precedence constraints: Tasks are linked by precedence constraints. The precedence constraint preceding a particular task must be met before that task executes. The run time supports executing tasks in parallel, if their precedence constraints so allow. Constraints may otherwise allow different paths of execution depending on the success or failure of other tasks. Together with the tasks, precedence constraints comprise the workflow of the package. ; 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. The data transformation task actually copies data; it implements the ETL features of the product ; Variables: Tasks may reference variables to store results, make decisions, or affect their configuration. A package may be saved to a file or to a store with a hierarchical namespace within a SQL Server instance. In either case, the package content is persisted in

XML Extensible Markup Language (XML) is a markup language and file format for storing, transmitting, and reconstructing arbitrary data. It defines a set of rules for encoding documents in a format that is both human-readable and machine-readable ...

. Once completed, the designer also allows the user to start the package's execution. Once started, the package may be readily debugged or monitored.

Features of the data flow task

SSIS provides the following built-in transformations: * Aggregation * Audit * Cache Transform * Copy/Map * Data Conversion * Data Mining Model Training * Data Mining Query * Dimension Processing * Derived Column * Export & Import Column * For loop Container * Foreach Loop Container * ( Fuzzy) Lookup * Fuzzy Grouping * OLE DB Command * Partition Processing * Percentage Sampling *

Pivot Pivot may refer to: *Pivot, the point of rotation in a lever system *More generally, the center point of any rotational system *Pivot joint, a kind of joint between bones in the body *Pivot turn, a dance move Companies *Incitec Pivot, an Austra ...

* Unpivot * Row Count * Row Sampling * Row Sampling Transformation * Script Component *

Slowly Changing Dimension A slowly changing dimension (SCD) in data management and data warehousing is a dimension which contains relatively static data which can change slowly but unpredictably, rather than according to a regular schedule. Some examples of typical slowly ...

* Term extraction * Term Lookup The Conditional Split transformation is used to conditionally route rows to other transformation objects based on a particular condition. It is similar to the " if … else" construct in the C language.

Other included tools

Aside from the Import/Export Wizard and the designer, the product includes a few other notable tools. DTEXEC executes a package from the command line wherever it may be stored. Before running the package, the tool may be instructed to apply configuration information, which will allow the same package to be reused with slightly different parameters, including different connection strings for its endpoints. DTUTIL provides the ability to manage packages from the

command prompt Command Prompt, also known as cmd.exe or cmd, is the default command-line interpreter for the OS/2, eComStation, ArcaOS, Microsoft Windows (Windows NT family and Windows CE family), and ReactOS operating systems. On Windows CE .NET 4.2, W ...

. The tool can copy or move a package from a file into the server store, or back out again. Among a few other sundry functions, it can be used to delete, rename, encrypt, or decrypt packages. The Bulk Copy Program (BCP), is a command-line tool used to import or export data against a

, or Sybase database.

Extensibility and programmability

Users may write code to define their own connection objects, log providers, transforms, and tasks. SSIS features a programmable object model that allows developers to write their own hosts for package execution. Such a host can respond to events, start and stop packages, and so on. The object model also allows developers to create, store, and load packages, as well as create, destroy, and modify any of the contained objects. Within limits, SSIS packages can load and call CLI assembly DLLs, providing access to virtually any kind of operation permissible by the .NET

CLR CLR may refer to: * Calcium Lime Rust, a household cleaning-product * California Law Review, a publication by the UC Berkeley School of Law * Tube_bending, Centerline Radius, a term in the tubing industry used to describe the radius of a bend * Cen ...

. SSIS can be used on all SQL Server 2005,

2008 File:2008 Events Collage.png, From left, clockwise: Lehman Brothers went bankrupt following the Subprime mortgage crisis; Cyclone Nargis killed more than 138,000 in Myanmar; A scene from the opening ceremony of the 2008 Summer Olympics in Beijing; ...

, 2008 R2,

2012 File:2012 Events Collage V3.png, From left, clockwise: The passenger cruise ship Costa Concordia lies capsized after the Costa Concordia disaster; Damage to Casino Pier in Seaside Heights, New Jersey as a result of Hurricane Sandy; People gat ...

2014 File:2014 Events Collage.png, From top left, clockwise: Stocking up supplies and personal protective equipment (PPE) for the Western African Ebola virus epidemic; Citizens examining the ruins after the Chibok schoolgirls kidnapping; Bundles of wat ...

and

2016 File:2016 Events Collage.png, From top left, clockwise: Bombed-out buildings in Ankara following the 2016 Turkish coup d'état attempt; the Impeachment of Dilma Rousseff, impeachment trial of Brazilian President Dilma Rousseff; Damaged houses duri ...

editions except Express and Workgroup.

References

{{Reflist, 2

External links