DTS for 100 text files

Hello DBA's:

I want to upload 100 text files to a single table on SQL Server 2000.
Records from these text files would be selected on the basis of a where clause.

What would be the best way of accomplishing this? Using DTS, I can do only one file at a time. Is there a faster approach.

Thanks

VivekTill the time you get a better solution you can use some file concatenation utility to join all those files into a single one and do the data load using DTS.

http://www.jddesign.f2s.com/concat-s.htm|||Does this utility join files OR append them one after the other. I want to append the records not join them? Let me know.|||it appends them|||Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?|||sounds like a job for perl (every admins friend) or heaven forbid vbscript.|||You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||Originally posted by Brett Kaiser
Why don't you write a sproc that interogates a directory, finds all the file names, then bcp them in, one by one with a cursor and then archive them after it's loaded...

What format are the files in? Are they the same structure?

Brett ... no way you can accomplish this without a cursor ...

I challenge everyone to make a set based solution for the same

LOL ... am working on a similar procedure ... need to uload files daily of the form LocationCD_Extracttype_yyyymmdd.txt ... tab seperated files .. any ideas other than bcp and dts ..|||It is a one-time event. I have no experience in shell scripting, unfortunately.
Originally posted by rnealejr
You can use dts - using an activex script task using the fso object to find the files you need to load. Or windows shell scripting using bcp (bulk copy). Is this a one time event or is there a potential for more ?|||How about this?

Create 2 DTS packages. An Outer and an Inner Package.

The Outer package has an ActiveX script task the uses the FSO to indentify all the files in a directory, then for each file set the text file connection of the inner package to the path of the text file identified in the Outer package. Then call the Inner package which just has a text file connection and a DB connection with a trasnformdata task.
Loop through all the files repeating the above.

This would be very automated and fairly quick depending on the size of the text files. And actually pretty easy to implement.

Stevesql

DtPicker Format

i have placed one date picker in a Vb form,while i select the value from datepicker the selected value is assigned to a textbox,while selection it is selecting in proper date format but my backend is SQL 2000,in backend it is storing the date as 1/1/1900 .00.00.00 ,so can help why this happens and send me the correct coding for that .

what i found out is while i select date between eg: 1/1/00 to 12/1/00 it will store in a correct format in backend,but if select 13/1/00 it won't store,i think it is taking in yyyy-dd-mm,i had given coding like this
insert into (regdate) values(' " & format(text1.text,"dd/mm/yy") & " ')"

can u help me for this by sending correct coding

thanks

saiju

saijumammen@.gmail.comTry this format

insert into (regdate) values(' " & format(text1.text,"dd-mmm-yyyy") & " ')"|||Dear Saiju
I had this problem , the best way to beat this is to change the date to "dd MMM yyyy" format and store it , Believe me it works

chk = format (dtpicker1.value,"dd MMM yyyy")
This is in a string format
Project it to the insert statement as a string and it works
This is to compliment what madhi has said|||Thank u ,Madhi & Rambi for the assistance for my Datepicker Problem.

This really helped me

Thanks once again

Saiju Mammen

DSN Using Trusted Connection When SQL Authentication is selected

I am trying to help an in-house developer with his database. He has develope
d an Access front-end that links to SQL tables. The tables are linked using
an ODBC DSN with SQL Server Authentication selected. WHen a user opens the d
atabse, they get one of two
error messages, based on the user. THey eith get ODBC Call failed and can no
t go any further, or they get Connection failed, and the SQL Server login di
alog, where they have to uncheck Trusted connection and enter the appropriat
e username and password. W
e want it to just authenticate based on the settings in the DSN without open
ing a logn box.You can trace the calls using ODBC Trace to see exactly why the call
failed. Also, the developer's connection string may be being used and
could be stored in the system table. You'll need to modify this before
distributing the app.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

DSN Using Trusted Connection When SQL Authentication is selected

I am trying to help an in-house developer with his database. He has developed an Access front-end that links to SQL tables. The tables are linked using an ODBC DSN with SQL Server Authentication selected. WHen a user opens the databse, they get one of two
error messages, based on the user. THey eith get ODBC Call failed and can not go any further, or they get Connection failed, and the SQL Server login dialog, where they have to uncheck Trusted connection and enter the appropriate username and password. W
e want it to just authenticate based on the settings in the DSN without opening a logn box.
You can trace the calls using ODBC Trace to see exactly why the call
failed. Also, the developer's connection string may be being used and
could be stored in the system table. You'll need to modify this before
distributing the app.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.