SQL Server Integration Services: Difference between revisions

Revision as of 13:07, 3 February 2020

Revision as of 06:12, 1 February 2021

Line 16:

SSIS is a platform for [[data integration]] and [[workflow application]]s. It features a [[data warehouse|data warehousing]] tool used for data [[Extract, transform, load|extraction, transformation, and loading (ETL)]]. The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional [[OLAP cube|cube data]].

First released with Microsoft SQL Server 2005, SSIS replaced [[Data Transformation Services]], 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.<ref>{{cite web | title = Features Supported by the Editions of SQL Server 2014 | url = http://msdn.microsoft.com/en-gb/library/cc645993.aspx#SSIS|website=msdn.microsoft.com | publisher = Microsoft Developer Network | accessdateaccess-date = 20 August 2014}}</ref> 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==

Line 35:

===Features of the data flow task===

SSIS provides the following built-in transformations:<ref>{{cite web | title = Integration Services Transformations | url = http://msdn.microsoft.com/en-us/library/ms141713.aspx|work=Microsoft Developer Network | publisher = Microsoft | accessdateaccess-date = 22 March 2013}}</ref>

{| class="wikitable"

|-

Line 42:

| Audit || For loop Container || Row Count

|-

| Cache Transform<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/bb895264 | title = Cache Transform|work=Microsoft Developer Network | publisher = Microsoft | accessdateaccess-date = 22 March 2013}}</ref> || Foreach Loop Container || Row Sampling

|-

| Copy/Map || ([[Fuzzy_logic|Fuzzy]]) Lookup || Script Component

Line 52:

| Data Mining Query || Partition Processing || Term Lookup

|-

| Derived Column || Percentage Sampling<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms139864.aspx | title = Percentage Sampling Transformation|work=Microsoft Developer Network | publisher = Microsoft | accessdateaccess-date = 22 March 2013}}</ref> || [[Pivot table|Unpivot]]

|-

| Dimension Processing || Row Sampling Transformation ||

Line 62:

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.<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms162810%28v=sql.105%29.aspx | title = dtexec Utility (SSIS Tool) | accessdateaccess-date = 2013-03-24 | publisher = Microsoft|work=Microsoft Developer Network}}</ref><ref>{{cite web | url = http://www.databasejournal.com/features/mssql/article.php/3897236/Executing-SSIS-Packages-Using-DTExec.htm | title = Executing SSIS Packages Using DTExec | accessdateaccess-date = 2013-03-24|date=2010-08-09 | publisher = Database Journal|author=Marcin Policht}}</ref> 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 [[Windows command prompt|command prompt]].<ref>{{cite web | url = http://msdn.microsoft.com/en-us/library/ms162820%28v=sql.105%29.aspx | title = dtutil Utility (SSIS Tool) | accessdateaccess-date = 2013-03-24 | publisher = Microsoft|work=Microsoft Developer Network}}</ref><ref>{{cite web | url = http://www.databasejournal.com/features/mssql/article.php/3734096/Using-dtutil-to-copy-SSIS-packages-stored-in-SQL-Server.htm | title = Using dtutil to copy SSIS packages stored in SQL Server | accessdateaccess-date = 2013-03-24|date=2008-03-20 | publisher = Database Journal|author=Yan Pan}}</ref> 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.<ref>{{cite web | title = Developing a Custom Data Flow Component | url = http://msdn.microsoft.com/en-us/library/ms136078.aspx|work=Microsoft Developer Network | publisher = Microsoft | accessdateaccess-date = 22 March 2013}}</ref><ref>{{cite web | title = Developing a Custom Task | url = http://msdn.microsoft.com/en-us/library/ms135965.aspx|work=Microsoft Developer Network | publisher = Microsoft | accessdateaccess-date = 22 March 2013}}</ref>

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.