How to import a bunch of files?

I have a large number of text files that I want to import into a database. I
have created a DTS package that knows how to import and transform the data
so that it can be imported but that seems to be hard coded to a particular
file. Doing this by hand 18 thousand times is not something I want to do. Is
there a command line interface that I can call from either DOS or WSH to
import all of this data? I'm thinking that something like
for %%f in (*.txt) do <import %%f>
would be great. Anyone have a suggestion on how this could be done?
Richard Lewis Haggard
Looping, Importing and Archiving
http://www.sqldts.com/default.aspx?246
For SQL2005 you can use the ForEach Loop file enumerator
Looping over files with the Foreach Loop
http://www.sqlis.com/default.aspx?55
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>I have a large number of text files that I want to import into a database.
>I have created a DTS package that knows how to import and transform the
>data so that it can be imported but that seems to be hard coded to a
>particular file. Doing this by hand 18 thousand times is not something I
>want to do. Is there a command line interface that I can call from either
>DOS or WSH to import all of this data? I'm thinking that something like
> for %%f in (*.txt) do <import %%f>
> would be great. Anyone have a suggestion on how this could be done?
> --
> Richard Lewis Haggard
>
|||I ended up doing it with a dtsrun package called from a pair of batch files.
In this case, c:\StockWizPro98\OUTPUT is the destination directory that
receives periodic updates and data drops. I decided to make a temp directory
that will have a single file in it and to create a DTS package that knows
how to import data from it. The
first batch file iterates through all of the input files, copies and renames
them to what the DTS package is looking for and calls the package to import
the data. Simple.
1.bat
pushd c:\StockWizPro98\OUTPUT
for %%f in (*.txt) do call 2 %%f
popd
2.bat
pushd c:\StockWizPro98\OUTPUT\temp
copy /y ..\%1 temp.txt
dtsrun /s (local) /n StockImport /e
popd
Richard Lewis Haggard
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:%23aBh2ff%23FHA.740@.TK2MSFTNGP12.phx.gbl...
> Looping, Importing and Archiving
> http://www.sqldts.com/default.aspx?246
> For SQL2005 you can use the ForEach Loop file enumerator
> Looping over files with the Foreach Loop
> http://www.sqlis.com/default.aspx?55
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Richard Lewis Haggard" <HaggardAtWorldDotStdDotCom> wrote in message
> news:eYhbjZf%23FHA.1032@.TK2MSFTNGP09.phx.gbl...
>