SQL Server Integration Services: Difference between revisions

Revision as of 07:25, 20 August 2012

Revision as of 06:31, 21 August 2012

Line 1:

'''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 fast and flexible ##[[data warehouse|data warehousing##]] tool used for data extraction, transformation, and loading ##[[#Extract, transform, load|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" and "Enterprise" editions#

==Features==

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 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 na#e, 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#

; Parameters #[SQL Server 2012 Integration Services# : Parameters allow you to assign values to properties within packages at the time of package execution# You can 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#

A package may be saved to a file or to a store with a hierarchical na#espace within a SQL Server instance# In either case, the package content is persisted in ##[[XML###

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

Line 26:

* Conditional Split

* Multicast

* Union-All, Merge, and ##[[Sort-merge join|Merge Join##

* Sort

* Fuzzy Grouping

* Lookup and ##[[Fuzzy logic|Fuzzy##]] Lookup

* Percentage Sampling<ref>#http://msdn#microsoft#com/en-us/library/ms139864#aspx creates a sample data set by selecting a percentage of the transformation input#</ref> and Row Sampling Transformation#

* Copy/Map, Data Conversion, and Derived Column

* Aggregation

* Data Mining Model Training, Data Mining Query, Partition Processing, and Dimension Processing

* ##[[Pivot table|Pivot##]] and Unpivot

* ##[[Slowly Changing Dimension##

* Script Component

* Audit

Line 42:

* OLE DB Command

* Row Count

* ##[[term extraction|Term Extraction##

* Term Lookup

* Row Sampling

Line 54:

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###]] 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, rena#e, encrypt, or decrypt packages#

==Extensibility and programmability==

Line 61:

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 ##[[Assembly #[CLI#|CLI assembly##]] ##[[Dynamic-link library|DLL##s, providing access to virtually any kind of operation permissible by the #[NET ##[[Common Language Runtime|CLR###

SSIS can be used on all SQL Server ##[[Microsoft_SQL_Server#SQL_Server_2005|2005##/##Microsoft_SQL_Server#SQL_Server_2008|2008##/##Microsoft_SQL_Server#SQL_Server_2008_R2|2008 R2##/2012 editions except Express and Workgroup#

==Troubleshooting==

* #[http://support#microsoft#com/kb/918760 An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step#

* #[http://technet#microsoft#com/en-us/sqlserver/ff686900#aspx Troubleshooting: SSIS Package Execution Using SQL Server Agent #[SQL Server Video##

* #[http://social#technet#microsoft#com/wiki/contents/articles/7639#troubleshoot-configuration-issues-in-microsoft-change-data-capture-for-oracle-by-attunity#aspx Troubleshoot Configuration Issues in Microsoft Change Data Capture for Oracle by Attunity#

* #[http://social#technet#microsoft#com/wiki/contents/articles/7642#troubleshoot-cdc-instance-errors-in-microsoft-change-data-capture-for-oracle-by-attunity#aspx Troubleshoot CDC Instance Errors in Microsoft Change Data Capture for Oracle by Attunity#

* #[http://social#technet#microsoft#com/wiki/contents/articles/11848#sql-server-integration-services-ssis-troubleshooting-articles#aspx SQL Server Integration Services #[SSIS#: Troubleshooting Articles#

==See also==

Line 76:

==External Tools==

* #[http://pragmaticworks#com/Products/Business-Intelligence/DTSxChange/DTSxChange-Download#aspx SQL Server Integration Tools#

* #[http://www#cozyroc#com/search-scripts/ SSIS Scripts#

* #[http://www#gerasus#com/ SSIS package search and documentation tool, SSIS-DTS Package Search#

* #[http://ssisconfigeditor#codeplex#com/ SSIS Package Configuration Editor#

* #[http://www#ssisdocumenter#com/ SSIS 2005/2008 Documenter#

* #[https://ssispropertyeditor#codeplex#com/ SSIS Package Property Editor#

==References==

Line 88:

==External links==

* [http://www.microsoft.com/sqlserver/2008/en/us/integration.aspx SQL Server Integration Services official site]

* [http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx SQL Server Integration Services #[SSIS# Resources]

* [http://social.technet.microsoft.com/Forums/en/sqlintegrationservices/threads SQL Server Integration Services Discussion Forum]

* [//msdn.microsoft.com/en-us/library/hh667275.aspx 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012]