August 19, 2003 at 10:35 am
I have a DTS package that takes the structure from an Access database and pumps it into SQL Server. it's really not too complicated but there are a lot of tables. I have a lot of Access MDB connection objects. This is only temporary where I run the update daily so that the information can be used through the sql server connection rather than the slow and often cumbersom odbc Acccess one. The data is then normalized into our develompent database where we are nearing the completion of our new system. I've been running the update manually but now my boss would like it to occur automatically. How could I script it so that I could modify this? The db currently points to a backup I keep on my C drive, if I run this dts from my co-workers computer I found out that he has the have the db in his local c:\backups path as well, is it as simple as creating this directory on the server and copying the .mdb before the Job runs?
Thanks for you help in advance...
-Francisco
-Francisco
August 20, 2003 at 3:16 am
Why cant you create an ftp step to run and copy the file before the transformation step.
Another way would be to have an activex script and use the Filesystem object and do any file manipulation in the script.
Eg Dim objfso
Set objfso=CreateObject"Scripting.FileSystemObject")
theis will allow you to do
Objfso.filexsists(filename)
objfso.CreateFolder(foldername)
etc
August 20, 2003 at 8:26 am
Another possible option (although I don't recommend having an automated production run use a developer's PC) would be to use UNC notation (eg. use \\gslnt8456\c$\firstlevel\secondlevel\input.mdb instead of c:\firstlevel\secondlevel\inout.mdb ). By using UNC the package will always point to the correct connection instead of the local mappings of the box you are running on. Note that you will need to adjust the security on the box(es) to allow the mapping in the first place.
Hope this helps.
August 21, 2003 at 9:25 am
Thanks for the suggestions, initially I thought of using vbscript in order to simply just move the .mdb file over... but thought it might be too much overhead to simply move a file. so instead I wrote it up as a sql statement, probably not as efficient, but [:S]
so this is how it works...
xp_FileExists for .mdb if true then xp_cmdshell copy .mdb to new .mdb location
run the rest of the dts packages....I'd love to be able to if on error return the approriate error from the dts packages but for now I have a generic one that reads "Failed dts package 1, please run mannually" [:S]
if anyone thinks this is not a good solution or if I should revisit the vbscript solution please feel free to post. I took the easiest route for me but that may not necessarily be the best...
Thanks in advance for your input.
-Francisco
-Francisco
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply