DTS Speed Issues

#1

I have to import a few million text Files with a specific format importing
them is not the problem; Speed is. I run the Script from DTS on a master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch of text
files.. I only process ones with a specific extention (in the script) I
think the problem is that I created a table that holds the FullPath of the
file and each time I read a file I first check it against the Database,
however this DB is increasingly large.. and I think it is slowing down the
process of importing... What Should I do.. scrap that table and when I
process a file or bunch of files.. Zip them up and move them.. or should I
keep the Table my boss doesnt want me to Delete the files.. he is worried
about losing data and etc... I have been doing the inital process of
importing data from 2002 till now.. and its been running oh 2 days and not
even half done. Should I have just turned off the check files feature for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#2

Sorry for the name.. my outlook was set up wrong.. :|

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote

Quote:

I have to import a few million text Files with a specific format importing
them is not the problem; Speed is. I run the Script from DTS on a master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch of text
files.. I only process ones with a specific extention (in the script) I
think the problem is that I created a table that holds the FullPath of the
file and each time I read a file I first check it against the Database,
however this DB is increasingly large.. and I think it is slowing down the
process of importing... What Should I do.. scrap that table and when I
process a file or bunch of files.. Zip them up and move them.. or should I
keep the Table my boss doesnt want me to Delete the files.. he is worried
about losing data and etc... I have been doing the inital process of
importing data from 2002 till now.. and its been running oh 2 days and
not
even half done. Should I have just turned off the check files feature for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#3

Why not use something like the following article except add a check for the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you require. If
file = good then COPY it over to another directory. Grab the file from that
directory. This way simply bring in all files in that directory with no
checkingof names. The round trip to the DB every time will become a killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote

Quote:

I have to import a few million text Files with a specific format importing
them is not the problem; Speed is. I run the Script from DTS on a master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch of text
files.. I only process ones with a specific extention (in the script) I
think the problem is that I created a table that holds the FullPath of the
file and each time I read a file I first check it against the Database,
however this DB is increasingly large.. and I think it is slowing down the
process of importing... What Should I do.. scrap that table and when I
process a file or bunch of files.. Zip them up and move them.. or should I
keep the Table my boss doesnt want me to Delete the files.. he is worried
about losing data and etc... I have been doing the inital process of
importing data from 2002 till now.. and its been running oh 2 days and
not
even half done. Should I have just turned off the check files feature for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#4

Can the FileSystemWatcher scan a directory base.. do you have an articles
beyond MSDN about it... I dont have VS.NEt on my machine (none of the
machines have the .Net framework even [not my choice]) but I would have no
problem putting it on to create a little task. but the files when they come
to the server get put into subdirectories within subdirectories of a root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

Why not use something like the following article except add a check for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you require.
If
file = good then COPY it over to another directory. Grab the file from
that
directory. This way simply bring in all files in that directory with no
checkingof names. The round trip to the DB every time will become a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific format
importing
them is not the problem; Speed is. I run the Script from DTS on a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch of
text
files.. I only process ones with a specific extention (in the script) I
think the problem is that I created a table that holds the FullPath of
the
file and each time I read a file I first check it against the Database,
however this DB is increasingly large.. and I think it is slowing down
the
process of importing... What Should I do.. scrap that table and when I
process a file or bunch of files.. Zip them up and move them.. or should
I
keep the Table my boss doesnt want me to Delete the files.. he is
worried
about losing data and etc... I have been doing the inital process of
importing data from 2002 till now.. and its been running oh 2 days and
not
even half done. Should I have just turned off the check files feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#5

I haven't checked but it does have a flag for IncludeSubDirs. How far down
it goes I do not know

Currently what I do in a similar position to you is I have implemented DFS.
This way I can logically show the diretories as being at the root of a given
Directory on a Server. If I need to manage another directory I add it to
the DFS structure and that is that. This has the added advanatge that do
not need to even be monitoring the same server and in fact I don't. There
are around 20 servers all being pointed to through DFS and shown as they
they were local.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote

Quote:

Can the FileSystemWatcher scan a directory base.. do you have an articles
beyond MSDN about it... I dont have VS.NEt on my machine (none of the
machines have the .Net framework even [not my choice]) but I would have
no
problem putting it on to create a little task. but the files when they
come
to the server get put into subdirectories within subdirectories of a root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uISfKti9DHA.1268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Why not use something like the following article except add a check for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you require.
If
file = good then COPY it over to another directory. Grab the file from
that
directory. This way simply bring in all files in that directory with no
checkingof names. The round trip to the DB every time will become a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific format
importing
them is not the problem; Speed is. I run the Script from DTS on a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch of
text
files.. I only process ones with a specific extention (in the script)
I
think the problem is that I created a table that holds the FullPath of
the
file and each time I read a file I first check it against the
Database,
however this DB is increasingly large.. and I think it is slowing down
the
process of importing... What Should I do.. scrap that table and when I
process a file or bunch of files.. Zip them up and move them.. or
should
I
keep the Table my boss doesnt want me to Delete the files.. he is
worried
about losing data and etc... I have been doing the inital process of
importing data from 2002 till now.. and its been running oh 2 days
and
not
even half done. Should I have just turned off the check files feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#6

Sounds like it will work great. You seem to have a wealth of knowledge that
a student like me just loves to pick at so got another question still within
the same problem for you.
I have to let the current Import finish before I redesign the DTS because
the engineers here need to do a query to make up some histograms by the end
of the week. My plan is to write an app in .Net as you suggested to monitor
the filesystem from what I can tell via MSDN documentation it will watch all
the subdirectories.. don't know if I'll go as far as to create a DFS because
its always uploading to the same network directory. When it sees a new file
being created with the extension I use, it will copy that to a "ToProcess"
folder which then will be scanned by the DTS. My next Question is this,
there are duplicate filenames but in the original directory structure they
are in specific folders so that isn't a problem. My little task running in
the background.. should I get it to duplicate the filestructure or just
rename a duplicated file to something else?

Really Appreciate the help BTW

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

I haven't checked but it does have a flag for IncludeSubDirs. How far
down
it goes I do not know

Currently what I do in a similar position to you is I have implemented
DFS.
This way I can logically show the diretories as being at the root of a
given
Directory on a Server. If I need to manage another directory I add it to
the DFS structure and that is that. This has the added advanatge that do
not need to even be monitoring the same server and in fact I don't. There
are around 20 servers all being pointed to through DFS and shown as they
they were local.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:eZtXvjj9DHA.2392 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Can the FileSystemWatcher scan a directory base.. do you have an
articles
beyond MSDN about it... I dont have VS.NEt on my machine (none of the
machines have the .Net framework even [not my choice]) but I would have
no
problem putting it on to create a little task. but the files when they
come
to the server get put into subdirectories within subdirectories of a
root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uISfKti9DHA.1268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Why not use something like the following article except add a check
for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you
require.
If
file = good then COPY it over to another directory. Grab the file
from
that
directory. This way simply bring in all files in that directory with
no
checkingof names. The round trip to the DB every time will become a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific format
importing
them is not the problem; Speed is. I run the Script from DTS on a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch
of
text
files.. I only process ones with a specific extention (in the
script)
I
think the problem is that I created a table that holds the FullPath
of
the
file and each time I read a file I first check it against the
Database,
however this DB is increasingly large.. and I think it is slowing
down
the
process of importing... What Should I do.. scrap that table and when
I
process a file or bunch of files.. Zip them up and move them.. or
should
I
keep the Table my boss doesnt want me to Delete the files.. he is
worried
about losing data and etc... I have been doing the inital process
of
importing data from 2002 till now.. and its been running oh 2 days
and
not
even half done. Should I have just turned off the check files
feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#7

Duplicated names will be a problem if you add the two files to a processing
directory at the same time yes. If you take the approack of loading them to
a WorkingFolder then how about you prefix filenames when moved with
<dir>_FileName.txt or whatever you want. After all the import routine
doesn't care what the files are called does it.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote

Quote:

Sounds like it will work great. You seem to have a wealth of knowledge
that
a student like me just loves to pick at so got another question still
within
the same problem for you.
I have to let the current Import finish before I redesign the DTS because
the engineers here need to do a query to make up some histograms by the
end
of the week. My plan is to write an app in .Net as you suggested to
monitor
the filesystem from what I can tell via MSDN documentation it will watch
all
the subdirectories.. don't know if I'll go as far as to create a DFS
because
its always uploading to the same network directory. When it sees a new
file
being created with the extension I use, it will copy that to a "ToProcess"
folder which then will be scanned by the DTS. My next Question is this,
there are duplicate filenames but in the original directory structure they
are in specific folders so that isn't a problem. My little task running
in
the background.. should I get it to duplicate the filestructure or just
rename a duplicated file to something else?

Really Appreciate the help BTW

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:%23ovKY2j9DHA.1472 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
I haven't checked but it does have a flag for IncludeSubDirs. How far
down
it goes I do not know

Currently what I do in a similar position to you is I have implemented
DFS.
This way I can logically show the diretories as being at the root of a
given
Directory on a Server. If I need to manage another directory I add it
to
the DFS structure and that is that. This has the added advanatge that
do
not need to even be monitoring the same server and in fact I don't.
There
are around 20 servers all being pointed to through DFS and shown as they
they were local.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:eZtXvjj9DHA.2392 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Can the FileSystemWatcher scan a directory base.. do you have an
articles
beyond MSDN about it... I dont have VS.NEt on my machine (none of the
machines have the .Net framework even [not my choice]) but I would
have
no
problem putting it on to create a little task. but the files when
they
come
to the server get put into subdirectories within subdirectories of a
root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uISfKti9DHA.1268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Why not use something like the following article except add a check
for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you
require.
If
file = good then COPY it over to another directory. Grab the file
from
that
directory. This way simply bring in all files in that directory
with
no
checkingof names. The round trip to the DB every time will become a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific format
importing
them is not the problem; Speed is. I run the Script from DTS on a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a bunch
of
text
files.. I only process ones with a specific extention (in the
script)
I
think the problem is that I created a table that holds the
FullPath
of
the
file and each time I read a file I first check it against the
Database,
however this DB is increasingly large.. and I think it is slowing
down
the
process of importing... What Should I do.. scrap that table and
when
I
process a file or bunch of files.. Zip them up and move them.. or
should
I
keep the Table my boss doesnt want me to Delete the files.. he is
worried
about losing data and etc... I have been doing the inital
process
of
importing data from 2002 till now.. and its been running oh 2
days
and
not
even half done. Should I have just turned off the check files
feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#8

No the import Routine doesn't, infact the orginal filename is actually
stored in the content of the file itself and imported into the database with
everything else. So yea a prefix on the files sounds like a good idea..
that way I dont have to loop through any subdirectores increasing the speed
of the import more. Ha, but the old import is still chugging along.
Started Monday night.. Oh We'll these engineers need their data. When I get
the new implimentation done, I'll post here to let know how it went.
Cheers.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

Duplicated names will be a problem if you add the two files to a
processing
directory at the same time yes. If you take the approack of loading them
to
a WorkingFolder then how about you prefix filenames when moved with
dir>_FileName.txt or whatever you want. After all the import routine
doesn't care what the files are called does it.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OH3ZZVm9DHA.2368 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Sounds like it will work great. You seem to have a wealth of knowledge
that
a student like me just loves to pick at so got another question still
within
the same problem for you.
I have to let the current Import finish before I redesign the DTS
because
the engineers here need to do a query to make up some histograms by the
end
of the week. My plan is to write an app in .Net as you suggested to
monitor
the filesystem from what I can tell via MSDN documentation it will watch
all
the subdirectories.. don't know if I'll go as far as to create a DFS
because
its always uploading to the same network directory. When it sees a new
file
being created with the extension I use, it will copy that to a
"ToProcess"
folder which then will be scanned by the DTS. My next Question is this,
there are duplicate filenames but in the original directory structure
they
are in specific folders so that isn't a problem. My little task running
in
the background.. should I get it to duplicate the filestructure or just
rename a duplicated file to something else?

Really Appreciate the help BTW

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:%23ovKY2j9DHA.1472 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
I haven't checked but it does have a flag for IncludeSubDirs. How far
down
it goes I do not know

Currently what I do in a similar position to you is I have implemented
DFS.
This way I can logically show the diretories as being at the root of a
given
Directory on a Server. If I need to manage another directory I add it
to
the DFS structure and that is that. This has the added advanatge that
do
not need to even be monitoring the same server and in fact I don't.
There
are around 20 servers all being pointed to through DFS and shown as
they
they were local.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:eZtXvjj9DHA.2392 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Can the FileSystemWatcher scan a directory base.. do you have an
articles
beyond MSDN about it... I dont have VS.NEt on my machine (none of
the
machines have the .Net framework even [not my choice]) but I would
have
no
problem putting it on to create a little task. but the files when
they
come
to the server get put into subdirectories within subdirectories of a
root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uISfKti9DHA.1268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Why not use something like the following article except add a
check
for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you
require.
If
file = good then COPY it over to another directory. Grab the file
from
that
directory. This way simply bring in all files in that directory
with
no
checkingof names. The round trip to the DB every time will become
a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific format
importing
them is not the problem; Speed is. I run the Script from DTS on
a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a
bunch
of
text
files.. I only process ones with a specific extention (in the
script)
I
think the problem is that I created a table that holds the
FullPath
of
the
file and each time I read a file I first check it against the
Database,
however this DB is increasingly large.. and I think it is
slowing
down
the
process of importing... What Should I do.. scrap that table and
when
I
process a file or bunch of files.. Zip them up and move them..
or
should
I
keep the Table my boss doesnt want me to Delete the files.. he
is
worried
about losing data and etc... I have been doing the inital
process
of
importing data from 2002 till now.. and its been running oh 2
days
and
not
even half done. Should I have just turned off the check files
feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#9

Thanks

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote

Quote:

No the import Routine doesn't, infact the orginal filename is actually
stored in the content of the file itself and imported into the database
with
everything else. So yea a prefix on the files sounds like a good idea..
that way I dont have to loop through any subdirectores increasing the
speed
of the import more. Ha, but the old import is still chugging along.
Started Monday night.. Oh We'll these engineers need their data. When I
get
the new implimentation done, I'll post here to let know how it went.
Cheers.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:e4x5Jdr9DHA.2028 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
Duplicated names will be a problem if you add the two files to a
processing
directory at the same time yes. If you take the approack of loading
them
to
a WorkingFolder then how about you prefix filenames when moved with
dir>_FileName.txt or whatever you want. After all the import routine
doesn't care what the files are called does it.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OH3ZZVm9DHA.2368 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Sounds like it will work great. You seem to have a wealth of
knowledge
that
a student like me just loves to pick at so got another question still
within
the same problem for you.
I have to let the current Import finish before I redesign the DTS
because
the engineers here need to do a query to make up some histograms by
the
end
of the week. My plan is to write an app in .Net as you suggested to
monitor
the filesystem from what I can tell via MSDN documentation it will
watch
all
the subdirectories.. don't know if I'll go as far as to create a DFS
because
its always uploading to the same network directory. When it sees a
new
file
being created with the extension I use, it will copy that to a
"ToProcess"
folder which then will be scanned by the DTS. My next Question is
this,
there are duplicate filenames but in the original directory structure
they
are in specific folders so that isn't a problem. My little task
running
in
the background.. should I get it to duplicate the filestructure or
just
rename a duplicated file to something else?

Really Appreciate the help BTW

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:%23ovKY2j9DHA.1472 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
I haven't checked but it does have a flag for IncludeSubDirs. How
far
down
it goes I do not know

Currently what I do in a similar position to you is I have
implemented
DFS.
This way I can logically show the diretories as being at the root of
a
given
Directory on a Server. If I need to manage another directory I add
it
to
the DFS structure and that is that. This has the added advanatge
that
do
not need to even be monitoring the same server and in fact I don't.
There
are around 20 servers all being pointed to through DFS and shown as
they
they were local.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:eZtXvjj9DHA.2392 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Can the FileSystemWatcher scan a directory base.. do you have an
articles
beyond MSDN about it... I dont have VS.NEt on my machine (none of
the
machines have the .Net framework even [not my choice]) but I
would
have
no
problem putting it on to create a little task. but the files when
they
come
to the server get put into subdirectories within subdirectories of
a
root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uISfKti9DHA.1268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Why not use something like the following article except add a
check
for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you
require.
If
file = good then COPY it over to another directory. Grab the
file
from
that
directory. This way simply bring in all files in that directory
with
no
checkingof names. The round trip to the DB every time will
become
a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific
format
importing
them is not the problem; Speed is. I run the Script from DTS
on
a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a
bunch
of
text
files.. I only process ones with a specific extention (in the
script)
I
think the problem is that I created a table that holds the
FullPath
of
the
file and each time I read a file I first check it against the
Database,
however this DB is increasingly large.. and I think it is
slowing
down
the
process of importing... What Should I do.. scrap that table
and
when
I
process a file or bunch of files.. Zip them up and move them..
or
should
I
keep the Table my boss doesnt want me to Delete the files..
he
is
worried
about losing data and etc... I have been doing the inital
process
of
importing data from 2002 till now.. and its been running oh 2
days
and
not
even half done. Should I have just turned off the check files
feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

#10

Thank you.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Quote:

Thanks

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:%237OVomu9DHA.1504 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
No the import Routine doesn't, infact the orginal filename is actually
stored in the content of the file itself and imported into the database
with
everything else. So yea a prefix on the files sounds like a good idea..
that way I dont have to loop through any subdirectores increasing the
speed
of the import more. Ha, but the old import is still chugging along.
Started Monday night.. Oh We'll these engineers need their data. When I
get
the new implimentation done, I'll post here to let know how it went.
Cheers.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:e4x5Jdr9DHA.2028 (AT) TK2MSFTNGP10 (DOT) phx.gbl...
Duplicated names will be a problem if you add the two files to a
processing
directory at the same time yes. If you take the approack of loading
them
to
a WorkingFolder then how about you prefix filenames when moved with
dir>_FileName.txt or whatever you want. After all the import routine
doesn't care what the files are called does it.

--

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

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OH3ZZVm9DHA.2368 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Sounds like it will work great. You seem to have a wealth of
knowledge
that
a student like me just loves to pick at so got another question
still
within
the same problem for you.
I have to let the current Import finish before I redesign the DTS
because
the engineers here need to do a query to make up some histograms by
the
end
of the week. My plan is to write an app in .Net as you suggested to
monitor
the filesystem from what I can tell via MSDN documentation it will
watch
all
the subdirectories.. don't know if I'll go as far as to create a DFS
because
its always uploading to the same network directory. When it sees a
new
file
being created with the extension I use, it will copy that to a
"ToProcess"
folder which then will be scanned by the DTS. My next Question is
this,
there are duplicate filenames but in the original directory
structure
they
are in specific folders so that isn't a problem. My little task
running
in
the background.. should I get it to duplicate the filestructure or
just
rename a duplicated file to something else?

Really Appreciate the help BTW

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:%23ovKY2j9DHA.1472 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
I haven't checked but it does have a flag for IncludeSubDirs. How
far
down
it goes I do not know

Currently what I do in a similar position to you is I have
implemented
DFS.
This way I can logically show the diretories as being at the root
of
a
given
Directory on a Server. If I need to manage another directory I
add
it
to
the DFS structure and that is that. This has the added advanatge
that
do
not need to even be monitoring the same server and in fact I
don't.
There
are around 20 servers all being pointed to through DFS and shown
as
they
they were local.

Allan

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Ryan Rogers" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:eZtXvjj9DHA.2392 (AT) TK2MSFTNGP11 (DOT) phx.gbl...
Can the FileSystemWatcher scan a directory base.. do you have an
articles
beyond MSDN about it... I dont have VS.NEt on my machine (none
of
the
machines have the .Net framework even [not my choice]) but I
would
have
no
problem putting it on to create a little task. but the files
when
they
come
to the server get put into subdirectories within subdirectories
of
a
root
folder.. can it scan that?

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote in message
news:uISfKti9DHA.1268 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
Why not use something like the following article except add a
check
for
the
extension

Looping, Importing and Archiving
(http://www.sqldts.com/Default.aspx?246)

You could even do this

Build a FileSystemWatcher task in .Net.

Have it watch your directory. Filer on the extension type you
require.
If
file = good then COPY it over to another directory. Grab the
file
from
that
directory. This way simply bring in all files in that
directory
with
no
checkingof names. The round trip to the DB every time will
become
a
killer

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
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

"Microsoft" <ryan.rogers (AT) tesma (DOT) com> wrote in message
news:OS1I7ji9DHA.1428 (AT) TK2MSFTNGP12 (DOT) phx.gbl...
I have to import a few million text Files with a specific
format
importing
them is not the problem; Speed is. I run the Script from
DTS
on
a
master
directory which structure is similar to
Results\TestStand#\'Month'\####TestNumber###\TestF iles.P*,F*
We Get an upload every two hours to the current 'Month' of a
bunch
of
text
files.. I only process ones with a specific extention (in
the
script)
I
think the problem is that I created a table that holds the
FullPath
of
the
file and each time I read a file I first check it against
the
Database,
however this DB is increasingly large.. and I think it is
slowing
down
the
process of importing... What Should I do.. scrap that table
and
when
I
process a file or bunch of files.. Zip them up and move
them..
or
should
I
keep the Table my boss doesnt want me to Delete the files..
he
is
worried
about losing data and etc... I have been doing the inital
process
of
importing data from 2002 till now.. and its been running oh
2
days
and
not
even half done. Should I have just turned off the check
files
feature
for
the first run.. ANY HELP Please! would be great.

Co-Op student
Ryan Rogers
STT Technologies

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

DTS Speed Issues

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

DTS Speed Issues - 02-18-2004 , 08:10 AM

Re: DTS Speed Issues - 02-18-2004 , 08:16 AM

Re: DTS Speed Issues - 02-18-2004 , 08:33 AM

Re: DTS Speed Issues - 02-18-2004 , 10:04 AM

Re: DTS Speed Issues - 02-18-2004 , 10:44 AM

Re: DTS Speed Issues - 02-18-2004 , 03:22 PM

Re: DTS Speed Issues - 02-19-2004 , 01:09 AM

Re: DTS Speed Issues - 02-19-2004 , 07:09 AM

Re: DTS Speed Issues - 02-19-2004 , 07:20 AM

Re: DTS Speed Issues - 02-19-2004 , 07:24 AM