ETL – SSIS Step 2

The last interesting step here involves a number of calls to stored procedures that were not rewritten yet.

The original code was using a Execute SQL Task in the Control Flow to execute all of the stored procedures.

(more…)

ETL – SSIS Step 1

Lately i was working on project where the current ETL process was approaching 8 hours run time. There were a few critical tables to had over 100,000,000 rows to load that were bogging down the system. The entire process was done by a ton of SQL Stored Procedures that were simply executed by an SSIS package. (more…)

ETL – Straight .Net

This past winter i was working on a project where i used my Access Converter to turn a Microsoft Access application into .Net.  This ended up as a WPF smart client app.

The Access app was using VBA to execute two SSIS packages.  When i dug into the packages i found that they were quite simple with just straight SQL. More importantly, i found the data they were loading was not in a fixed format. The data came in as flat files and from time to time new columns would be added.  Hm.

(more…)

ETL – Windows Service

I worked on a financial application recently where there were importing data from 8 different databases nightly into a single database. The data became available at different points during the night and had lots of detailed processing around each element.  And they never knew how much data was coming in. Plus there was processing for different business segments that had to be done too. There current solution took over 12 hours each night.

(more…)

Using Excel in ETL Processes

Using Excel in ETL Processes

Microsoft Excel is a very popular tool among users, so it’s common to find Excel files when implementing extraction, transformation, and loading (ETL) processes. After I explain the reasons why users create Excel files and where you might find them, I’ll talk about some common problems that you might encounter when dealing with them in ETL processes, such as problems with drivers, hidden data, data types, certain transformations, separators, and worksheet variants. I’ll also present the recommended solutions for these problems. (more…)