I'm looking for an example of a simple data flow in C# without relying on SSIS or external libraries such as Rhino-ETL (which is a very good library, BTW). Requirements: One arbitrary ADO .Net data source One arbitrary ADO .Net data destination Does not require entire dataset to be loaded into memory at once, so that it can handle arbitrarily large data sets. It would need to rely on a buffer of some sort, or "yield return" like Rhino ETL takes advantage of. Uses bulk insert (i.e. SqlBulkCopy) Minimal transformation. No lookups, no merge joins. Multi-threaded is not necessary if single threaded can do the job. Another way of stating the question ... how does Rhino ETL do this, but without all the abstractions and inherited classes, and without the quacking dictionary? I would like to see it in a simple non-abstract class. And yet another rephrasing of the question: I'm looking for the fundamental example of taking a data flow output of a "select" query, and bulk inserting it at 10,000 or 50,000 records at a time to a destination without loading the entire result into memory, which could potentially exceed available RAM.

Reputation: 999

doing a simple SSIS-style data flow in c# without any external libraries

I'm looking for an example of a simple data flow in C# without relying on SSIS or external libraries such as Rhino-ETL (which is a very good library, BTW).

Requirements:

One arbitrary ADO .Net data source
One arbitrary ADO .Net data destination
Does not require entire dataset to be loaded into memory at once, so that it can handle arbitrarily large data sets. It would need to rely on a buffer of some sort, or "yield return" like Rhino ETL takes advantage of.
Uses bulk insert (i.e. SqlBulkCopy)
Minimal transformation. No lookups, no merge joins.
Multi-threaded is not necessary if single threaded can do the job.

Another way of stating the question ... how does Rhino ETL do this, but without all the abstractions and inherited classes, and without the quacking dictionary? I would like to see it in a simple non-abstract class.

And yet another rephrasing of the question: I'm looking for the fundamental example of taking a data flow output of a "select" query, and bulk inserting it at 10,000 or 50,000 records at a time to a destination without loading the entire result into memory, which could potentially exceed available RAM.

Upvotes: 1

Answers (2)

Steve Homer

Reputation: 3922

Far from a complete answer I'm afraid.

You can "page" the results of an arbitrary select query within .Net using one or more of the techniques outlined here.

http://msdn.microsoft.com/en-us/library/ff650700.aspx

This should allow you to chunk up the data and avoid RAM concerns.

Alternatively - if your existing SSIS packages are simple / similar enough, then it could be worth while to have a look at generating the SSIS packages automatically based on a template. For example I'm maintaining 100+ packages that are automatically generated by a small c# application using the EzAPI API for SSIS.

Upvotes: 1

William Salzman

Reputation: 6446

It looks like you want to learn how an etl program works to increase your knowledge of programming. Rhino ETL is an open source project, so you can get the source here:

https://github.com/ayende/rhino-etl

and see exactly how they do it. There are also other ETL packages that are Open Source so you can see the way that they do things differently. For example talend source can be found at:

http://www.talend.com/resources/source-code.php

Of course, if you are trying to write your own code for commercial use, you will not want to see the source code of others, so you will need to come up with your process on your own.

Hope this helps you!

Upvotes: 1

doing a simple SSIS-style data flow in c# without any external libraries

Answers (2)

Related Questions