I need guidance on how best to automate the daily update of a table containing employee info for our company's intranet.
What happens today:
I have automated the first part of this task by scheduling a nightly extract out of our Peoplesoft system. The resulting EXCEL file is then manually imported into the target SQL Server 2008 database table using the Import and Export Data (32-bit) utility pgm, which drops the existing data before importing the new stuff. This is done from my PC, obviously. I then manually verify the load was successful by using the SQL Server 2008 Management Studio and running a query to display the most recently added employee. This table is a critical table providing employee reporting relationships on our (SharePoint) intranet.
While the entire process only takes a minute or two to complete, I'm wondering if this can be automated somehow. Any suggestions?