I have some files I need to load and have learned how to create a DTS package to do so. The problem is that the file path changes every day according to the date.
I can do it usine MS Access but this doesn't meet the time or performance constraint. I would like to be able to call a DTS package while giving it the path and file name to download. An example of this, something I can do from Access 2000 would be fantastic.

I've tried the bulk copy route but it won't do but bulk copy won't recognized the carriage return as the end of the record.

Thanks!

Are you looking for something like this

How can I change the filename for a text file connection?
(http://www.sqldts.com/default.aspx?200)

Also if you really wanted you could simply have the filename be a
global variable and fire your package using DTSRUN along with the /A
switch.


Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
www.konesans.com - for all your consultancy needs

Allan,
The example at the URL does look like exactly what I asked for, however I'm not sure I know how to use it (plus it implies it's for a SQL Server version beyond 2000). However, to let you understand why I can't take advantage of that, let me say that I have about six years of Access and reasonable but not expert VBA skills. My experience with DTS is limited to what can be create with the DTS import wizard. I did manage to get the wizard to make a procedure with the sub Main and all that, but nothing happened when I ran it from Access 2000, it compiled, and no errors were thrown so it's hard to know what the problem is.
I've created the *.dts file you put on your hard drive and have found code to execute it from Access VBA. I've also managed to create a package and leave it in the local package folder within Enterprise Manager. And I've gone to the diagram within the package and found the place where the file-to-import path/filename is.
Maybe part of what I need to know is what to replace the path with so that the package knows to use the variable that I pass to it. The DTSRun route with the /A sounds the most promising of the two options you've discussed, especially if I can launch DTSRun from the SQL Analyzer via pass-thru query. I look forward to your response. Thanks!

--

Please see my reply to your other exact same post titled "FilePath variable"



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"TomTait747" <TomTait747 (AT) discussions (DOT) microsoft.com> wrote

Server version beyond 2000). However, to let you understand why I can't
take advantage of that, let me say that I have about six years of Access and
reasonable but not expert VBA skills. My experience with DTS is limited to
what can be create with the DTS import wizard. I did manage to get the
wizard to make a procedure with the sub Main and all that, but nothing
happened when I ran it from Access 2000, it compiled, and no errors were
thrown so it's hard to know what the problem is.
package and leave it in the local package folder within Enterprise Manager.
And I've gone to the diagram within the package and found the place where
the file-to-import path/filename is.
route with the /A sounds the most promising of the two options you've
discussed, especially if I can launch DTSRun from the SQL Analyzer via
pass-thru query. I look forward to your response. Thanks!