The original code was using a Execute SQL Task in the Control Flow to execute all of the stored procedures.
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.
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.
Over the last year or so i have worked on 3 different projects where we had to load data into a SQL Server database. There are many options for doing this from straight .Net to SSIS and more. The next 3 blog posts relay my experiences and the reasons for each choice.
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…)