SQL Server Integration Services: Difference between revisions

Revision as of 16:46, 13 May 2021

Latest revision as of 00:11, 19 March 2025

(27 intermediate revisions by 13 users not shown)

Line 13:

| website = {{URL|https://technet.microsoft.com/en-us/library/ms141026.aspx}}

}}

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

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

Line 22:

The SSIS Import/Export [[Wizard (software)|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).<ref>{{Cite web|url=http://sqlmag.com/sql-server-2014/q-where-business-intelligence-development-studio-bids-sql-server-2014|title = IT Pro}}</ref><ref>{{Cite web|url=http://blogs.msdn.com/b/analysisservices/archive/2013/03/06/sql-server-data-tools-business-intelligence-for-visual-studio-2012-released-online.aspx|title=DevBlogs}}</ref>)

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

Line 30:

; 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 [[Extract, transform, load|ETL]] features of the product

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

; Streamlined Data Integration: SSIS offers a visual interface and pre-built components to simplify the process of extracting data from various sources, transforming it, and loading it into target destinations. This reduces development time and effort compared to writing custom code. <ref>{{cite web | url = https://techunwrapped.com/ssis-816-definition/ | title = SSIS-816: Definition, History, and ETL Capabilities |work=Techunwrapped | date = 6 September 2024 | publisher = Techunwrapped.com}}</ref>

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

Line 36 ⟶ 38:

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

[[File:SSIS workflow.png|thumb|An example of a SQL Server Integration Services (SSIS) workflow, by visually programming the steps needed to transform data from source to destination.|alt=Diagram with seven nodes. 1: OLE DB Source (Source Table), flows to 3: Sort Source Table, flows to 5: Merge Join. Another flow goes from 2: OLE DB Source (Dest Table), flows to 4: Sort Dest Table, flows to 5: Merge Join. From 5: Merge Join, the flow goes to 6: Conditional Split, flows to 7: OLE DB Destination.]]

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 | access-date = 22 March 2013}}</ref>

{| class="wikitable"

|-

| Aggregation || Export & Import Column || [[Pivot table|Pivot]]

|-

| 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 | access-date = 22 March 2013}}</ref> || Foreach Loop Container || Row Sampling

|-

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

|-

| Data Conversion || [[Fuzzy_logic|Fuzzy]] Grouping || [[Slowly Changing Dimension]]

|-

| Data Mining Model Training || OLE DB Command || [[term extraction|Term Extraction]]

|-

| 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 | access-date = 22 March 2013}}</ref> || [[Pivot table|Unpivot]]

|-

| Dimension Processing || Row Sampling Transformation ||

|}

{{Div col|colwidth=18em}}

The Conditional Split transformation is used to conditionally route rows to other transformation objects based on a particular condition. It is similar to the "<code>if … else</code>" construct in the C language.

* Aggregation

* Audit

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

* Copy/Map

* Data conversion

* Data mining model training

* Data mining query

* Dimension processing

* [[Derived column]]

* Export and import column

* For loop container

* Foreach loop container

* ([[Fuzzy_logic|Fuzzy]]) lookup

* [[Fuzzy_logic|Fuzzy]] grouping

* OLE DB command

* Partition processing

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

* [[Pivot table|Pivot]]

* [[Pivot table|Unpivot]]

* Row count

* Row sampling

* Row sampling transformation

* Script component

* [[Slowly changing dimension]]

* [[Term extraction]]

* Term Lookup

{{div col end}}

The Conditionalconditional Splitsplit transformation is used to conditionally route rows to other transformation objects based on a particular condition. It is similar to the "<code>[[conditional (programming)|if … else]]</code>" construct in the C language.

=={{Anchor|A-tools}}Other included tools==

Line 67 ⟶ 79:

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

The Bulk Copy Program (BCP), is a [[Console application|command-line tool]] used to import or export data against a [[Microsoft SQL Server]],<ref name="FIPADA">{{cite book|last1=Rankins|first1=Ray|last2=Bertucci|first2=Paul|last3=Jennsen|first3=Paul|date=December 2002|title=Microsoft SQL Server 2000 Unleashed|edition=2|publisher=Sams|___location=Indiana|isbn=9780672324673|oclc=474621100|pppages=86–87}}</ref> or [[Sybase database]].<ref>{{cite book|url=http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc30191.1550/html/utility/X14951.htm|work=[[Sybase]]|accessdate=14 May 2021|title=Adaptive Server Enterprise 15.5|chapter=Chapter 7: Utility Commands Reference}}</ref>{{better source needed|date=May 2021}}

==Extensibility and programmability==

Line 90 ⟶ 102:

[[Category:Microsoft database software]]

[[Category:Microsoft server technology]]

[[Category:2005 software]]