MS SQL Server DTS - Import Multiple Text Files

Author

Import Multiple Text Files

molsonexpert

2006-03-29, 9:29 am

I'm new to DTS so bear with me.
I have an app that exports several tab-delimited files throughout the day.
I'd like to then schedule a time when these can be imported into an SQL
database. I've got it to work fine on a test server using DTS for one file.
The exported files generated have different names, but I can have this
standardized if need be. Do I have to set up a separate DTS package for each
file? I was hoping the source could be configured with a wildcard, but I
suspect this isn't possible. Can anyone suggest a better way of doing this?

Thanks.

Tomer Shoval

2006-03-29, 8:27 pm

you can create as many transformation tasks as you like.
if the number of file you want to import is low and static, add a
connection to sql, and a connection for each file and create
transformations for them. the dts package will execute them all when it
runs.

if you have a large number of file, or a dynamic amount of files, you'd
be better of using the bulk insert task along with some activex script
that will read all files from a directory (based on a wildcard or not)
and then change the parameters for the bulk insert task and execute it
in a loop to load all files.

Sponsored Links