Microsoft SQL Server Integration Services (SSIS) is an Extract Transform and Load (ETL) utility that is supplied and licensed as part of Microsoft SQL Server 2005+. SSIS is built to extract data from data sources using extraction technologies, such as OLEDB, transform the data using its own .NET based language and then load it into data destination. SSIS is primarily used for loading data from various sources into SQL Server, but it can be used to extract from most databases and load into any other database.
WhereScape RED manages the extraction and load of data into the data warehouse as part of data warehouse processing. WhereScape RED attempts to utilize the optimum loading technology for each supported database platform. Optimal loading performance from a database source into SQL Server is achieved using SSIS. WhereScape RED provides the ability to leverage the extract and load performance available from SSIS as part of the WhereScape RED processing. WhereScape RED does this by generating and executing an SSIS package dynamically at run time. Any errors or messages resulting from execution are passed back into the WhereScape RED workflow metadata to drive subsequent workflow actions. In the event of an error during execution of the SSIS package, the package will be persisted to disk to assist the developer with problem resolution.
An Integration Services Load can have a number of source databases:
- SQL Server
- Oracle
- DB2 for Linux, UNIX and Windows (the DB2 RED can build a warehouse in)
- Teradata
- Sybase
- MySQL
- DB2/400 (the DB2 that runs on AS/400 machines - quite different to above)
- MS Access
- Excel
To use SSIS to load data, select the relevant version of SSIS in Tools > Options > Code Generation > General.