DTS help for accesing ACCESS db.

Question

Post reply

DTS help for accesing ACCESS db.

ishaan99

SSCertifiable

Points: 6261
More actions
September 20, 2007 at 9:45 am

#374314

Has anyone worked on a similar problem before. I have a table which has list of all access databases. Each time one database need to selected and that has to be set as connection to access from which data wil lbe imported to sql server env. I see this is possibl;e thru an ASP scripst but has anyone created this type fo DTS before? Any help on thsi will be greatly appreciated.
TIA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

One question first are they all the same table and layout only thing is the connection changes?

ishaan99 SSCertifiable Points: 6261 More actions · Answer 2

Exactly all databases have same table and table has same structure across all dbs. all i need is to pick one db at a time from say one temp tabl which has all db names then set that as a global variable to make a db connectin to access and then import teh data to sql server. Thanks

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

you're on the right track - here's a rough checklist:

set up the global variable to store the filename (the Access DB name).
set up a "static" database connection to one that would be your "guinea pig"
create the steps needed to import that one file. Make sure you take advantage of the work-flow "on-success/On-failure/on-completion" items to get the process to fire in a predictable way.
add a "dynamic properties" task to the beginning of the job. Use it to set the name of the DB.

You now have the functional DTS. At this point, I would tend to navigate towards automating the loop by using something like a old-style batch file, using the DTSrun utility. I find you get the best control and fastest dev out of that. I also think it runs substantially BETTER than multiple XP_cmdshell type commands.

Batch command would be something like:

For %%a in (<directory name>\*.mdb) DO DTSrun .......

DTSRUN takes a fair amount of parameters - do DTSRUN /? at a command prompt to get a better feel.

I know - old-school. But it does seem to work quite a bit better.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?