SQL Server Integration Services - AbsoluteAstronomy.com

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

Microsoft SQL Server

Microsoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...

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

Data migration

Data migration is the process of transferring data between storage types, formats, or computer systems. Data migration is usually performed programmatically to achieve an automated migration, freeing up human resources from tedious tasks...

tasks.

SSIS is a platform for data integration

Data integration

Data integration involves combining data residing in different sources and providing users with a unified view of these data.This process becomes significant in a variety of situations, which include both commercial and scientific domains...

and workflow application

Workflow application

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

s. It features a fast and flexible data warehousing

Data warehouse

In computing, a data warehouse is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.A data warehouse...

tool used for data extraction, transformation, and loading (ETL

Extract, transform, load

Extract, transform and load is a process in database usage and especially in data warehousing that involves:* Extracting data from outside sources* Transforming it to fit operational needs...

). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data

OLAP cube

An OLAP cube is a data structure that allows fast analysis of data. It can also be defined as the capability of manipulating and analyzing data from multiple perspectives...

.

First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services

Data Transformation Services

Data Transformation Services, or DTS, is 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 utilities are called DTS tools...

, 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" and "Enterprise" editions.

Features

The SSIS Import/Export Wizard

Wizard (software)

A software wizard or setup assistant is a user interface type that presents a user with a sequence of dialog boxes that lead the user through a series of well-defined steps. Tasks that are complex, infrequently performed, or unfamiliar may be easier to perform using a 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

Microsoft Visual Studio

Microsoft Visual Studio is an integrated development environment from Microsoft. It is used to develop console and graphical user interface applications along with Windows Forms applications, web sites, web applications, and web services in both native code together with managed code for all...

called the SQL Server Business Intelligence Development Studio

Business Intelligence Development Studio

Business Intelligence Development Studio is the IDE from Microsoft used for developing data analysis and Business Intelligence solutions utilizing the Microsoft SQL Server Analysis Services, Reporting Services and Integration Services...

(BIDS). It allows users to edit SSIS packages using a drag-and-drop user interface. A scripting environment in which to write 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.

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.
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 (which 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.
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.
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.
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

XML

Extensible Markup Language is a set of rules for encoding documents in machine-readable form. It is defined in the XML 1.0 Specification produced by the W3C, and several other related specifications, all gratis open standards....

.

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:

Conditional Split,
Multicast
Union-All, Merge, and Merge Join
Sort
Fuzzy Grouping
Lookup and Fuzzy Lookup
Percentage Sampling and Row Sampling
Copy/Map, Data Conversion, and Derived Column
Aggregation
Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing
Pivot and Unpivot
Slowly Changing Dimension
Script Component
Audit
Cache Transform
Export and Import Column
OLE DB Command
Row Count
Term Extraction
Term Lookup

The Conditional Split transformation is used to speed up the query on the source table 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, again from the command prompt

Command Prompt

Command Prompt is the Microsoft-supplied command-line interpreter on OS/2, Windows CE and on Windows NT-based operating systems...

. 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.

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 .Net assembly

.NET assembly

In the .NET framework, an assembly is a compiled code library used for deployment, versioning, and security. There are two types: process assemblies and library assemblies . A process assembly represents a process that will use classes defined in library assemblies...

DLL

Dynamic-link library

Dynamic-link library , or DLL, is Microsoft's implementation of the shared library concept in the Microsoft Windows and OS/2 operating systems...

s, providing access to virtually any kind of operation permissible by the .Net CLR

Common Language Runtime

The Common Language Runtime is the virtual machine component of Microsoft's .NET framework and is responsible for managing the execution of .NET programs. In a process known as just-in-time compilation, the CLR compiles the intermediate language code known as CIL into the machine instructions...

.

SSIS can be used on all SQL Server 2005/2008/2008 R2 editions except Express and Workgroup.

Troubleshooting

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

External links

The source of this article is wikipedia, the free encyclopedia. The text of this article is licensed under the GFDL.