What is SSIS
- SSIS is a platform for building high performance data integration and workflow solutions, including the extraction, transformation and load (ETL) operations of data warehousing.
Uses of SSIS
- Populating Data Warehouses and Data Marts
- Merging Data from Heterogeneous Data Stores
- Cleaning and Standardizing Data
- Building Business Intelligence into a Data Transformation Process
- Automating Administrative Functions and Data Loading
- The new integration services architecture is divided into two main sections.
1. Data Transformation pipeline (DTP)
-Take the place of the old DTS data pump that was used in SQL server 7 and 200.
- Primary function is handle the data flow between the source and target destinations
2. Data Transformation Runtime (DTR)
- A job execution environment that controls the control flow that is used in an SSIS package.
- Each of these components exposes its own distinct object model
Data Transformation Pipeline (DTP): Overview
- The DTP takes care of the data flow and transformations that take place as rows are moved between the data source and data target
- DTP uses data adapters to connect to the source and destination data sources.
- The DTP engine is accessed using the DTP Pipeline object model.
Data Transformation Runtime (DTR): Overview
- The DTR is essentially a job execution environment that controls the control flow that’s used in an SSIS package.
- The DTR is accessed using the DTR object framework.
- The DTR run-time object framework is the API that supports the integration service import/export wizard and the integration service designer in addition to the command-line dtexe tool.
What is SSIS
Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. SQL Server Integration Services (SSIS) is a tool that we use to perform ETL operations; i.e. extract, transform and load data. SSIS provides the ability to:
Retrieve data from just about any source
Perform various transformations on the data; e.g. convert from one type to another, convert to uppercase or lowercase, perform calculations, etc.
Load data into just about any source
Define a workflow
Creating SSIS packages with SQL Server Management Studio (SSMS)
Business Intelligence Development Studio (BIDS)
Creating a simple SSIS package in BIDS
Deploying SSIS packages
Executing SSIS packages