I will be going to work for a new client next week and I understand the first task is to revamp their current importation tool. So I am looking just for general input from the DBA community here.
It appears that a previous developer devised an importation tool using Cold Fusion as the GUI (for staff use) and then all of the active coding is done using nested Stored Procs. The required task is, they have to import data that comes in once a month, but in very different formats (Access, text files, FoxPro, Excel). However, these files are fixed, so one file does not look different than the previous one.
What happens is, when they go to load data, all of the resources are tapped because the SS2K base is running the sprocs that are inspecting, cleaning, forming, and importing the data. Every file imported only represents a few hundred rows of data, so we are not talking about a lot of data here. I think the “bogging down” they describe is obviously, occurring in the data preparation phase and not the actual importation.
I am thinking about designing an Access base (to replace the CF and T-SQL tool now in use) that will perform all of the functions using tailored VBA modules for each format and then build temp tables for export – then let the SQL Server import it in. I am just looking for an overall approach, so any suggestions, input, or stories of previous experiences would most welcome!
Thanks!
<== Some people say they are afraid of heights. With me its table widths. ==>
It appears that a previous developer devised an importation tool using Cold Fusion as the GUI (for staff use) and then all of the active coding is done using nested Stored Procs. The required task is, they have to import data that comes in once a month, but in very different formats (Access, text files, FoxPro, Excel). However, these files are fixed, so one file does not look different than the previous one.
What happens is, when they go to load data, all of the resources are tapped because the SS2K base is running the sprocs that are inspecting, cleaning, forming, and importing the data. Every file imported only represents a few hundred rows of data, so we are not talking about a lot of data here. I think the “bogging down” they describe is obviously, occurring in the data preparation phase and not the actual importation.
I am thinking about designing an Access base (to replace the CF and T-SQL tool now in use) that will perform all of the functions using tailored VBA modules for each format and then build temp tables for export – then let the SQL Server import it in. I am just looking for an overall approach, so any suggestions, input, or stories of previous experiences would most welcome!
Thanks!
<== Some people say they are afraid of heights. With me its table widths. ==>