I've just begun working with SQL Server (2000) recently and have some
familiarity with setting up DTS packages. I also have a lot of
experience with VBA, so ActiveX scripting should come easily to me.
Basically, I have a folder, let's say "V:\MyFolder\", and I would like
to

1) truncate a holding table in SQL Server,
2) import a file in the given folder into this holding table,
3) run a couple of queries against the table, and then
4) append the rows in the holding table to a master table,
5) go to step 1.

The files have the form 's005%.txt', where % is a wildcard and I would
like to loop this procedure until all files of this form in the given
folder are exhausted.

I'm looking for some detailed assistance here, if possible, and it
would be greatly appreciated. I've been waiting for a month to get
permission from the DBAs for BULK INSERT and I believe I may be
waiting until sometime after hell freezes over... ;-)

K

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 5, 3:08 am, Klein-4 <soreeyeh... (AT) gmail (DOT) com> wrote:
Hi Klein,
for what i know, DTS don't allow you to loop through group of tasks,
unless you use activex scripting. in this case you must script it
entirely. In SQL Server 2005 ther're some new objects that let you do
this quite easily.
Depending on your specific needs, here are some workarounds:
- execute the package every... minute and importing one file (eg,
alphabetical order) every execution, taking care of deleting that file
at the end of the dts.
- at the beginning of the package, merge all txt files into one
(assuming they've all the same structure) and then import it, with
activex shouldn't be difficult for you.

the one-file-package shuld appear like this:
1) execute sql task
2) file connetion to your .txt
grey arrow connectin (transform data task in which you choose field,
field names, ecc)
3) oledb connection to your hodling table (by 'holding' you mean a
sort of temp or staging table, don't you?)
4) execute sql task - insert into master select * from holding

Bye,
M.

PS: Here it's VERY hot and wet and i don't believe hell is going to
freeze over soon...

On Jul 6, 4:38*am, matteus <matteog... (AT) gmail (DOT) com> wrote:
Thanks much. I'll try this out. :-)

K