Importing Excel data into a MS SQL Database

[Sk8te]

Hi all,

I need some help here. I am trying to build a small data mart to consolidate too my different database into. What I have is a couple of databases and currently, I can run a script to extract the information I need in an excel file.

What I want to do now is to pump the data into a MS SQL database. I want to store these excel files ina folder and run a schedule to schedule some application (either custom or off the shelf) to pump the data into the data mart. Then move the raw excel file to an old folder.

Also if anyone could advise on how to validate the data have not been pumped in twice....

Thanks for all the help... sorry for asking so much

 

[JayKusch]

Sounds like a good job for DTS. You can accomplish every one of the events you have listed above within a DTS package.

Thanks

J. Kusch

 

[MDXer]

A method we used on a past project was to have a control table that we wrote the File information to File Name, Date it was Processed, Total Number of Records, Records loaded, Records suspended for data issues. Then in the Fact tables we had a column that we keyed back to the Control Table. This was done to both easily detect duplicate data, but also allow either the removal of duplicate data or to back out a specific dataload that may have been recalled do to some data quality issues.



"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck