Use a temporary database as a staging area

Overview

With the Etlworks Integrator, it is possible to create a Connection to a temporary, in-memory database and use it as a staging area for the data. The SQLite engine is used for these temporary databases. You can process a single file as well as multiple files from the source folder.

It's not required to clean up or drop the table. It will automatically drop when the Flows are executed.

Process

Work with a single file: Store the content into the staging database

You can store the source file's content or a response from a web service in the staging database and then use SQL, running against the database to transform the data.

Step 1. Create a Connection to a temporary database.

Step 2. Create a Flow where the source is a file or a web service, and the destination is a Connection created in step 1. Let the system automatically create the staging table.

Step 3. Combine Flow created in step 2 with other Flows to transform the source. 

Work with multiple files: Create a single output file in a different Format

To process multiple files, follow the steps below.

Step 1. Create a Connection to a temporary database.

Step 2. Create a Flow that reads multiple source files and inserts data into the staging database, using the Connection created in Step 1.

Step 3. Create a Flow, which reads data from the staging database and creates an output file.

Step 4 Create a nested Flow, which combines the Flows created in Steps 2 and 3.

Use a temporary database for a driving table in a database loop

Read about the database loop and do the following:

Step 1. Create a Connection to a temporary database.

Step 2. Create a Flow that reads multiple source files and inserts data into the staging database using the Connection created in Step 1.

Step 3. Create a database loop using the Connection created in Step 1.