Post reply

DTS help for accesing ACCESS db.

  • 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

  • September 20, 2007 at 10:50 am

    #735011

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

  • September 20, 2007 at 2:57 pm

    #735074

    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

  • September 20, 2007 at 3:44 pm

    #735083

    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?

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

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