New Project Types - Extract and Load Project and SQL Transformation Project

In addition to the Extract, Transform, and Load (ETL) Project type that continues to provide the same functionality as in the previous releases, two new project types are added in Sybase IQ InfoPrimer.

While the existing ETL project type provides the capability for extracting data from a data source, transforming and cleaning the data, and then loading it into a Sybase IQ data target, the new project types, namely Extract and Load (EL) and SQL Transformation projects, allow you to extract data from source into IQ stage tables, transform it, and load it into IQ destination tables. This helps to leverage the data processing power of Sybase IQ.

Extract and Load project – is used for designing high speed bulk extraction and bulk loading processes, with a single instance of Sybase IQ as the target. The EL project lets you extract data from any supported datasource and load it into a Sybase IQ database or to a Sybase IQ load file.

Unlike the ETL project, an EL project facilitates extracting multiple tables in Sybase IQ from a single database source or from multiple files. An EL project allows you to migrate data from Oracle, Adaptive Server Enterprise, Microsoft SQL Server, flat files, and other supported datasources to a Sybase IQ database, if minor or no schema changes are needed.

The data file types supported as a source by an EL project are:
- Sybase IQ compatible binary format
- Text files with delimited record values (including Apache Web log formats)
- Files with fixed length values and variable length records
- Large objects (LOBs)
  
  Note: Files with fixed-length values and fixed-length records are not supported. For this particular file type, use ETL project instead.
Extracting LOB data is supported only for Adaptive Server, Oracle, Microsoft SQL Server, and LOB files (binary files or character files).

Note: The EL project does not support XML as a source. To load XML sources, use the XML via SQL Data Providers component of the ETL project.

See Sybase IQ InfoPrimer Users Guide > Extract and Load Projects.
SQL Transformation project – performs SQL transformations where the source and target are both on the same instance of Sybase IQ. It generates transformations to be performed on data that exists in a Sybase IQ database. As compared to JavaScript transformations, SQL transformations executed in Sybase IQ can enhance performance and scalability.
A SQL Transformation project defines the Sybase IQ connection at the project level. The entire transformation project is translated into a single SQL script, so all transformation tasks use the same connection. A SQL Transformation project controls execution flow, based on the connections between its components. The components required to handle execution flow are:
- Start – represents the beginning of a control flow.
- Error – marks the end of a failed control flow.
- Success – marks the successful end of a control flow.
- Decision – routes execution flow based on SQL rules.
- Task Group – defines ordered groups of transformation tasks.
See Sybase IQ InfoPrimer Users Guide > SQL Transformation Projects.
Extract Transform and Load (ETL) project – extracts data from multiple heterogeneous data sources and loads it into one or more data targets using a comprehensive set of transformation functions. All functionalities of the ETL projects remain the same as in Sybase ETL version 4.9. See Sybase IQ InfoPrimer Users Guide > Extract, Transform, and Load Projects.