SQL Server Performance Forum – Threads Archive

DTS Import Question

Hi all, I’m relatively new to sql server so please bear with me. At present i have a large number of Access databases consisting of the same tables (different data). I want to merge a couple of these tables so that they are stored into one table on SQL Server 2000.
I was going to write an app in VB.net to do this but then thought that it may be better to use the DTS feature. Is it possible to import from multiple sources into the one destination. i.e. loop through the Access DB’s and import data from just the table named [PMI] into a PMI table already created on the SQL server? If so, how would i go about doing this? Any help on this matter would be most appreciated. Thanks in advance Grant

I think it is not possible to import from various MDBs, as you need to specify one .mdb file at a time. What you can do is create individual tables and use the corresponding Access dbs to import.
For the ease of operation simply follow the DTS wizard option using SQL enterprise manager –> Tools –> Wizards. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the quick response. Is there not anyway to create a script that loops through the databases and can call a DTS package with a variable source database which is changed dynamically? May just have to go back to looking at the VB.Net route. Thanks for your help. Grant

I had this issue
As Satya said you will not be able to create DTS for several MSAccess. However, you can save the DTS into VB Script and change it. But take my word IT IS VERY Difficult.
I think best option is to create vb application with out a DTS

dineshasanka, Thanks for the advice, i had just come across a similar post on another fourm about altering the DTS code. It looks like it would be far simpler to write a quick program to do what is required. Thanks for your help.

Great, can you paste the link please

Dineshasanka, My mistake, it was on this forum. It doesn’t mention about loops but does mention parameterizing the VBcode. I seen this and wondered about altering the vb code to incorporate a loop for my purposes. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=8147 apologies if my previous post was a bit misleading and this isn’t of any use to you. Thanks again.

Am I missing something on this. We should be able to create a DTS for this particulat task. You should be able to create different connection cans and creating one destination can. Meaning if there are two source access databases then create two connections one for each source. Then you will have destination as one connection. Between these trhee connections you should be able to create transformation tasks. Friends, correct me if I am wrong or if I am out of track

]]>