ETL through .NET API

As with other data objects within Exago, ETL Jobs and settings can be created, edited, and managed through the .NET API.

Note: For more information on .NET API, please refer to the .NET API General Reference article.

 

ETL Settings Management

The following ETL Settings are available and can be managed via the .NET API:

Activating ETL

Set the licensing information in order to active the ETL module.

api.General.LicenseKey = "<licenseKey>";
Note: For information regarding licensing please see the ETL Licensing article.

ETL Settings Information

Create the new folder for storing ETL Reports. Using a separate folder for storing ETL Reports is recommended.

ReportMgmtFileSystem manager = new ReportMgmtFileSystem(api.PageInfo);
manager.AddFolder(@"C:\Exago\Reports\", "ETL_Reports");

Set the ETL Reports Folder setting to the newly created folder.

api.General.EtlReportFolder = "ETL_Reports"; 

 

ETL Report Creation

The following methods of ETL Report creation are available via the .NET API:

Launching the ETL Report Designer

To launch directly into the the ETL Report designer for the purpose of creating or editing an ETL Report within the embedded application, the following information needs to be set via the API prior to calling API.Action:

pageInfo.SetupData.ReportDesignerMode = wrReportDesignerMode.Etl;

To launch the ETL Report designer in order to edit an existing ETL Report:

api.Action = wrApiAction.EditEtlDesign;

To launch the ETL Report designer in order to create a new ETL Report:

//Set the Destination Data Source, this is required to create an ETL Report
etlObject.DataSourceId = api.DataSources.GetDataSource("ETL").Id;
api.Action = wrApiAction.NewEtlDesign;

 

Creating an ETL Report through the API

Though complex, it is also possible create an ETL Report from scratch within the API. The following code provides an example of creating a simple, two-column report through the API. Nevertheless, it is highly recommended that reports are created within the application, as it offers a much more efficient and user-friendly experience.

This example ETL Report will contain two columns, one for Orders.OrderID and one for Order.OrderDate, and two rows, one for the report header and one for the detail section.

//Create and name a new Report object
Report report = new Report(pageInfo);
report.Name = "Orders_ETL";

//Set the Report Type to an Advanced Report
report.ReportType = wrReportType.Advanced;

//Set up the Rows for the Report: Detail & Report Header
Row headerRow = new Row(pageInfo);
Row detailRow = new Row(pageInfo);

//Set up Sections for each row
Section headerSection = null;
Section detailSection = null;

//Set the Section Types of each Row
headerRow.SectionType = Row.RowSectionType.ReportHeader.ToString();
detailRow.SectionType = Row.RowSectionType.Detail.ToString();

//Instantiate each Section
headerSection = new Section(pageInfo, Section.SectionType.ReportHeader,
string.Empty, 0, 0,
new Row.RowShadingColors(), string.Empty);
detailSection = new Section(pageInfo, Section.SectionType.Detail,
string.Empty, 1, 1,
new Row.RowShadingColors(), string.Empty);

//Add the Sections to the Report
report.Sections.Add(headerSection);
report.Sections.Add(detailSection);

//Create the Columns for each data field
Column orderIdCol = new Column(pageInfo);
Column orderDateCol = new Column(pageInfo);

//Add the ETL Data Type metadata to each Column
ColumnEtlMetadata orderIdDT = new ColumnEtlMetadata();
orderIdDT.DataTypeId = "1"; //Data Type "1" is Integer
orderIdCol.EtlMetadata = orderIdDT;

ColumnEtlMetadata orderDateDT = new ColumnEtlMetadata();
orderDateDT.DataTypeId = "3"; //Data Type "3" is DateTime
orderDateCol.EtlMetadata = orderDateDT;

//Add the Columns to the Report
report.Columns.Add(orderIdCol);
report.Columns.Add(orderDateCol);

//Create the Header and Detail Cells for each field
//Header Cells for each Column
Cell idHeaderCell = new Cell(pageInfo, 0, 0); //Specify Cell coordinates
idHeaderCell.Type = CellType.Text;
idHeaderCell.Text = "OrderID";
report.Cells.Add(idHeaderCell);

Cell dateHeaderCell = new Cell(pageInfo, 0, 1);
dateHeaderCell.Type = CellType.Text;
dateHeaderCell.Text = "OrderDate";
report.Cells.Add(dateHeaderCell);

//Detail Cells for each Column
Cell idDetailCell = new Cell(pageInfo, 1, 0);
idDetailCell.Type = CellType.Data;
idDetailCell.SaveText = "Orders.OrderID";
report.Cells.Add(idDetailCell);

Cell dateDetailCell = new Cell(pageInfo, 1, 1);
dateDetailCell.Type = CellType.Data;
dateDetailCell.SaveText = "Orders.OrderDate";
report.Cells.Add(dateDetailCell);

//Add the information of the Cells to the Rows and to each Section
headerRow.Cells.Add(idHeaderCell);
headerRow.Cells.Add(dateHeaderCell);
report.Rows.Add(headerRow);
headerSection.AddRow(headerRow, headerRow.Cells);

detailRow.Cells.Add(idDetailCell);
detailRow.Cells.Add(dateDetailCell);
report.Rows.Add(detailRow);
detailSection.AddRow(detailRow, detailRow.Cells);

//Add the Data Table that is being referenced
Entity ordersEnt = api.SetupData.Entities.GetEntity("Orders");
report.Entities.Add(ordersEnt);

This report can then optionally be saved to the repository and moved to the ETL Reports Folder so that it may be accessed by ETL Jobs outside of the current API session.

//Save the Report to the repository
api.ReportObjectFactory.SaveToRepository(report);

//Move the Report to the ETL Report Path
manager.RenameReport(
@"C:\Exago\Reports\Orders_ETL",
@"C:\Exago\Reports\ETL_Reports\Orders_ETL");

 

ETL Job Creation, Execution, and Scheduling

The following code example demonstrates how to create, execute and schedule an ETL Job through the .NET API:

EtlJobObject etlObject = new EtlJobObject(pageInfo);

//ETL Job Name
etlObject.Name = "ETL Orders";
//Destination Data Source
etlObject.DataSourceId = api.DataSources.GetDataSource("ETL").Id;
//Destination Data Object
etlObject.ObjectName = "Orders_ETL";
//Data Source Report Name
etlObject.ReportName = @"ETL_Reports\Orders_ETL";
//Enable Scheduling
etlObject.IsEnabled = false;

//Add Etl Object
pageInfo.SetupData.EtlObjects.Add(etlObject);

//If executing immediately, set to true
//If scheduling the ETL Job, set to false
bool executeNow = true;

if (executeNow)
{
etlObject.SetSchedulerInfo(new EtlRecurrencePackage(), true);
}
else
{
EtlRecurrencePackage recurrencePackage = new EtlRecurrencePackage()
{
//Scheduling information
isImmediate = false,
scheduleTime = new DateTime(2019, 1, 31, 2, 0, 0),
repeatEvery = true,
repeatEveryHours = 12,
repeatEveryMinutes = 0,
repeatEveryEndTime = new DateTime(2019, 1, 31, 15, 0, 0),
recurrencePattern = "daily", //"once"; "weekly"; "monthly"; "yearly"
isRangeNoEnd = true
};
etlObject.SetSchedulerInfo(recurrencePackage, false);
}

etlObject.ScheduleAndSave();

//Save to the API
api.SaveData();

 

ETL Schedule Manager

The ETL Schedule Manager can be set up and managed through the API using the same methods listed in the Schedule Queue article; however, in order to do so, the following information needs to first be set through the API:

pageInfo.SetupData.ReportDesignerMode = wrReportDesignerMode.Etl;
api.Action = wrApiAction.ScheduledReportsManager;

 

Data Object Creation

After an ETL Job is created, a corresponding data object can automatically be created via the .NET API:

Entity etlEnt = api.Entities.NewEntity("Orders (ETL)");

//Add required and optional information
//Required:
etlEnt.DataSourceId = api.DataSources.GetDataSource("ETL").Id;
etlEnt.ObjectType = DataObjectType.Table;
etlEnt.DbName = "Orders_ETL";
etlEnt.Id = "Orders_ETL";
etlEnt.KeyColumns.Add(
new KeyColumn(etlEnt.GetColumn("OrderID").ActualFullName)
);

//Optional:
etlEnt.Category = "ETL";
etlEnt.ObjectDescription = "ETL'd Order Data";

//Save to the API
api.SaveData();
Note: For more information on advanced .NET API features for data objects (e.g., Joins), please see the Advanced Configuration section in the .NET API General Reference article.