Note: I have more than one client, so my progress on this project will go slower than if it was the only thing that I was doing.
Step 1: User clicks a button and it sucks up a file, and imports it into expense reports
Sub step: Sucking up the file
It is interesting to find out that importing a CSV file into SQL server with quotes (“) as a text delimiter is NOT easy at all. It requires creating a SSIS (sql server integration services) package, and executing it from within T-SQL. This is not an easy task if you are trying to do everything with BIDS 2005 and don’t have the SSIS package installed.
I ran into so many road blocks with this one I decided to come back to it later and skipped on to the part where the data is already in some kind of processing table and needs to be imported (processed).
Sub step: processing the data
First, make sure the user isn’t copying over existing transactions.
Second, when the data is in the “processing table” where it will stay for ever, start matching up employee names with numbers, validating dates and so on
Third, insert expense transactions
Fourth, update the processing table with the corresponding expense data primary key
Fifth, when user updates the expense item with a project number, give that back to the processing table so we can see the full round for reconciliation later.
This is what I’ll be working on over the next few days.
Cheers!