All You Need to Know About Agillic's ETL Service

ETL Service stands for Extract Transform Load Service. It allows you to extract content from an online source, such as an RSS-feed, and import it into either a Promotion or a Global Data Table.

The Promotion ETL Service can only be run on Staging. This is because content can only be generated on Staging. This new content needs to be manually published to the Production environment to reach live recipients. 

The Global Data Table ETL Service is not limited by the above constraint. As the service updates data and not content, the service can be run both on Staging (for configurators to confirm the data's format) and Production (to reach live recipients.) 

The ETL Service is found under 'System Settings' in the Settings module.

When creating an ETL Service, you will need to set up an ETL transformation script to convert the content from the RSS-feed into a pre-defined format for your Promotion or Global Data Table.

Once enabled, you can apply a schedule to the ETL Service which will cause it to check the RSS-feed at a certain interval.

Below you will find information about:

• How to Set up an ETL Service for a Promotion
• How to Set up an ETL Service for a Global Data Table
• How to Insert an ETL Transformation Script
• How to Execute an ETL Service

 How to Set up an ETL Service for a Promotion

1. Log in to Staging.
2. Create a Promotion with no Propositions.
3. Create a Promotion Field as a TEXT or HTML type per field from your URL source.
4. Open the Settings module and select 'ETL' under the 'Integrations' section.
5. Check the 'Enable Extract, Transform, Load service' checkbox for Staging.
6. Click 'Save'.
7. Click the plus icon to add a new ETL service and the ETL details modal will appear. 
8. Fill in the name of the ETL service in the 'Name' field. You can also fill in the description of the ETL service in the 'Description' field if you wish.
9. Fill in the URL of your source in the 'URL' field.
10. Select if the source is JSON or XML format with the 'Type' radio buttons. You can also add an authentication header with the 'Authentication header' fields if your source requires authentication to access.
11. Select 'Promotion' from the 'Target' drop-down.
12. Select how often the ETL service should run with the 'Schedule' drop-down.
13. Click 'Save'.
14. Publish to Production to go live with your change.

It's important that the Promotion ETL service must be run on Staging and the content generated must be published from Staging to Production. This is because content cannot be directly updated on Production.

An ETL service set up to import to a Promotion

How to Set up an ETL Service for a Global Data Table

1. Log in to Staging.
2. Create a Global Data Table.
3. Create a Global Data Table field per field from your URL source.
4. Open the Settings module and select 'ETL' under the 'Integrations' section.
5. Check the 'Enable Extract, Transform, Load service' checkbox for Staging or Production or both, depending on where the data should be imported to.
6. Click 'Save'.
7. Click the plus icon to add a new ETL service and the ETL details modal will appear.
8. Fill in the name of the ETL service in the 'Name' field. If you wish, you can also fill out the description of the ETL service in the 'Description' field.
9. Fill out the URL of your source in the 'URL' field.
10. Select if the source is JSON or XML format with the 'Type' radio buttons.
11. If you'd like, you can add an authentication header with the 'Authentication header' fields. This is beneficial if your source requires authentication to access.
12. Select 'Global Data Table' from the 'Target' drop-down.
13. Select how often the ETL service should run with the 'Schedule' drop-down.
14. Click 'Save'.
15. Publish to Production to go live with your change.

An ETL service set up to import to a Global Data Table

How to Insert an ETL Transformation Script

First, you must create an ETL Transformation Script before you can insert it into your ETL service.

1. Open the Settings module and select 'ETL' under the 'Integrations' section.
2. Click the code icon for your ETL service and the ETL transformation script modal will appear.
3. Paste in your ETL transformation script in the 'Transformation script' field.
4. Select 'Processed' or 'Raw' from the 'Source' radio buttons, depending on your source.
5. Click the refresh icon next to the 'Source' field.
6. Verify the 'Result' field shows 'Valid'.
7. Click 'Save'.

You've now applied the ETL Transformation Script to your ETL service.

A functional ETL Transformation Script for an ETL service to a Global Data Table 

A functional ETL Transformation Script for an ETL service to a Promotion  

How to Execute an ETL Service

The ETL service will execute according to the schedule that you set up when creating the ETL Service. However, you can execute it manually, if needed.

1. Open the Settings module and select 'ETL' under the 'Integrations' section.
2. Click the play icon for your ETL service and a confirmation box will appear.
   • A confirmation pop-up will appear
3. Click 'OK'.

You've now manually executed the ETL service. 

When you execute the ETL Script, the status of the script can be QUEUED, DONE, SKIPPED, or FAILED:

• QUEUED means that there is a scheduled execution waiting to be completed.
• DONE means that the new data has been parsed and loaded by the ETL service.
  If the ETL job imports data to a GDT it will generate an "etel" import file with the parsed feed data. The import file will then be queued for import in the Agillic import module. Here the import file will be available for inspection as well.
• SKIPPED means that the script execution was disabled and not attempted.
• FAILED means that the XSL script failed to update data in Agillic. This could be a result of a failed import, invalid configuration, or failed script execution.