Operations: How to Add ETL

This API article demonstrates how to create various ETL tasks and the connection strings required.

You can add an ETL task by using the AddEtlOperation method.
ETL tasks are ongoing tasks that:
1. Extract selected data from your source database when changes are made or new data is added.
2. Apply a transform script on the data.
3. Load the transformed data to a destination that you designate.
To learn more about ETL (Extract, Transfer, Load) ongoing tasks, see this article on ETL Basics.
To learn how to manage ETL tasks using the Studio see: RavenDB ETL Task.
In this page:
AddEtlOperation
Add Raven ETL
- Creating a Connection String for Raven ETL
- Code Sample to Add Raven ETL
Add Sql ETL
- Creating a Connection String for Sql ETL
- Code Sample to Add Sql ETL

You can add ETL task by using the AddEtlOperation method.

AddEtlOperation

public AddEtlOperation(EtlConfiguration<T> configuration)

Parameters
configuration	`EtlConfiguration<T>`	ETL configuration where `T` is connection string type

Add Raven ETL

Raven ETL tasks enable ongoing Extract, Transform, Load functionality from a RavenDB source database to a RavenDB destination.

Secure servers
In addition to defining a connection string, to connect secure RavenDB servers you must export the server certificate from the source server and install it as a client certificate on the destination server.
After passing the certificate, you can either create an ETL with a connection string and transformation script via the studio or with the following API.

Creating a Connection String for Raven ETL

{
  //define connection string
  var ravenConnectionString = new RavenConnectionString()
  {
      //name connection string
      Name = "raven-connection-string-name",

      //define appropriate node
      //Be sure that the node definition in the connection string has the "s" in https
      TopologyDiscoveryUrls = new[] { "https://127.0.0.1:8080" },

      //define database to connect with on the node
      Database = "Northwind",

  };
  //create the connection string
  var resultRavenString = store.Maintenance.Send(
      new PutConnectionStringOperation<RavenConnectionString>(ravenConnectionString));
}

Code Sample to Add Raven ETL

AddEtlOperation<RavenConnectionString> operation = new AddEtlOperation<RavenConnectionString>(
  new RavenEtlConfiguration
  {
      ConnectionStringName = "raven-connection-string-name",
      Name = "Employees ETL",
      Transforms =
      {
          new Transformation
          {
              Name = "Script #1",
              Collections =
              {
                  "Employees"
              },
              Script = @"loadToEmployees ({
                      Name: this.FirstName + ' ' + this.LastName,
                      Title: this.Title
              });"
          }
      },

      // Do not prevent task failover to another node
      PinToMentorNode = false

  });

AddEtlOperationResult result = store.Maintenance.Send(operation);

Add Sql ETL

SQL ETL tasks enable ongoing Extract, Transform, Load functionality from RavenDB to SQL servers.

Creating a Connection String for Sql ETL

{
  // define new connection string
  PutConnectionStringOperation<SqlConnectionString> operation
  = new PutConnectionStringOperation<SqlConnectionString>(
      new SqlConnectionString
      {
          // name connection string
          Name = "local_mysql",

          // define FactoryName
          FactoryName = "MySql.Data.MySqlClient",

          // define database - may also need to define authentication and encryption parameters
          // by default, encrypted databases are sent over encrypted channels
          ConnectionString = "host=127.0.0.1;user=root;database=Northwind"

      });

  // create connection string
  PutConnectionStringResult connectionStringResult
  = store.Maintenance.Send(operation);

}

Code Sample to Add Sql ETL

AddEtlOperation<SqlConnectionString> operation = new AddEtlOperation<SqlConnectionString>(
  new SqlEtlConfiguration
  {
      ConnectionStringName = "sql-connection-string-name",
      Name = "Orders to SQL",
      SqlTables = {
          new SqlEtlTable {TableName = "Orders", DocumentIdColumn = "Id", InsertOnlyMode = false},
          new SqlEtlTable {TableName = "OrderLines", DocumentIdColumn = "OrderId", InsertOnlyMode = false},
      },
      Transforms =
      {
          new Transformation
          {
              Name = "Script #1",
              Collections =
              {
                  "Orders"
              },
              Script = @"var orderData = {
                              Id: id(this),
                              OrderLinesCount: this.Lines.length,
                              TotalCost: 0
                          };

                          for (var i = 0; i < this.Lines.length; i++) {
                              var line = this.Lines[i];
                              orderData.TotalCost += line.PricePerUnit;
                              
                              // Load to SQL table 'OrderLines'
                              loadToOrderLines({
                                  OrderId: id(this),
                                  Qty: line.Quantity,
                                  Product: line.Product,
                                  Cost: line.PricePerUnit
                              });
                          }
                          orderData.TotalCost = Math.round(orderData.TotalCost  * 100) / 100;

                          // Load to SQL table 'Orders'
                          loadToOrders(orderData)"
          }
      },

      // Do not prevent task failover to another node
      PinToMentorNode = false

  });

AddEtlOperationResult result = store.Maintenance.Send(operation);

AddEtlOperation​

Add Raven ETL​

Creating a Connection String for Raven ETL​

Code Sample to Add Raven ETL​

Add Sql ETL​

Creating a Connection String for Sql ETL​

Code Sample to Add Sql ETL​