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...
>

How to I load XML into SQL Server 2000

I feel like I must be missing something because there
doesn't seem to be a simple solution to this problem. I
have an XML document and a DTD document and I want to load
this information into SQL Server.
I try to use DTS import but I keep getting the error
message "SQLOLEDB must be specified as the data provider".
Am I asking too much here?Bob: This can be accomplished using OPENXML in SQL 2000:
http://www.sqlxml.org/faqs.aspx?faq=39.
Jay Nathan
http://www.jaynathan.com/blog
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:10ca01c4e54a$cdc35e10$a401280a@.phx.gbl...
> I feel like I must be missing something because there
> doesn't seem to be a simple solution to this problem. I
> have an XML document and a DTD document and I want to load
> this information into SQL Server.
> I try to use DTS import but I keep getting the error
> message "SQLOLEDB must be specified as the data provider".
> Am I asking too much here?

How to I load XML into SQL Server 2000

I feel like I must be missing something because there
doesn't seem to be a simple solution to this problem. I
have an XML document and a DTD document and I want to load
this information into SQL Server.
I try to use DTS import but I keep getting the error
message "SQLOLEDB must be specified as the data provider".
Am I asking too much here?
Bob: This can be accomplished using OPENXML in SQL 2000:
http://www.sqlxml.org/faqs.aspx?faq=39.
Jay Nathan
http://www.jaynathan.com/blog
"Bob Smith" <anonymous@.discussions.microsoft.com> wrote in message
news:10ca01c4e54a$cdc35e10$a401280a@.phx.gbl...
> I feel like I must be missing something because there
> doesn't seem to be a simple solution to this problem. I
> have an XML document and a DTD document and I want to load
> this information into SQL Server.
> I try to use DTS import but I keep getting the error
> message "SQLOLEDB must be specified as the data provider".
> Am I asking too much here?

How to i get all the data from database within CD?

In my company i have one requirement.i have a database in sql server and i
have one report generation application to connect that database an it will
generate reports.for this client machine must have sql server.now my client
need data and application in one cd .he want to see the reports using that
cd.without sql server how can we access that database.is it possible ?
hi,
MOHAMED NASEER wrote:
> In my company i have one requirement.i have a database in sql server
> and i have one report generation application to connect that database
> an it will generate reports.for this client machine must have sql
> server.now my client need data and application in one cd .he want to
> see the reports using that cd.without sql server how can we access
> that database.is it possible ?
you can put a SQL Server/MSDE database on a CD (the like, as later
explained), but SQL Server/MSDE must be present and installed on the target
pc in order to acces that database and related data..
storing a database on CD support can be performed using sp_create_removable
system stored procedure, but this will always produce a 3 files set database
at least...
1 file storing the actual database data (that can be stored on the CD
support)
1 file for the transaction log (that can be stored on the CD support)
1 file for system tables that must stored in a read/write hosting area...
usually the targed pc hard disk..
the database will be of course a readonly database but a part of it, the
data file storing the system tables, must of course be read/write in order
to allow security features like granting database access rights and
permissions..
that's to say, a database can not be totally self contained in a CD-ROM and
more, before using it, it must be attached on to the target database via
sp_attach_db system stored procedure and detached via sp_detach_db before
ejecting the CD (the database will be marked as suspect as unavailable if
the detach operation is skipped)
further info about the sp_create_removable stored procedure and related
synopsis can be found at
http://msdn.microsoft.com/library/de...es_03_92hx.asp
and
http://msdn.microsoft.com/library/de...ca-cz_2e79.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply