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

DTS completion time jump

Hi,
I have a DTS package that runs 4 other packages on a nightly basis. This
package usually takes about 2 hours to run, but ocasionally it jumps to
around 5 hours to complete. There have not been any changes to the
parameters or coding. It is the same process that causes this each time
which is a cursor run strored procedure. There have not been any huge jumps
in the number of records in the table or changes to the source data that
would cause the cursor to take longer to run through. This seems to be
intermittent, I can not easily check to see if something else in the network
on that server is happening since it is a customer site. Would you think
that something outside of the package is affecting this since it only happen
s
once in a while (about once a w or so), like I mentioned - backups, serve
r
processes, other jobs etc.? I just don't know where to look if I have not
changed anything within my package.
Thanks,
PatriceThe are many reasons why performance can change over time. One is job
contention, where multiple jobs are running at the same time. The following
will dump a listing of jobs, when they started and ended out of the
msdb.dbo.sysjobhistory system table, and store it in a temporary table:
select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
'1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
INTO #jobsched
FROM (
select t1.[name],
CAST(
start_date + ' ' + LEFT(t1.start_time,2) + ':' +
SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
) AS "job_start",
LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
RIGHT(t1.job_duration,2) AS job_duration
FROM
(
select
j.[name],
LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date",
REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
"start_time",
REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6))
"job_duration"
from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
ON h.job_id = j.job_id
WHERE h.step_id = 0
) t1
) t2
SELECT * FROM #jobsched
You could take a look at the output of the above and "eyeball" it for job
conflicts, or you could try (*untested*)
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
RIGHT JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
to get a listing of jobs that are conflicting with the job named 'Job to
analyze for conflicts' .
It's also possible that the additional time for the DTS task to process is
due to locking. You could try adding another stop to the DTS job that dumbs
the output of sp_who2 into a table for further analyses.
Hope that the above helps.
--
"Patrice" wrote:

> Hi,
> I have a DTS package that runs 4 other packages on a nightly basis. This
> package usually takes about 2 hours to run, but ocasionally it jumps to
> around 5 hours to complete. There have not been any changes to the
> parameters or coding. It is the same process that causes this each time
> which is a cursor run strored procedure. There have not been any huge jum
ps
> in the number of records in the table or changes to the source data that
> would cause the cursor to take longer to run through. This seems to be
> intermittent, I can not easily check to see if something else in the netwo
rk
> on that server is happening since it is a customer site. Would you think
> that something outside of the package is affecting this since it only happ
ens
> once in a while (about once a w or so), like I mentioned - backups, ser
ver
> processes, other jobs etc.? I just don't know where to look if I have not
> changed anything within my package.
> Thanks,
> Patrice|||Sorry, that last query should have been
SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
t2.job_start, t2.job_end
FROM
( SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] = 'Job to analyze for conflicts'
) t1
INNER JOIN --INNER replaced RIGHT for the JOIN
(
SELECT [name], job_start, job_end FROM #jobsched
WHERE [name] <> 'Job to analyze for conflicts'
) t2
ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
"Mark Williams" wrote:
> The are many reasons why performance can change over time. One is job
> contention, where multiple jobs are running at the same time. The followin
g
> will dump a listing of jobs, when they started and ended out of the
> msdb.dbo.sysjobhistory system table, and store it in a temporary table:
>
> select t2.[name], t2.job_start, DATEADD(ss,DATEDIFF(ss,'1980-01-01',
> '1980-01-01 ' + t2.job_duration),t2.job_start) AS job_end
> INTO #jobsched
> FROM (
> select t1.[name],
> CAST(
> start_date + ' ' + LEFT(t1.start_time,2) + ':' +
> SUBSTRING(t1.start_time,3,2) + ':' + RIGHT(t1.start_time,2) AS datetime
> ) AS "job_start",
> LEFT(t1.job_duration,2) + ':' + SUBSTRING(t1.job_duration,3,2) + ':' +
> RIGHT(t1.job_duration,2) AS job_duration
> FROM
> (
> select
> j.[name],
> LEFT(CAST(h.run_date AS char(8)), 4) + '-' + SUBSTRING(CAST(h.run_date AS
> char(8)),5,2) + '-' + RIGHT(CAST(h.run_date AS char(8)), 2) AS "start_date
",
> REPLICATE('0', 6 - LEN(h.run_time)) + CAST(h.run_time AS varchar(6)) AS
> "start_time",
> REPLICATE('0', 6 - LEN(h.run_duration)) + CAST(h.run_duration AS varchar(6
))
> "job_duration"
> from msdb.dbo.sysjobhistory h INNER JOIN msdb.dbo.sysjobschedules j
> ON h.job_id = j.job_id
> WHERE h.step_id = 0
> ) t1
> ) t2
> SELECT * FROM #jobsched
> You could take a look at the output of the above and "eyeball" it for job
> conflicts, or you could try (*untested*)
> SELECT t1.[name], t1.job_start, t1.job_end, t2.[name] AS "conflicting job",
> t2.job_start, t2.job_end
> FROM
> ( SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] = 'Job to analyze for conflicts'
> ) t1
> RIGHT JOIN
> (
> SELECT [name], job_start, job_end FROM #jobsched
> WHERE [name] <> 'Job to analyze for conflicts'
> ) t2
> ON (t2.job_start BETWEEN t1.job_start AND t1.job_end
> OR t2.job_end BETWEEN t1.job_start AND t1.job_end)
> to get a listing of jobs that are conflicting with the job named 'Job to
> analyze for conflicts' .
> It's also possible that the additional time for the DTS task to process is
> due to locking. You could try adding another stop to the DTS job that dumb
s
> the output of sp_who2 into a table for further analyses.
> Hope that the above helps.
> --
> "Patrice" wrote:
>