automated Import of a bunch of Access files to SQL2K

[Glowworm27]

Hi all.

I have a DTS package that reads a folder and looks for *.mdb files,

then it should get the first file, Dynamicaly set the Access Connection Object with the filename, then import the 2 tables I want out of the Access mdb, into SQK 2k database. Then after it has imported the data, delete the file, then loop back around to the beginning, and grab the next filename in the folder, and repeat the import process.

I have done this successfully when importing a Text, or csv file but for some reason once it has finished importing, the Access database stays open, and I cannot delete the file. Is there anyway to close the connection to that database after importing it?

Thanks in advance.

George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips

 

[Glowworm27]

I figured out my problem. The problem was that in the connection object has a setting called resuable and by default is set to -1 or false, this is not accessable from the designer, but you can create an activex script to set the value to 0 or true. This way I was able to get a filename, use the dynamic object and set the datasource to the connection object to the mdb file, then run the transform data task, to import the data, then set the reusable to 0, and the datasource to "" (empty string) then I created a for next loop in an activex task to count to 10000000, (basicaly wait a few seconds) then I was able to delete the file after that. Then loop back around, and repeat the process untill all of the files were gone.



George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips