"Conan Kelly" <CTBarbarin at msn dot com> wrote in message
news:uU9JggdWGHA.4580@TK2MSFTNGP04.phx.gbl...
> 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
>
>

> 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
>

"Allan Mitchell" <allan@no-spam.sqldts.com> wrote in message news:f0343b031fb158c82a362ea8eac0@msnews.microsoft.com...
> 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
>>
>
>