ETLBox

https://www.etlbox.net/docs
Admin
ETLBox is a .NET library for building data integration pipelines with support for relational...
Tokens:98,446
Snippets:1,161
Trust Score:7.1
Update:1 month ago
Show doc for...
Tokens:
Raw
### SQL Table Setup for Bulk Update

Source: https://www.etlbox.net/docs/relational-databases/dbdestination

Initial SQL schema and data setup for the bulk update demonstration.

```sql
CREATE TABLE DestinationTable (
    Id INT NOT NULL PRIMARY KEY,
    Value VARCHAR(50) NULL
);

INSERT INTO DestinationTable (Id, Value) VALUES
(1, 'OldValue1'),
(2, 'OldValue2'),
(3, 'OldValue3');
```

--------------------------------

### SQL Table Setup for Bulk Delete

Source: https://www.etlbox.net/docs/relational-databases/dbdestination

Initial SQL schema and data setup for the bulk delete demonstration.

```sql
CREATE TABLE DestinationTable (
    Id INT NOT NULL PRIMARY KEY,
    Value VARCHAR(50) NULL
);

INSERT INTO DestinationTable (Id, Value) VALUES
(1, 'Value1'),
(2, 'Value2'),
(3, 'Value3');
```

--------------------------------

### Install ETLBox.Analysis Package

Source: https://www.etlbox.net/docs/specialized-connectors/overview

Commands and configuration snippets to install the ETLBox.Analysis package for DataFrame support.

```bash
dotnet add package ETLBox.Analysis
```

```powershell
PM> NuGet\Install-Package ETLBox.Analysis
```

```xml
<PackageReference Include="ETLBox.Analysis" Version="latest" />
```

```fsharp
> #r "nuget: ETLBox.Analysis, latest"
```

--------------------------------

### Install ETLBox Database Package

Source: https://www.etlbox.net/docs/relational-databases/overview

Command to install the required NuGet package for a specific database provider.

```bash
dotnet add package ETLBox.SqlServer
```

--------------------------------

### Define POCO and XmlDestination

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Example of a POCO class with XML attributes and the corresponding XmlDestination setup.

```csharp
[XmlRoot("MySimpleRow")]
public class MyRow
{
    [XmlAttribute]
    public int Col1 { get; set; }
    [XmlAttribute]
    public string Col2 { get; set; }
}

XmlDestination<MyRow> dest = new XmlDestination<MyRow>("dest.xml", ResourceType.File);
```

--------------------------------

### Full ADO.NET row count example

Source: https://www.etlbox.net/docs/control-flow/overview

Complete example showing the boilerplate required for a row count operation using ADO.NET.

```csharp
string connectionString = "Data Source=.; Database=Sample; Integrated Security=SSPI";
using (SqlConnection con = new SqlConnection(connectionString))
{
    SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM demotable", con);
    con.Open();
    int numRows = (int)cmd.ExecuteScalar();
}
```

--------------------------------

### Install MongoDB Connector

Source: https://www.etlbox.net/docs/nosql-databases/overview

Installation commands for the ETLBox MongoDB connector across different package management environments.

```bash
dotnet add package ETLBox.MongoDb
```

```powershell
PM> NuGet\Install-Package ETLBox.MongoDb
```

```xml
<PackageReference Include="ETLBox.MongoDb" Version="latest" />
```

```fsharp
> #r "nuget: ETLBox.MongoDb, latest"
```

--------------------------------

### Basic Deduplication Example

Source: https://www.etlbox.net/docs/transformations/distinct

Demonstrates linking a MemorySource to a Distinct transformation and outputting the results to a MemoryDestination.

```csharp
var source = new MemorySource<MyRow>();
source.DataAsList.Add(new MyRow() { Id = 1, Value = "A", TestId = "Test1" });
source.DataAsList.Add(new MyRow() { Id = 2, Value = "A", TestId = "Test2" });
source.DataAsList.Add(new MyRow() { Id = 2, Value = "B", TestId = "Test3" });
source.DataAsList.Add(new MyRow() { Id = 1, Value = "A", TestId = "Test4" });
source.DataAsList.Add(new MyRow() { Id = 2, Value = "A", TestId = "Test5" });
source.DataAsList.Add(new MyRow() { Id = 3, Value = "B", TestId = "Test6" });

var trans = new Distinct<MyRow>();
var dest = new MemoryDestination<MyRow>();

source.LinkTo(trans);
trans.LinkTo(dest);
Network.Execute(source);

foreach (var row in dest.Data)
    Console.WriteLine($"Id:{row.Id} Value:{row.Value} TestId:{row.TestId}");

//Output
/*
Id:1 Value:A TestId:Test1
Id:2 Value:A TestId:Test2
Id:2 Value:B TestId:Test3
Id:3 Value:B TestId:Test6
*/
```

--------------------------------

### Execute data flow to database

Source: https://www.etlbox.net/docs/relational-databases/dbdestination

Full example showing how to link a memory source to a DbDestination and execute the network.

```csharp
var conn = new SqlConnectionManager("Data Source=.;Integrated Security=SSPI;");
var source = new MemorySource<MyRow>(new List<MyRow> {
    new MyRow { Id = 1, Value = "A" },
    new MyRow { Id = 2, Value = "B" }
});
var dest = new DbDestination<MyRow>(conn, "DestinationTable");

source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### Expected Output for Value Generation

Source: https://www.etlbox.net/docs/relational-databases/dbdestination

Example output showing populated values after database insertion.

```text
Row1: Id=1, Value=Test1, CreatedDate=2024-02-25 12:34:56
Row2: Id=2, Value=Test2, CreatedDate=2024-02-25 12:34:56
```

--------------------------------

### XML Example Data

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Sample XML files used for demonstrating dynamic parsing and element handling.

```xml
<?xml version="1.0" encoding="utf-8"?>
<root>
  <comment>Hello, world!</comment>
  <entry>
    <id>TestId_1</id>
    <category term="TermA" />
  </entry>
  <entry>
    <id>TestId_2</id>
    <category term="TermB" />
  </entry>
</root>
```

```xml
<?xml version="1.0" encoding="utf-8"?>
<Root>
    <CreationDate>2022-05-01</CreationDate>
    <A Id="1">
        <Value>Test1</Value>
    </A>
    <B Value="Test2">
        <Id>2</Id>
    </B>
    <Skipped Id="0">X</Skipped>
    <B Value="Test3">
        <Id>3</Id>
    </B>
    <A Id="4">
        <Value>Test4</Value>
    </A>
</Root>
```

--------------------------------

### Install ETLBox JSON Connector

Source: https://www.etlbox.net/docs/streaming-connectors/overview

Methods to install the ETLBox.Json package using various package management tools.

```bash
dotnet add package ETLBox.Json
```

```powershell
PM> NuGet\Install-Package ETLBox.Json -Version 3.10.0
```

```xml
<PackageReference Include="ETLBox.Json" Version="3.10.0" />
```

```fsharp
> #r "nuget: ETLBox.Json, 3.10.0"
```

--------------------------------

### Execute Row Transformation Pipeline

Source: https://www.etlbox.net/docs/transformations/row

A complete example demonstrating a data pipeline using MemorySource, RowTransformation, and MemoryDestination.

```csharp
public class MyRow {
    public int InputValue { get; set; }
    public int Result { get; set; }
}

public static void Main() {
    var source = new MemorySource<MyRow>();
    source.DataAsList.Add(new MyRow() { InputValue = 1 });
    source.DataAsList.Add(new MyRow() { InputValue = 2 });

    var rowTrans = new RowTransformation<MyRow>(
        row => {
            row.Result = row.InputValue * 2;
            return row;
        }
    );

    var dest = new MemoryDestination<MyRow>();

    source.LinkTo(rowTrans).LinkTo(dest);
    Network.Execute(source);

    foreach (var row in dest.Data)
        Console.WriteLine($"InputValue:{row.InputValue} Result:{row.Result}");

    //Outputs
    //InputValue:1 Result:2
    //InputValue:2 Result:4
}
```

--------------------------------

### Install Kafka Connector via Package Manager Console

Source: https://www.etlbox.net/docs/messaging/overview

Use the Package Manager Console in Visual Studio to install the Kafka integration package.

```powershell
PM> NuGet\Install-Package ETLBox.Apache.Kafka
```

--------------------------------

### Duplicate All Rows Example

Source: https://www.etlbox.net/docs/transformations/row-duplication

A complete example showing how to configure RowDuplication to produce 2 copies of each row in a MemorySource.

```csharp
public class MyRow {
    public string Value { get; set; }
}

public static void Main() {
    var source = new MemorySource<MyRow>();
    source.DataAsList.Add(new MyRow() { Value = "A" });
    source.DataAsList.Add(new MyRow() { Value = "B" });

    var duplication = new RowDuplication<MyRow>();
    duplication.NumberOfDuplicates = 2;

    var dest = new MemoryDestination<MyRow>();

    source.LinkTo(duplication).LinkTo(dest);
    Network.Execute(source);

    foreach (var row in dest.Data)
        Console.WriteLine($"Value:{row.Value}");

    // Output:
    // Value:A
    // Value:A
    // Value:A
    // Value:B
    // Value:B
    // Value:B
}
```

--------------------------------

### Install Kafka Connector via .NET CLI

Source: https://www.etlbox.net/docs/messaging/overview

Use the .NET CLI to add the Kafka integration package to your project.

```bash
dotnet add package ETLBox.Apache.Kafka
```

--------------------------------

### Define database table schema

Source: https://www.etlbox.net/docs/relational-databases/dbsource

Example SQL definition for the source table used in the examples.

```sql
CREATE TABLE SourceTable (
    Id INT NOT NULL,
    Value VARCHAR(50) NULL
);
```

--------------------------------

### Define OnInitialization Action

Source: https://www.etlbox.net/docs/introduction/shared

Executes a function when the component starts processing.

```csharp
var component = new RowTransformation<MyRow>(row => row);
component.OnInitialization = () => Console.WriteLine("Component initialized");
```

--------------------------------

### Define XML with Attributes

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Example XML input containing namespaces and attributes.

```xml
<?xml version="1.0" encoding="utf-8"?>
<ns:Root xmlns:ns="http://foo.com">
    <CreationDate>2022-05-01</CreationDate>
    <ns:Element Id="1">
        <ns:Inner Number="1.1">
            <ns:Value>Test1</ns:Value>
        </ns:Inner>
    </ns:Element>
    <ns:Element Id="2">
        <ns:Inner Number="1.2">
            <ns:Value></ns:Value>
        </ns:Inner>
    </ns:Element>
</ns:Root>
```

--------------------------------

### Define XML Input File

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Example XML structure used for POCO mapping demonstrations.

```xml
<?xml version="1.0" encoding="utf-8"?>
<Root>
  <Element>
    <Id>1</Id>
    <Inner>
      <Value>Test1</Value>
      <Number>1.1</Number>
    </Inner>
  </Element>
  <Element>
    <Id>2</Id>
    <Inner>
      <Value></Value>
      <Number>1.2</Number>
    </Inner>
  </Element>
</Root>
```

--------------------------------

### Install Kafka Connector via #r Directive

Source: https://www.etlbox.net/docs/messaging/overview

Use the #r directive in F# Interactive or Polyglot Notebooks to reference the package.

```fsharp
> #r "nuget: ETLBox.Apache.Kafka, latest"
```

--------------------------------

### Track ETL Process Lifecycle

Source: https://www.etlbox.net/docs/logging/extended-logging

Demonstrates starting a process, executing logic, and handling completion or abortion states.

```csharp
var process = LoadProcessTask.Start("ETL Run", "Start ETL");

try {
    // Run your pipeline
    process.End("ETL completed");
} catch (Exception ex) {
    process.Abort(ex.ToString());
}
```

--------------------------------

### Store data using CouchbaseDestination

Source: https://www.etlbox.net/docs/nosql-databases/couchbase

Basic setup for writing data to a Couchbase bucket by linking a source to the destination.

```csharp
var source = new MemorySource<MyRow>();
source.DataAsList.Add(new MyRow { Col1 = 1, Col2 = "Test1" });

var dest = new CouchbaseDestination<MyRow> {
    ConnectionString = "<your-connection-string>",
    ClusterOptions = new ClusterOptions {
        UserName = "user",
        Password = "pass"
    },
    BucketName = "my_bucket"
};

source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### XML Output with Namespace Prefixes

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Example output showing elements qualified with the 'f' prefix.

```xml
<?xml version="1.0" encoding="utf-8"?>
<f:Root xmlns:f="https://www.w3schools.com/furniture">
  <f:table name="Coffee table">
    <f:width>80</f:width>
    <f:length>120</f:length>
  </f:table>
  <f:table name="Dining table">
    <f:width>100</f:width>
    <f:length>200</f:length>
  </f:table>
  <f:table name="Sofa">
    <f:width>400</f:width>
    <f:height>50</f:height>
  </f:table>
</f:Root>
```

--------------------------------

### Define database table and POCO for mapping

Source: https://www.etlbox.net/docs/relational-databases/dbsource

Example schema and class definition used to demonstrate column name resolution.

```sql
CREATE TABLE SourceTable (
    "col_Id" INT NOT NULL,
    "col_Value" VARCHAR(100) NOT NULL
);
```

```csharp
public class MyRow {
    public int Id { get; set; }
    public string Value { get; set; }
}
```

--------------------------------

### Execute Data Flow

Source: https://www.etlbox.net/docs/introduction/simple-flow

Starts the execution of the data flow pipeline.

```csharp
await Network.ExecuteAsync(source);
```

--------------------------------

### Install Kafka Connector via PackageReference

Source: https://www.etlbox.net/docs/messaging/overview

Add this XML node to your project file to include the Kafka integration package.

```xml
<PackageReference Include="ETLBox.Apache.Kafka" Version="latest" />
```

--------------------------------

### Execute Transactional Operations

Source: https://www.etlbox.net/docs/relational-databases/connection-manager

Demonstrates the basic pattern for starting, committing, and rolling back a transaction using a connection manager.

```csharp
var sqlServerConn = new SqlConnectionManager("Data Source=.;Integrated Security=SSPI;");

try {
    sqlServerConn.BeginTransaction();
    // Execute operations within the transaction,e.g. running the ETL: Network.Execute(source);
    sqlServerConn.CommitTransaction();
} catch {
    sqlServerConn.RollbackTransaction();
}
```

--------------------------------

### Insert Data with TableDestination

Source: https://www.etlbox.net/docs/nosql-databases/tables

Basic setup for linking a memory source to an Azure Table destination.

```csharp
var source = new MemorySource<TestEntity>();
source.Data = GenerateDefaultTestData();

var dest = new TableDestination<TestEntity> {
    ConnectionString = "<your-connection-string>",
    TableName = "Movies"
};
source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### XML Output with Root-Level Namespace

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Example output showing the namespace defined only at the root element.

```xml
<?xml version="1.0" encoding="utf-8"?>
<Root xmlns="https://www.w3schools.com/furniture">
  <table name="Coffee table">
    <width>80</width>
    <length>120</length>
  </table>
  <table name="Dining table">
    <width>100</width>
    <length>200</length>
  </table>
  <table name="Sofa">
    <width>400</width>
    <height>50</height>
  </table>
</Root>
```

--------------------------------

### Console Output for Dynamic Element Handling

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Expected output after processing the dynamic element example.

```text
Received Id: 1, Value: Test1
Received Id: 2, Value: Test2
Received Id: 3, Value: Test3
Received Id: 4, Value: Test4
```

--------------------------------

### XML Output with Namespace per Element

Source: https://www.etlbox.net/docs/streaming-connectors/xml

Example output showing the namespace declaration repeated on each table element.

```xml
<?xml version="1.0" encoding="utf-8"?>
<Root>
  <table name="Coffee table" xmlns="https://www.w3schools.com/furniture">
    <width>80</width>
    <length>120</length>
  </table>
  <table name="Dining table" xmlns="https://www.w3schools.com/furniture">
    <width>100</width>
    <length>200</length>
  </table>
  <table name="Sofa" xmlns="https://www.w3schools.com/furniture">
    <width>400</width>
    <height>50</height>
  </table>
</Root>
```

--------------------------------

### Write nodes and relationships with Neo4jDestination

Source: https://www.etlbox.net/docs/nosql-databases/neo4j

Example of using CreateCypherFunc to generate complex Cypher queries that create nodes and relationships based on nested object data.

```csharp
public class Movie {
    public string Title { get; set; }
    public Person[] Actors { get; set; }
}

public class Person {
    public string Name { get; set; }
}

var source = new MemorySource<Movie>();
source.Data = new List<Movie> {
    new Movie {
        Title = "Interstellar",
        Actors = new[] {
            new Person { Name = "Matthew McConaughey" },
            new Person { Name = "Anne Hathaway" }
        }
    }
};

var dest = new Neo4jDestination<Movie> {
    ConnectionString = "bolt://localhost:7687",
    CreateCypherFunc = (movie, _) => {
        var actorCreates = string.Join("\n", movie.Actors.Select((a, i) =>
            $"MERGE (a{i}:Person {{name: '{a.Name}'}})\n" +
            $"MERGE (m)-[:ACTED_IN]->(a{i})"));

        return
$@"MERGE (m:Movie {{title: '{movie.Title}'}})
{actorCreates}";
    }
};

source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### Dynamic Row Validation Example

Source: https://www.etlbox.net/docs/transformations/row-validation

Demonstrates configuring RowValidation with multiple rules and routing valid/invalid rows to different destinations using dynamic objects.

```csharp
var source = new MemorySource();
source.DataAsList.Add(CreateTestData(1, "5", -5.34m));  // Valid
source.DataAsList.Add(CreateTestData(2, "X", 34));      // Invalid (Value1 is not numeric)
source.DataAsList.Add(CreateTestData(3, "5", null));    // Valid
source.DataAsList.Add(CreateTestData(4, "A", null));    // Invalid (Value2 is null)
source.DataAsList.Add(CreateTestData(5, 6, "-7"));      // Invalid (Value2 is not negative)

var trans = new RowValidation();
trans.IgnoreMissingProperties = true;
trans.ValidateColumns = new []
{
    new ValidateColumn() { PropertyName = "Value1", ValidationMethod = ValidationMethod.IsNumberBetween, Parameter1 = 1, Parameter2 = 10 },
    new ValidateColumn() { PropertyName = "Value2", ValidationMethod = ValidationMethod.IsNotNull },
    new ValidateColumn() { PropertyName = "Value2", ValidationMethod = ValidationMethod.IsNegative }
};

var validRows = new MemoryDestination();
var invalidRows = new MemoryDestination();

source.LinkTo(trans);
trans.LinkTo(validRows);       // Valid rows are sent to this destination
trans.LinkInvalidTo(invalidRows); // Invalid rows are sent to this destination
Network.Execute(source);

Console.WriteLine("Valid Rows:");
foreach (dynamic row in validRows.Data) {
    Console.WriteLine($"Id: {row.Id}, Value1: {row.Value1}, Value2: {row.Value2}");
}

Console.WriteLine("\nInvalid Rows:");
foreach (dynamic row in invalidRows.Data) {
    var validationResult = row.ValidationResult as ValidationResult;
    var value2 = (row as IDictionary<string,object>).ContainsKey("Value2") ? row.Value2 : "null";
    Console.WriteLine($"Id: {row.Id}, Value1: {row.Value1}, Value2: {value2}");
    Console.WriteLine("Errors:");
    foreach (var error in validationResult.Errors) {
        Console.WriteLine($" - {error.Reason} (Value: {error.Value})");
    }
}

/* Output
 *     Valid Rows:
Id: 1, Value1: 5, Value2: -5.34
Id: 5, Value1: 6, Value2: -7

Invalid Rows:
Id: 2, Value1: X, Value2: 34
Errors:
 - Number is not between '1' and '10', but expected it to be. (Value: X)
 - Value is not negative, but expected value to be negative. (Value: 34)
Id: 3, Value1: 5, Value2: null
Errors:
 - Value is null, but expected value to be not null. (Value: )
 - Value is not negative, but expected value to be negative. (Value: )
Id: 4, Value1: A, Value2: null
Errors:
 - Number is not between '1' and '10', but expected it to be. (Value: A)
 - Value is null, but expected value to be not null. (Value: )
 - Value is not negative, but expected value to be negative. (Value: )
*/

ExpandoObject CreateTestData(int id, object value1, object value2) {
    dynamic obj = new ExpandoObject();
    obj.Id = id;
    obj.Value1 = value1;
    if (value2 != null)
        obj.Value2 = value2;
    return obj;
}
```

--------------------------------

### Create a DbDestination instance

Source: https://www.etlbox.net/docs/relational-databases/dbdestination

Initializes a DbDestination with a connection manager and target table name.

```csharp
var conn = new SqlConnectionManager("Data Source=.;Integrated Security=SSPI;");
var dest = new DbDestination<MyRow>() {
  ConnectionManager = conn,
  TableName = "DestinationTable"
};
```

--------------------------------

### Basic usage of Neo4jDestination

Source: https://www.etlbox.net/docs/nosql-databases/neo4j

Shows how to map object data to Neo4j nodes using a simple CreateCypherFunc.

```csharp
public class Movie {
    public string Name { get; set; }
    public string Genre { get; set; }
}

var source = new MemorySource<Movie>();
source.Data = new List<Movie> {
    new Movie { Name = "The Matrix", Genre = "Sci-Fi" },
    new Movie { Name = "Inception", Genre = "Thriller" }
};

var dest = new Neo4jDestination<Movie> {
    ConnectionString = "bolt://localhost:7687",
    CreateCypherFunc = (movie, suggestion) =>
        $@"CREATE (m:Movie {{name: '{movie.Name}', genre: '{movie.Genre}'}})"
};

source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### Configure and execute a task instance

Source: https://www.etlbox.net/docs/control-flow/overview

Instantiate a task to apply specific configurations or options before execution.

```csharp
var task = new RowCountTask("demotable") {
    Options = RowCountOptions.QuickQueryMode
};
int count = task.Count();
```

--------------------------------

### Initialize Data Sources

Source: https://www.etlbox.net/docs/transformations/lookup

Create and populate the memory sources for both the primary input and the lookup data.

```csharp
var inputSource = new MemorySource<InputRow>();
inputSource.DataAsList = new List<InputRow> {
    new InputRow { Id = 1 },
    new InputRow { Id = 2 },
    new InputRow { Id = 3 }
};

var lookupSource = new MemorySource<LookupRow>();
lookupSource.DataAsList = new List<LookupRow> {
    new LookupRow { LookupId = 1, LookupValue = "Value1" },
    new LookupRow { LookupId = 2, LookupValue = "Value2" }
};
```

--------------------------------

### Defining Dynamic Objects

Source: https://www.etlbox.net/docs/transformations/distinct

Example of creating an ExpandoObject for use with the Distinct transformation.

```csharp
dynamic row = new ExpandoObject();
row.DistinctCol1 = 1;
row.DistinctCol2 = "A";
row.OtherValue = "Example";
```

--------------------------------

### Initialize Database Connection Managers

Source: https://www.etlbox.net/docs/relational-databases/overview

Instantiate specific connection managers for SQL Server or PostgreSQL to handle database communication.

```csharp
var conn = new SqlConnectionManager("Data Source=myServer;Initial Catalog=myDB;User ID=myUser;Password=myPass;");
```

```csharp
var conn = new PostgresConnectionManager("Host=myServer;Database=myDB;Username=myUser;Password=myPass;");
```

--------------------------------

### DataFrame Output Example

Source: https://www.etlbox.net/docs/specialized-connectors/dataframe

The expected console output after creating and filtering the DataFrame.

```text
Col1      Col2      Col3
1         A         1.1
2         B         2.2
3         C         3.3

Filtered DataFrame:
Col1      Col2      Col3
2         B         2.2
3         C         3.3
```

--------------------------------

### JSON output format

Source: https://www.etlbox.net/docs/streaming-connectors/json

The resulting JSON structure generated by the JsonDestination example.

```json
[
  {
    "Col1": 1,
    "Col2": "Test1"
  },
  {
    "Col1": 2,
    "Col2": "Test2"
  }
]
```

--------------------------------

### Dynamic MergeJoin Example

Source: https://www.etlbox.net/docs/transformations/merge-join

Shows how to use MergeJoin with ExpandoObject for dynamic schema handling.

```csharp
var source1 = new MemorySource();
dynamic left1 = new ExpandoObject();
left1.FirstName = "Elvis";
dynamic left2 = new ExpandoObject();
left2.FirstName = "Marilyn";
source1.DataAsList.Add(left1);
source1.DataAsList.Add(left2);

var source2 = new MemorySource();
dynamic right1 = new ExpandoObject();
right1.LastName = "Presley";
dynamic right2 = new ExpandoObject();
right2.LastName = "Monroe";
source2.DataAsList.Add(right1);
source2.DataAsList.Add(right2);

var join = new MergeJoin(
    (left, right) => {
        dynamic output = new ExpandoObject();
        output.FullName = $"{((dynamic)left).FirstName} {((dynamic)right).LastName}";
        return output;
    });

var dest = new MemoryDestination();
source1.LinkTo(join.LeftInput);
source2.LinkTo(join.RightInput);
join.LinkTo(dest);

Network.Execute(source1, source2);

foreach (dynamic row in dest.Data)
    Console.WriteLine(row.FullName);

// Output:
// Elvis Presley
// Marilyn Monroe
```

--------------------------------

### Configuring DbSource with ExpandoObject

Source: https://www.etlbox.net/docs/introduction/types

Shows how to initialize a DbSource to use ExpandoObject, either explicitly or by relying on the default behavior.

```csharp
DbSource<ExpandoObject> source = new DbSource<ExpandoObject>("sourcetable");
```

```csharp
DbSource source = new DbSource("sourcetable"); // Defaults to ExpandoObject
```

--------------------------------

### Define target database table

Source: https://www.etlbox.net/docs/relational-databases/dbdestination

SQL schema definition for the destination table used in examples.

```sql
CREATE TABLE DestinationTable (
    Id INT NOT NULL,
    Value VARCHAR(50) NULL
);
```

--------------------------------

### Sample NLog Configuration

Source: https://www.etlbox.net/docs/logging/structured-logging

Defines targets and rules for NLog to output structured ETLBox logs to console and file.

```xml
<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      autoReload="true"
      internalLogFile="internal-nlog.log"
      internalLogLevel="Info">

  <targets>
    <target name="logfile" xsi:type="File" fileName="etlbox-log.txt"
            deleteOldFileOnStartup="true"
            layout="${longdate}|${level}|${mdlc:item=taskType}|${event-properties:item=action}|${mdlc:item=taskHash}|${mdlc:item=taskName}|${message}" />

    <target name="logconsole" xsi:type="Console"
            layout="${longdate}|${level}|${pad:padding=20:inner=${mdlc:item=taskType}}|${pad:padding=5:inner=${event-properties:item=action}}|${pad:padding=20:inner=${mdlc:item=taskName}}|${message}" />
  </targets>

  <rules>
    <logger name="*" minlevel="Debug" writeTo="logfile,logconsole" />
  </rules>
</nlog>
```

--------------------------------

### Configure Dynamic Document Writing

Source: https://www.etlbox.net/docs/nosql-databases/cosmos

Initializes a non-generic CosmosDestination to support dynamic data structures like ExpandoObject.

```csharp
var dest = new CosmosDestination() {
    ContainerName = "Movies",
    DatabaseName = "etlbox",
    ConnectionString = "AccountEndpoint=https://<your-account>.documents.azure.com:443/;AccountKey=<your-key>;",
};
```

--------------------------------

### Implementing API Pagination

Source: https://www.etlbox.net/docs/streaming-connectors/overview

Configures pagination logic for web-based data sources using GetNextUri and HasNextUri delegates.

```csharp
JsonSource<MyRow> source = new JsonSource<MyRow>();
int page = 1;
source.GetNextUri = streamMetaData => $"https://api.example.com/data?page={page++}";
source.HasNextUri = streamMetaData => streamMetaData.ProcessedRows > 0;
```

--------------------------------

### Configure Database Connection

Source: https://www.etlbox.net/docs/introduction/simple-flow

Sets up a connection manager for the target database.

```csharp
SqlConnectionManager connMan = new SqlConnectionManager("Data Source=.;Initial Catalog=demo;Integrated Security=false;User=sa;password=reallyStrongPwd123");
```

--------------------------------

### Define Destination Table Schema

Source: https://www.etlbox.net/docs/relational-databases/dbmerge

SQL script to create the destination table used in the merge example.

```sql
CREATE TABLE DestinationTable (
    Key INT NOT NULL PRIMARY KEY,
    Value VARCHAR(50) NULL
);

INSERT INTO DestinationTable (Key, Value) VALUES
(2, 'XXX'),
(3, 'Test - Exists'),
(4, 'Test - Deleted');
```

--------------------------------

### Configure CosmosSource with Account Endpoint and Auth Key

Source: https://www.etlbox.net/docs/nosql-databases/cosmos

Connect using an account endpoint and an authentication key or resource token instead of a full connection string.

```csharp
var source = new CosmosSource<Movie>() {
    AccountEndpoint = "https://<your-account>.documents.azure.com:443/",
    AuthKeyOrResourceToken = "<your-key-or-token>",
    DatabaseName = "etlbox",
    ContainerName = "Movies",
    Sql = "SELECT * FROM Movies"
};
```

--------------------------------

### JSON Input Example for Normalization

Source: https://www.etlbox.net/docs/transformations/row-multiplication

Sample JSON structure containing nested arrays suitable for normalization.

```json
{
  "Invoices": [
    {
      "Id": 1,
      "CustomerName": "Peter",
      "Total": 45,
      "Items": [
        { "Name": "Clock", "Price": 20 },
        { "Name": "Socks", "Price": 10 },
        { "Name": "Shirt", "Price": 15 }
      ]
    },
    {
      "Id": 2,
      "CustomerName": "Mary",
      "Total": 100,
      "Items": [
        { "Name": "Clock", "Price": 29 },
        { "Name": "Dress", "Price": 71 }
      ]
    }
  ]
}
```

--------------------------------

### Process In-Memory Data with Pipeline

Source: https://www.etlbox.net/docs/specialized-connectors/in-memory

Demonstrates a complete ETL pipeline using MemorySource, RowTransformation, and MemoryDestination.

```csharp
MemorySource<MyRow> source = new MemorySource<MyRow>();
source.Data = new List<MyRow>
{
    new MyRow { Id = 1, Value = "Test" },
    new MyRow { Id = 2, Value = "Test" }
};
var trans = new RowTransformation<MyRow>();
trans.TransformationFunc = row => {
    row.Value += row.Id;
    return row;
};
var dest = new MemoryDestination<MyRow>();
source.LinkTo(trans);
trans.LinkTo(dest);
Network.Execute(source);

foreach (var row in dest.Data)
    Console.WriteLine($"Id: {row.Id}, Value: {row.Value}");
```

--------------------------------

### Define Source Table Schema

Source: https://www.etlbox.net/docs/relational-databases/dbsource

SQL definition for the source table used in bulk selection examples.

```sql
CREATE TABLE SourceTable (
    Id INT NOT NULL PRIMARY KEY,
    Value VARCHAR(100) NOT NULL
);
```

--------------------------------

### Initialize a Network

Source: https://www.etlbox.net/docs/introduction/execution

Methods for creating a Network object to manage data flow components.

```csharp
var network = Network.Init(source1);
```

```csharp
var network = new Network(source1, rowTrans, dest1);
```

--------------------------------

### Create Load Process Table

Source: https://www.etlbox.net/docs/logging/extended-logging

Initializes the database table required for tracking ETL process lifecycles.

```csharp
LoadProcessTask.CreateTable(connectionManager, "etlbox_loadprocess");
```

--------------------------------

### LogSection.Execute

Source: https://www.etlbox.net/docs/logging/extended-logging

Wraps a block of code to generate structured START and END log messages for timing and tracking.

```APIDOC
## LogSection.Execute

### Description
Wraps a block of code to generate structured START and END log messages, providing visibility into the timing and result of the execution.

### Method
LogSection.Execute(name, action)

### Parameters
- **name** (string) - Required - The name of the log section.
- **action** (Action) - Required - The code block to execute.
```

--------------------------------

### Basic LogDestination Usage

Source: https://www.etlbox.net/docs/specialized-connectors/log

Demonstrates linking a data source to a LogDestination to output records as JSON.

```csharp
public class MyRow
{
    public int Id { get; set; }
    public string Value { get; set; }
}

var source = new MemorySource<MyRow>();
source.DataAsList.Add(new MyRow() { Id = 1, Value = "Test1" });
source.DataAsList.Add(new MyRow() { Id = 2, Value = "Test2" });

var dest = new LogDestination<MyRow>(myLogger);

source.LinkTo(dest);
Network.Execute(source);

/* Log output:
Record number 1 at 01/01/2026 12:00:00: '{"Id":1,"Value":"Test1"}'.
Record number 2 at 01/01/2026 12:00:00: '{"Id":2,"Value":"Test2"}'.
*/
```

--------------------------------

### Configuring Redis Connections

Source: https://www.etlbox.net/docs/nosql-databases/redis

Demonstrates setting a connection string or reusing an existing ConnectionMultiplexer instance.

```csharp
source.ConnectionString = "localhost:6379";
```

```csharp
var redis = ConnectionMultiplexer.Connect("localhost:6379");
var source = new RedisSource<MyRow> {
    RedisConnection = redis
};
```

--------------------------------

### ODBC Connection Managers

Source: https://www.etlbox.net/docs/relational-databases/connection-manager

Instantiate ODBC-based connection managers. Requires the appropriate ODBC driver to be installed on the system.

```csharp
new OdbcConnectionManager("DSN=myDSN;Uid=myUser;Pwd=myPass;");
```

```csharp
new SqlOdbcConnectionManager("Driver={SQL Server};Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;");
```

```csharp
new PostgresOdbcConnectionManager("Driver={PostgreSQL ANSI};Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;");
```

```csharp
new MySqlOdbcConnectionManager("Driver={MySQL ODBC 8.0 Driver};Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;");
```

```csharp
new MariaDbOdbcConnectionManager("Driver={MariaDB ODBC 3.1 Driver};Server=myServer;Database=myDB;Uid=myUser;Pwd=myPass;");
```

```csharp
new OracleOdbcConnectionManager("Driver={Oracle in OraDB};Dbq=mydb;Uid=myuser;Pwd=mypassword;");
```

```csharp
new SnowflakeOdbcConnectionManager("Driver={SnowflakeDSIIDriver};Account=myaccount;User=myuser;Password=mypassword;Database=mydb;");
```

```csharp
new SparkOdbcConnectionManager("Driver={Simba Spark ODBC Driver};Host=spark.server.com;Port=10000;Schema=default;");
```

```csharp
new AccessOdbcConnectionManager("Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=myDatabase.accdb;");
```

--------------------------------

### Execute via Source Shortcut

Source: https://www.etlbox.net/docs/introduction/execution

Trigger execution directly from a source component.

```csharp
source1.Execute();
```

--------------------------------

### Create Connection Managers for SQL Server and PostgreSQL

Source: https://www.etlbox.net/docs/relational-databases/connection-manager

Instantiate specific connection managers using database connection strings.

```csharp
var sqlConn = new SqlConnectionManager("Data Source=myServer;Database=myDB;User ID=myUser;Password=myPass;");
var postgresConn = new PostgresConnectionManager("Host=myServer;Database=myDB;Username=myUser;Password=myPass;");
```

--------------------------------

### Configure Serilog for ETLBox

Source: https://www.etlbox.net/docs/logging/structured-logging

Initializes a Serilog logger and assigns it to the ETLBox Settings.LogInstance.

```csharp
var serilogLogger = new LoggerConfiguration()
    .Enrich.FromLogContext()
    .MinimumLevel.Debug()
    .WriteTo.Console(outputTemplate: "[{Timestamp:HH:mm:ss} {Level:u3} {taskName}] {Message:lj}{NewLine}")
    .CreateLogger();

Settings.LogInstance = new SerilogLoggerFactory(serilogLogger).CreateLogger("Default");
```

--------------------------------

### Linking Multiple Components

Source: https://www.etlbox.net/docs/introduction/linking

Demonstrates connecting multiple sources and transformations to build a complex data pipeline with conditional routing.

```csharp
dbSource1.LinkTo(rowTransformation);
dbSource2.LinkTo(rowTransformation);
rowTransformation.LinkTo(mergeJoin);
jsonSource.LinkTo(mergeJoin);
mergeJoin.LinkTo(multicast, row => row.IsValid == true);
mergeJoin.LinkTo(xmlDestination, row => row.IsValid == false);
multicast.LinkTo(dbDestination);
multicast.LinkTo(aggregation);
aggregation.LinkTo(csvDestination);
```

--------------------------------

### Map Database Columns to POCO Properties

Source: https://www.etlbox.net/docs/introduction/types

Demonstrates creating a database table and defining a POCO with explicit column mapping using the DbColumnMap attribute.

```csharp
SqlTask.ExecuteNonQuery(@"CREATE TABLE sourcetable (
  Value1 INT NULL,
  Value2 VARCHAR(100) NULL
 )");

public class MyRow {
    public int Value1 { get; set; }
    [DbColumnMap("Value2")]
    public string Col2 { get; set; }
}

DbSource<MyRow> source = new DbSource<MyRow>("sourcetable");
```

--------------------------------

### Wrap Code in a LogSection

Source: https://www.etlbox.net/docs/logging/extended-logging

Executes a block of code while automatically generating START and END log messages for timing and visibility.

```csharp
LogSection.Execute("Custom step", () => {
    SqlTask.ExecuteNonQuery("Inner task", "SELECT 1");
});
```

--------------------------------

### Switching between CSV and JSON sources

Source: https://www.etlbox.net/docs/streaming-connectors/overview

Demonstrates the unified API structure for switching between local file and HTTP-based data sources.

```csharp
CsvSource source = new CsvSource("data.csv");
source.ResourceType = ResourceType.File;

// Switching to JSON API
JsonSource apiSource = new JsonSource("https://api.example.com/data");
apiSource.ResourceType = ResourceType.Http;
```

--------------------------------

### Join with Comparison Example

Source: https://www.etlbox.net/docs/transformations/merge-join

Demonstrates using MergeJoin with a custom comparison function to join two data sources based on an ID property.

```csharp
public class MyRow {
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName { get; set; }
}

public void JoinWithComparisonExample() {
    var source1 = new MemorySource<MyRow>();
    source1.DataAsList.Add(new MyRow() { Id = 1, FirstName = "Elvis" });
    source1.DataAsList.Add(new MyRow() { Id = 2, FirstName = "Psy" });
    source1.DataAsList.Add(new MyRow() { Id = 3, FirstName = "Marilyn" });

    var source2 = new MemorySource<MyRow>();
    source2.DataAsList.Add(new MyRow() { Id = 1, LastName = "Presley" });
    source2.DataAsList.Add(new MyRow() { Id = 3, LastName = "Monroe" });

    var join = new MergeJoin<MyRow>(
        (leftRow, rightRow) => {
            if (rightRow == null)
                leftRow.FullName = leftRow.FirstName + " " + "Unknown";
            else
                leftRow.FullName = leftRow.FirstName + " " + rightRow.LastName;
            return leftRow;
        });

    join.ComparisonFunc = (inputRow1, inputRow2) => {
        if (inputRow1.Id == inputRow2.Id)
            return 0;
        else if (inputRow1.Id < inputRow2.Id)
            return -1;
        else
            return 1;
    };

    var dest = new MemoryDestination<MyRow>();
    source1.LinkTo(join.LeftInput);
    source2.LinkTo(join.RightInput);
    join.LinkTo(dest);

    Network.Execute(source1, source2);

    foreach (var row in dest.Data)
        Console.WriteLine(row.FullName);

    //Outputs:
    //Elvis Presley
    //Psy Unknown
    //Marilyn Monroe
}
```

--------------------------------

### Configure CosmosSource with Connection String

Source: https://www.etlbox.net/docs/nosql-databases/cosmos

Use a standard Cosmos DB connection string to define the source connection.

```csharp
var source = new CosmosSource<Movie>() {
    ConnectionString = "AccountEndpoint=https://<your-account>.documents.azure.com:443/;AccountKey=<your-key>;",
    DatabaseName = "etlbox",
    ContainerName = "Movies",
    Sql = "SELECT * FROM Movies"
};
```

--------------------------------

### Example Error File Content

Source: https://www.etlbox.net/docs/introduction/linking

Shows the expected CSV output format for redirected errors containing exception details and the original record.

```csv
ErrorText,ReportTime,ExceptionType,RecordAsJson
Id must have a value > 0!,2021-03-26 14:41:26.952,System.ArgumentException,"{""Id"":0,""Value"":""-""}"
Id must have a value > 0!,2021-03-26 14:41:27.679,System.ArgumentException,"{""Id"":-1,""Value"":""C""}"
```

--------------------------------

### Cross Join Full Name Example

Source: https://www.etlbox.net/docs/blocking-transformations/cross-join

Combine first and last names from two separate sources to generate all possible full name combinations.

```csharp
public class MyLeftRow {
    public string FirstName { get; set; }
}

public class MyRightRow {
    public string LastName { get; set; }
}

public class MyOutputRow {
    public string FullName { get; set; }
}

var source1 = new MemorySource<MyLeftRow>();
source1.DataAsList.AddRange(new[] {
    new MyLeftRow { FirstName = "Elvis" },
    new MyLeftRow { FirstName = "James" },
    new MyLeftRow { FirstName = "Marilyn" }
});

var source2 = new MemorySource<MyRightRow>();
source2.DataAsList.AddRange(new[] {
    new MyRightRow { LastName = "Presley" },
    new MyRightRow { LastName = "Monroe" }
});

var join = new CrossJoin<MyLeftRow, MyRightRow, MyOutputRow>(
    (left, right) => new MyOutputRow {
        FullName = $"{left.FirstName} {right.LastName}"
    });

var dest = new MemoryDestination<MyOutputRow>();
source1.LinkTo(join.InMemoryTarget);
source2.LinkTo(join.PassingTarget);
join.LinkTo(dest);
Network.Execute(source1, source2);

foreach (var row in dest.Data)
    Console.WriteLine(row.FullName);

// Output:
// Elvis Presley
// James Presley
// Marilyn Presley
// Elvis Monroe
// James Monroe
// Marilyn Monroe
```

--------------------------------

### Using LogDestination with Dynamic Objects

Source: https://www.etlbox.net/docs/specialized-connectors/log

Shows how to use the non-generic LogDestination with ExpandoObject for dynamic data structures.

```csharp
var source = new MemorySource();
dynamic row1 = new ExpandoObject();
row1.Id = 1;
row1.Value = "Test1";
source.DataAsList.Add(row1);

var dest = new LogDestination(myLogger);

source.LinkTo(dest);
Network.Execute(source);

/* Log output:
Record number 1 at 01/01/2026 12:00:00: '{"Id":1,"Value":"Test1"}'.
*/
```

--------------------------------

### Set Up Validation Flow

Source: https://www.etlbox.net/docs/transformations/xml-schema

Configure the data flow components to link the source, validation, and destinations.

```csharp
var source = new MemorySource<MyXmlRow>();
source.DataAsList.Add(new MyXmlRow() { Xml = _validXml });
source.DataAsList.Add(new MyXmlRow() { Xml = _invalidXml });

var dest = new MemoryDestination<MyXmlRow>();
var error = new MemoryDestination<ETLBoxError>();

var schemaValidation = new XmlSchemaValidation<MyXmlRow>();
schemaValidation.XmlSelector = r => r.Xml;
schemaValidation.XmlSchema = xsdMarkup;

source.LinkTo(schemaValidation);
schemaValidation.LinkTo(dest);
schemaValidation.LinkErrorTo(error);
Network.Execute(source);
```

--------------------------------

### Write Simple Data to Excel

Source: https://www.etlbox.net/docs/streaming-connectors/excel

Demonstrates writing a list of POCO objects to an Excel file using MemorySource and ExcelDestination.

```csharp
public class MySimpleRow {
    public int Col1 { get; set; }
    public string Col2 { get; set; }
}

var source = new MemorySource<MySimpleRow>();
for (int i = 1; i <= 3; i++)
    source.DataAsList.Add(new MySimpleRow { Col1 = i, Col2 = "Test" + i });

var dest = new ExcelDestination<MySimpleRow>("ExcelDest.xlsx");
source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### Configure TableSource with Connection String

Source: https://www.etlbox.net/docs/nosql-databases/tables

Uses a standard Azure connection string to initialize the TableSource component.

```csharp
var source = new TableSource<TestEntity> {
    ConnectionString = "DefaultEndpointsProtocol=https;AccountName=account;AccountKey=key;EndpointSuffix=core.windows.net";
    TableName = "Movies"
};
```

--------------------------------

### Configure MongoDB connections

Source: https://www.etlbox.net/docs/nosql-databases/mongo

Connect using either a connection string or an existing MongoClient instance.

```csharp
var source = new MongoDbSource<MyRow> {
    ConnectionString = "mongodb://localhost:27017",
    DatabaseName = "etlbox",
    CollectionName = "movies"
};
```

```csharp
var client = new MongoClient("mongodb://localhost:27017");

var source = new MongoDbSource<MyRow> {
    DbClient = client,
    DatabaseName = "etlbox",
    CollectionName = "movies"
};

var dest = new MongoDbDestination<MyRow> {
    DbClient = client,
    DatabaseName = "etlbox",
    CollectionName = "archive"
};
```

--------------------------------

### Basic RedisSource Usage

Source: https://www.etlbox.net/docs/nosql-databases/redis

Connects to a Redis instance and retrieves values matching a specific key pattern.

```csharp
var source = new RedisSource<MyRow> {
    ConnectionString = "localhost:6379",
    ScanPattern = "user:*",
    ScanCountSize = 1000
};
var dest = new MemoryDestination<MyRow>();
source.LinkTo(dest);
Network.Execute(source);
```

--------------------------------

### Write data to a table using DbDestination

Source: https://www.etlbox.net/docs/relational-databases/overview

Initializes a database connection and a destination component for bulk inserting data into a target table.

```csharp
var conn = new SQLiteConnectionManager("Data Source=mydatabase.db;");
var destination = new DbDestination<MyData>(conn, "TargetTable");
```

--------------------------------

### Configure CosmosDestination for Bulk Inserts

Source: https://www.etlbox.net/docs/nosql-databases/cosmos

Initializes a CosmosDestination component to perform bulk insert operations by default.

```csharp
var dest = new CosmosDestination<Movie>() {
    ContainerName = "Movies",
    DatabaseName = "etlbox",
    ConnectionString = "AccountEndpoint=https://<your-account>.documents.azure.com:443/;AccountKey=<your-key>;",
};
```

--------------------------------

### Basic Component Linking

Source: https://www.etlbox.net/docs/introduction/linking

Standard approach to instantiating and connecting ETLBox components.

```csharp
// Create components
CsvSource source = new CsvSource("data.csv");
RowTransformation rowTrans = new RowTransformation(row => row);
MemoryDestination dest = new MemoryDestination();

// Link components
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);

public class MyRow {
    public int Id { get; set; }
    public string Value { get; set; }
}
```
Tokens:
Raw