Hello everyone,
I've been working with MS SQL servers and T-SQL for a while, but never with DTS. I think I have to take the plunge now, but I need major help on this first time.
I have to import data into an existing table in SQL2k server from single or multiple Excel 2002 files (each with only one worksheet). The Excel files will be put in a shared directory on a network by users. The DTS package will have to periodically (at least twice during office hours) import all the file(s) in the directory at the time into a table, then move the processed file to another directory.
I also need to take the first 2 characters of the filename (XXyyyymmdd.xls) and put them in the table along with the data in Excel file to identify the branch which the data comes from.
I've only managed to create a dumb DTS package that import an Excel file (fixed name) into a table with no data transformation (I need to convert a field from ddmmyyyy to Smalldatetime) along the way, no file moving, no scheduling. So, any help or suggestion would be greatly appreciated.
Thank a bunch in advance,
Patrick
I've been working with MS SQL servers and T-SQL for a while, but never with DTS. I think I have to take the plunge now, but I need major help on this first time.
I have to import data into an existing table in SQL2k server from single or multiple Excel 2002 files (each with only one worksheet). The Excel files will be put in a shared directory on a network by users. The DTS package will have to periodically (at least twice during office hours) import all the file(s) in the directory at the time into a table, then move the processed file to another directory.
I also need to take the first 2 characters of the filename (XXyyyymmdd.xls) and put them in the table along with the data in Excel file to identify the branch which the data comes from.
I've only managed to create a dumb DTS package that import an Excel file (fixed name) into a table with no data transformation (I need to convert a field from ddmmyyyy to Smalldatetime) along the way, no file moving, no scheduling. So, any help or suggestion would be greatly appreciated.
Thank a bunch in advance,
Patrick