Importing Excel Files Using DTS 1

[sexydog]

hi

I currently have a DTS package to import an excel file.This works fine.My problem now is that i have to read multiple excel files from one directory into the same table and i am required to read only certain columns. How can i create a DTS package that reads in files from a directory and inserts the data into the table. Each excel file has a different name but they all have the same columns...I know that i have to write a script but i am not very good at this...


Thanks
DJ

The early bird always catches the worm....My advice...be a late worm

 

[JayKusch]

Are the excel files always named differently? Give us an idea of the potential file names.

Thanks

J. Kusch

 

[sexydog]

HI

Yes the files are all named different eg.
1. Sales_Region_North.xls
1. Sales_Region_South.xls
1. Sales_Region_East.xls
1. Sales_Region_East_2.xls

but all have the same number of columns eg. all have
Accno,Name,address,total... and i am required to import only certain columns from here eg. only Accno nad Total..

Thanks
DJ

The early bird always catches the worm....My advice...be a late worm

 

[tb]

Does this have to be done within the DTS or can you kick off the DTS from code?

From within the DTS package you can do the following ...

* Add a Global Varaible to the DTS ... this will be the path where your excel files reside ... and you will pass the parameter to the DTS when you run it.

* Add an Active X script task - Here you will get all the excel files within the path specified by the global parameter with the FileSystemObject. (If you do not move these files after import ... you'll need to store the names somewhere to cross reference that so you don't import them more than once).

* When you have all the files that have not been imported already... depending on how you import ...
Say you are using the Text File (Source) connection you will the set the DataSource property from the Axtive X Task ... like this
DTSGlobalVariables.parent.Connections("Import").DataSource = FilePath & FileName
If you use the Data Transformation task to get the data across you will then set the Source Object Name property of the task... like this
DTSGlobalVariables.parent.Tasks("DTSTask_DTSDataPumpTask_1").Properties("SourceObjectName").Value = FilePath & FileName

* Now you have to execute the task ... tried in the Active X Script but doesn't seem to work ... within VB it can easily be done
VB CODE:
========
objPackage.Tasks("DTSTask_DTSDataPumpTask_1").Execute objPackage, objEvent, objLog, dtsresult

Seems that I've just added more questions than a proper solution .... If you are interested in the VB code ... let me know

I was standing in the park, wondering why frisbees got bigger as they came closer... then it hit me!

 

[sexydog]

HI

Thanks for the help...


DJ

The early bird always catches the worm....My advice...be a late worm