ETL Scenario Examples
Basic stuff
- Migrate data from the source to destination
- Migrate data from the source to the specified destination table
- Stream data
- High performance data migration
- Automatically create tables and indexes
- Conrol how to commit transaction
- Map source columns to destination columns. Exclude some columns
- Map column with spaces in the name to the variable name
- Create variables with spaces in the name
- Add columns to the destination on the fly using SQL
- Add column to the destination on the fly using JavaScript
- Change column type
- Map Source to Destination if names are different
- Migrate data from source to destination when names are different
- Migrate data using specific ETL driver
- Using generic ETL driver together with specific database ETL driver
- Control flow of extracts and loads
- Scenario Variables
- Variable substitution
- Allowed ETL actions
- Use attributes to force required connections
- Disable creation of the temporary tables in the destination database
- Merge data
- Merge data using generic ETL driver
- Update data
- Delete data
- Conditional action (insert, delete or update)
- Inline SQL
- Conitionaly include sources and destinations using JavaScript
- Conitionaly include sources and destinations using SQL
- Destination Exceptions
- If/Then/Else
- On (event) actions
- Migrate data from multiple source connections
- Multiple destination connections
- Migrate data to the multiple destination connections in parallel threads
- Extract and load data in parallel threads
- Parallel extract and load with synchronization
- Inner scenarios
- Inner Scenarios with individual actions
- Inner + Main Scenarios
- Execute inner scenarios in parallel threads
- Conditionaly execute inner scenarios using JavaScript
- Conditionaly executes inner scenarios using SQL
- Execute inner scenarios in the loop using SQL
- Execute inner scenarios in the loop using JavaScript
- Execute inner scenarios in the loop as long as condition is true
- Using stored procedure and reference cursor to extract data
Column level transformations
Dataset transformations
- Order Data Set
- Transpose Matrix
- Filter Data Set
- Remove duplicates
- Intersect datasets
- Minus data sets
- Union data sets
- Join Data Sets
- Join multiple data sets
- Diff datasets
- Group by
- Denormalize dataset
- Partition dataset
- Matrix transformation
- Key-Value Denormalization
- Key-Value Normalization
- Execute JavaScript
- Populate Dataset from any fragment of text using given connector, such as JSON, XML, etc
Dimension transformations
Data Migration
- High performance data migration
- Automatically create tables and indexes
- Migrate data from one db to another using diffrent table names
- Extract data from database and create files in any supported format
- Use named connections
- Migrate data using specific ETL driver
- Simple data migration
- Migrate data using specific parent ETL driver
- Migrate data to the multiple destination connections in parallel threads
- Migrate data using specific source ETL driver
- Migrate data in parallel threads with synchronization
- Stream data from source to destination
Common functions
- Get new column value for the destination table and keep referential integrity
- Use default functions instead of driver specific
- Calculate column value using JavaScript
- Exclude fields
- Assign scenario variable value to the column value
Common Tasks
- Validation using JavaScript
- Transformation using JavaScript
- Regex replacement
- SQL task
- Operating System command
- File Merge
- IO tasks
- XSL transformation
- Send email
- Before and after tasks
Web Services
- REST based Web service - GET request
- REST based Web service - POST request
- SOAP Web service
- Track downloads using Google Analytics API
XML Files in the Toolsverse data set format
- Export from xml to database using pre-configured xml connection
- Export from xml to database using hard coded file name
- Export from xml to database with xsl transformation using hard coded xml and xsl file names
- Export from database table to xml using xsl transformation
- Extract data from database table and load into xml file using xsl transformation
- Load data from xml into database using hard coded file name
- Export data from database into XML data set file
XML Files
- Export from xml to database using pre-configured xml connection
- Export from xml to database using hard coded file name
- Load data from xml into database using hard coded file name
- Export data from database into XML file
- Using XQuery
Delimited and Fixed Length Text Files
- Export data from database table to delimited text file
- Export data from database table to the fixed lenth text file
- Export from delimited text file to the database using pre-configured connection
- Export from text file to database using hard coded file name
- Export from database to text file using hard coded file name
- Export from database to text file using pre configured text connection
- Export text file to database using pre-configured text connection. Calculate\exclude fields.
- Using properties to configure file format
JSON files
- Export from json to database using pre-configured json connection
- Export from json to database using hard coded file name
- Load data from json into database using hard coded file name
- Export data from database into JSON file
HTML files
PDF files
Excel
- Export from database to Excel using pre-configured excel connection
- Export from Excel to database using pre-configured Excel connection
- Export from Excel (1997-2003) to database using hard coded file name
- Export from Excel (2007) to database using hard coded file name
- Export from Excel (1997-2003) to database using ODBC
- Export from database to Excel (1997-2003) using ODBC
- Export from database to Excel (1997-2003) file using hard coded file name
- Export from database to Excel (2007) file using hard coded file name
- Export from database table to Excel (1997-2003) file
- Export from database table to Excel (2007) file
Readers and Writers
Oracle specific functions and transformations
- Migrate data, including CLOBs and BLOBs
- Get new column value for the destination table using Oracle sequence and keep referential integrity
- Using PL/SQL in the etl scenario
- Create PL/SQL function and use it in the etl scenario. Use Oracle global variable
- Using Oracle cursors in the etl scenario
- Using sqlplus COPY command to copy data from one table to another
- Oracle Extract using slplus and Load using sql*loader
- Oracle merge, update, delete
DB2 specific functions and transformations
- Migrate data including BLOBs and CLOBs
- Get new column value using DB2 autoincrement column and sequence and keep referential integrity
- Using SQL/PL in the etl scenario
- Create SQL/PL function and use it in the etl scenario. Use DB2 global variable
- Using DB2 cursors in the etl scenario
- DB2 Extract and Load using ADMIN_CMD stored procedure
- Db2 merge, update, delete
MySql specific functions and transformations
- Migrate data including CLOBs and BLOBs
- Get new column value using MySql autoincrement column and keep referential integrity
- Using MySql procedural language in the etl scenario
- Create MySql function and use it in the etl scenario. Use Scenario global variable
- Using MySql cursors in the etl scenario
- MySql Extract using select INTO OUTFILE and Load using LOAD DATA
- MySql merge, update, delete
PostgreSQL specific functions and transformations
- Migrate data including BLOBs and CLOBs
- Get new column value using PostgreSQL sequence and serial column and keep referential integrity
- Using PL/pgSQL in the etl scenario
- Create pgSQL function and use it in the etl scenario. Use Scenario global variable
- Using PostgreSQL cursors in the etl scenario
- PostgreSQL Extract and Load using COPY
- PostgreSQL merge, update, delete
MS SQL Server specific functions and transformations
- Migrate data including BLOBs and CLOBs
- Get new column value using Ms Sql autoincrement and SQL and keep referential integrity
- Using TransactSQL in the etl scenario
- Create TransactSQL function and use it in the etl scenario. Use Scenario global variable
- Using Ms Sql cursors in the etl scenario
- Ms Sql Extract using bcp and Load using bulk insert and bcp
- Ms Sql merge, update, delete
Sybase SQL Server specific functions and transformations
- Migrate data including BLOBs and CLOBs
- Get new column value using Sybase autoincrement and SQL and keep referential integrity
- Using Sybase TSQL in the etl scenario
- Create TSQL function and use it in the etl scenario. Use Sybase app_context
- Using Sybase cursors in the etl scenario
- Sybase Extract and load using bcp
- Sybase, merge, update, delete
Informix specific functions and transformations
- Move blobs, clobs and other types of data. Create tables if needed
- Get new column value using Informix serial column and sequence and keep referential integrity
- Using Informix SPL in the etl scenario
- Create SPL function and use it in the etl scenario. Use Informix global variable
- Using Informix cursors in the etl scenario
- Informix Extract and Load using DbAccess
- Informix merge, update, delete