DTS Wizard and Dynamic change of Access database

[Bill Lista]

I frequently have the need to import data from MSAccess into SQL Server 2000.
I use the DTS wizard to create the package and save it to the server. It
is necessary for me to map all the columns because either the column names
are different, or due to hyphens vs. underbars, it does not automatically
map correctly. The problem is that the MSAccess source databases are not
usually in the same file location, or they have different names, but the
destination SQL database and structure is always the same. The MSAccess table
structure is also the same and contains about 135 tables, it just does not
always reside in the same file location. Question, is there a way to easily
change in DTS what the MSAccess database name (file name) is and simply change
that value to run a new import?

[Euan Garden]

Darren has an example of dynamically changing connections are runtime on
http://www.swynk.com/friends/green.

-Euan

"Bill Lista" <lista_bill@us.arthurandersen.com> wrote in message
news:3acca996$1@news.devx.com...
>
> I frequently have the need to import data from MSAccess into SQL Server
2000.
> I use the DTS wizard to create the package and save it to the server. It
> is necessary for me to map all the columns because either the column names
> are different, or due to hyphens vs. underbars, it does not automatically
> map correctly. The problem is that the MSAccess source databases are not
> usually in the same file location, or they have different names, but the
> destination SQL database and structure is always the same. The MSAccess
table
> structure is also the same and contains about 135 tables, it just does not
> always reside in the same file location. Question, is there a way to
easily
> change in DTS what the MSAccess database name (file name) is and simply
change
> that value to run a new import?