Dynamic file name on different server 1

[tman24m]

Hi all,
I've been scouring the internet for the last couple days trying to find an example but am not coming up with exactly what I need.

I have an ftp server where clients put text delimited files, a different server than my SQL server. I've got a vb6 app that monitors the different directories on the ftp server for any files that get added. The file names are dynamic so I only know the first three characters of the filename that I want to import. Does anyone have an example of a dts package that imports text files in the manner that I have described? I want to be able to call a dts package and pass it the file path.

 

[nigelrivett]

Why not use bcp - simpler and faster.

You can pass the filename to a global variable in the dtsrun command then use a dynamic properties task to set it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[tman24m]

Thanks for the quick response.

I looked at BCP, but my understanding is that the table def must be exactly the same format as the txt file format. I've got some additional fields in the table that are not in the text files. Since this is a staging table, I didn't want to create another staging table for the staging table.

I am open to suggestions, but I really need to see an example if you can help.

thanks

 

[nigelrivett]

You can use a format file

http://www.nigelrivett.net/BCP_quoted_CSV_Format_file.html

or easier to just create a view and bcp into that. Both willl be simpler and less resource hungry than dts.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.