SQL Server Integration Services – SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.
Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.
SSIS is basically an ETL (Extraction, Transformation, and Load) tool whose main purpose is to do extraction, transformation and loading of data but it can be used for several other purposes for example, to automate maintenance of SQL Server databases, update multidimensional cube data etc as well.
Creating an SSIS Package
There are three different ways to create SSIS packages, each is described below.
- Import and Export Wizard – Though one of the simplest way to create an SSIS package, it has very limited capability. No kind of transformation can be defined using this method. Though with SSIS 2008, there is an option to include a Data Conversion Transformation if there is a mismatch in data type between source and destination. The wizard is primarily used for simple data transfer from source to destination. For further details, refer to “Import and Export” section later in this article.
- The SSIS Designer – The SSIS Designer is hosted inside the Business Intelligence Development Studio (BIDS) as part of an Integration Services project. It is a graphical tool that can be used to create and maintain Integration Services packages. It has a toolbox which contains the various items needed for Control Flow, Data Flow Task as well as tasks needed for maintenance plans. The number of tasks in SSIS is much larger than what was available in the DTS. For more details, refer to “SSIS Designer” section later in this article.
- SSIS API Programming – SSIS provides API object model, which can be used in a variety of programming languages to create SSIS package programmatically. For more details refer to “SSIS API Programming” section later in this article.