I get 25 Fixed length field Text file extracts from our production system, which I then import into an SQL server database for our CRM and MIS systems. This is currently done with the DTS package system from SQL server 2000 which is then exported into a VB6 program.
My Managers have been on selling my system to other companies, who then require me to modify it to their requirements. I inherited this system from my predecessor, and it was never designed to have the potential to be modified to the extent the other companies want. So these modifications take way longer than they should.
I am looking for a replacement which is impressive, scalable, easy to maintain/modify, and one that I can present to the other companies IT departments so they are willing to take over their own maintenance after the initial modifications.
Also, our company is probably going to be taken over in October(pending shareholder votes) and I want to have something that will show that I have some sort of skills and that Im not just a Uni student looking after a legacy system.
The main things my current system does, that I want the new system to do better:
- The text files have a Date extension (MIS001.20090722), my system uses this date for the snapshot that its loading. Because the Text file names are not static, I have to modify the VB output from the DTS to handle this. There is a possibility to change the text file names to static ones (MIS001.txt), not straight forward, but possible.
- The default blank date is 99/99/9999, I use the VBScript feature to see if the text = that, and I change it to 31/12/9999 to store it in a date field.
- I would like an easy way to change the database that it is pointing to. So I can go between Test_DB and Prod_DB without having to go through the code and change all the references to a variable. Also, so I can clone the program and point it to anotherCompany_DB for onselling.
- There is some data that gets put into staging-tables and then its retrieved with views, because the data comes from several of the files.
- Ability to export to an executable would be a bonus for the smaller companies where we continue to do their maintenance
- It is automated daily and is triggered by the text files arriving after being ftp'd from the host.
- some of the data gets modified.
The new system needs to be free, or included with SQL server 2000 or 2005 (SSIS?) so we can sell/give it to the other companies to do their own maintenance. Writing it in .Net is a possibility as well (except I have no Idea how to go about DB concurrency and transaction control).
I have tried to research this a bit, but I'm not sure what to ask Google. So i keep getting irrelevant results.
So, If you're still with me after all my blabbing on, I'm open to suggestions.