Brtdku

I want to know Stack overflow members thoughts on below scenario based on their experience and understanding.

We get 20-30 millions of records as input (input is usually csv or xls files and can be uploaded to DB if that helps) and we need to process those records and generate different files, which include output files and error files. So, base on some business logic those records are moved to output or error files.

Currently the process in place use SSIS packages and some ETL tool to perform that task. Those pkgs and ETL tool itself takes 5 to 15 min to process, depending upon the input size.

There are number of inputs and number of SSIS packages and ETL programs for them.

We want to replace those SSIS pkgs and ETL programs with some .NET application.
We are concerned about the speed and performance of those .net applications, as usually the I/O is slow in .net.

Or is there any better way to deal with this.

We have replaced our simplest SSIS packages with a .NET console application that imports data with BULKINSERT. The performance is satisfactorily fast.

I personally prefer to use SSIS because of the ease of explaining what the package does to new people.

Every time I use C# components/tasks, I almost always own the package forever and my goal is to hand developed work off so I can work on new tasks.

That might be because I am in the BI environment and we hire database developers and report writers predominately and the .net people we usually have speciallize in web development.

I really taught myself c# in order to pull web services that returned XML and JSON. I found it easier to process as the data was paged and extremely complex by converting to c# classes vs trying to use SSIS XML Source. But now that I have the basics, I am able to learn quickly how to solve more and more problems.

All this being said, I like the envelope of SSIS and use script tasks and components when they are necessary, quicker, and sometimes even when they are more simple.

Although my answer is a little late, we faced the same issue in the past: we wanted to go away from SSIS and do the whole ETL with C# code. But we also wanted to have some kind of Data Flow like in SSIS. I would recommend you the nuget package ETLBox (https://etlbox.net) which basically is a C# replacement for SSIS.

E.g., you would have to do the following to load data from a CSV into a database:

Defina a CSV source

CSVSource sourceOrderData = new CSVSource("demodata.csv");

Optionally define a row transformation:

RowTransformation<string, Order> rowTrans = new RowTransformation<string, Order>(

  row => new Order(row)

);    

Define the destination

DBDestination<Order> dest = new DBDestination<Order>("dbo.OrderTable");

Link your ETL data pipeline together

sourceOrderData.LinkTo(rowTrans);

rowTrans.LinkTo(dest);

Finally start the dataflow (async) and wait for all data to be loaded.

source.Execute();

dest.Wait();