Looping through files for importing

#1

Hello all,

I have 60 text files that I need to import into a DB. I want to figure out a way to loop through the files automatically. Can this
be done in the DTS Package?

I understand how to loop through all files in a folder using VB/VBA, so I can manage that if I save the package as a VB File. The
only problem if I go that route is that there is a field missing in each data file (the Month End Date). But each file consists of
one month and conveniently has the month end date in the file name, so I can populate the field by extracting the date from the file
name.

How I normally would do this in a DTS Package is:
1. Select the file using the Open File Dialog Task I downloaded from www.SQLDTS.COM (store the path\filename in a global variable)
2. use an ActiveX Script Task to extract the date in the file name global variable and store it in a date global variable.
3. then in my Transform Data Task, I would have an ActiveX Script transformation assigning that date global variable to the MEDate
field.

This works okay, but it is one file at a time with some interaction from me selecting each file.

Now I can remove the Open File Dialog Task, save the package as a VB file, setup the VB code to loop through each file, and
extract/store the date from the file name in a date variable in VB.

The thing I don't understand is how to pass the date stored in that variable to the date global variable in the Package/Transform
Data Task code in VB. I don't understand the VB code that SQL Server comes up with very well.

Any help that anyone can provide will be greatly appreciated,

Conan Kelly

#2

I think this can be done in 2005.

"Conan Kelly" <CTBarbarin at msn dot com> wrote

Quote:

Hello all,

I have 60 text files that I need to import into a DB. I want to figure
out a way to loop through the files automatically. Can this
be done in the DTS Package?

I understand how to loop through all files in a folder using VB/VBA, so I
can manage that if I save the package as a VB File. The
only problem if I go that route is that there is a field missing in each
data file (the Month End Date). But each file consists of
one month and conveniently has the month end date in the file name, so I
can populate the field by extracting the date from the file
name.

How I normally would do this in a DTS Package is:
1. Select the file using the Open File Dialog Task I downloaded from
www.SQLDTS.COM (store the path\filename in a global variable)
2. use an ActiveX Script Task to extract the date in the file name global
variable and store it in a date global variable.
3. then in my Transform Data Task, I would have an ActiveX Script
transformation assigning that date global variable to the MEDate
field.

This works okay, but it is one file at a time with some interaction from
me selecting each file.

Now I can remove the Open File Dialog Task, save the package as a VB file,
setup the VB code to loop through each file, and
extract/store the date from the file name in a date variable in VB.

The thing I don't understand is how to pass the date stored in that
variable to the date global variable in the Package/Transform
Data Task code in VB. I don't understand the VB code that SQL Server
comes up with very well.

Any help that anyone can provide will be greatly appreciated,

Conan Kelly

#3

Hello Conan Kelly" CTBarbarin at msn dot com,

You should be able to adapt this article to do what you need.

http://www.sqldts.com/default.aspx?246

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Quote:

Hello all,

I have 60 text files that I need to import into a DB. I want to
figure out a way to loop through the files automatically. Can this be
done in the DTS Package?

I understand how to loop through all files in a folder using VB/VBA,
so I can manage that if I save the package as a VB File. The only
problem if I go that route is that there is a field missing in each
data file (the Month End Date). But each file consists of one month
and conveniently has the month end date in the file name, so I can
populate the field by extracting the date from the file name.

How I normally would do this in a DTS Package is:

1. Select the file using the Open File Dialog Task I downloaded from
www.SQLDTS.COM (store the path\filename in a global variable)

2. use an ActiveX Script Task to extract the date in the file name
global variable and store it in a date global variable.

3. then in my Transform Data Task, I would have an ActiveX Script
transformation assigning that date global variable to the MEDate

field.

This works okay, but it is one file at a time with some interaction
from me selecting each file.

Now I can remove the Open File Dialog Task, save the package as a VB
file, setup the VB code to loop through each file, and extract/store
the date from the file name in a date variable in VB.

The thing I don't understand is how to pass the date stored in that
variable to the date global variable in the Package/Transform Data
Task code in VB. I don't understand the VB code that SQL Server comes
up with very well.

Any help that anyone can provide will be greatly appreciated,

Conan Kelly

#4

Allan,

Thank you for the info. That looks like it will be very valuable to me.

Conan

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

Quote:

Hello Conan Kelly" CTBarbarin at msn dot com,

You should be able to adapt this article to do what you need.

http://www.sqldts.com/default.aspx?246

Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hello all,

I have 60 text files that I need to import into a DB. I want to
figure out a way to loop through the files automatically. Can this be
done in the DTS Package?

I understand how to loop through all files in a folder using VB/VBA,
so I can manage that if I save the package as a VB File. The only
problem if I go that route is that there is a field missing in each
data file (the Month End Date). But each file consists of one month
and conveniently has the month end date in the file name, so I can
populate the field by extracting the date from the file name.

How I normally would do this in a DTS Package is:

1. Select the file using the Open File Dialog Task I downloaded from
www.SQLDTS.COM (store the path\filename in a global variable)

2. use an ActiveX Script Task to extract the date in the file name
global variable and store it in a date global variable.

3. then in my Transform Data Task, I would have an ActiveX Script
transformation assigning that date global variable to the MEDate

field.

This works okay, but it is one file at a time with some interaction
from me selecting each file.

Now I can remove the Open File Dialog Task, save the package as a VB
file, setup the VB code to loop through each file, and extract/store
the date from the file name in a date variable in VB.

The thing I don't understand is how to pass the date stored in that
variable to the date global variable in the Package/Transform Data
Task code in VB. I don't understand the VB code that SQL Server comes
up with very well.

Any help that anyone can provide will be greatly appreciated,

Conan Kelly

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

Looping through files for importing

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

Looping through files for importing - 04-06-2006 , 07:13 PM

Re: Looping through files for importing - 04-08-2006 , 01:09 AM

Re: Looping through files for importing - 04-09-2006 , 03:46 PM

Re: Looping through files for importing - 04-11-2006 , 02:36 PM