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.
The solution was to forget SSIS and just code this up in .Net. The .Net code would read the header from the file and get the column names, then it would determine how to handle each column and build the SQL. Then it was a straight ADO.Net execute of the SQL.
The performance was fine and there were no unexpected issues.
And there was a funny note. The client eventually got me talking to the DBAs about how the app worked. When they found out that there was no SSIS, they said “That’s great because we don’t even have SSIS on the production servers”. I don’t know why no one told me this during the build process as i certainly asked lots of questions like “And is the SSIS in the current app ok?”. In the end, they were happy i got rid of SSIS.
