Search for new text file?

[balistikb]

I have a text file that is sent to me every week. I would like to create a DTS that can look for that text file so I could import it into a table. No here is the problem, the name of the text file changes everytime it is sent. The name of the file is usually the date that it was sent. How can I have my DTS find the new file?
Thanks

 

[TheJon]

I have come across this kind of issue in a data warehousing project. My colleague, who was tasked with the data extract, ended up saving the DTS package as a VB file, and re-writing it to grab the filenames from a directory listing using the VB filesystemobject. The VB DTS could then be compiled and run from a DTS package using the execute process task. Upon success of the package, the import file can be archived, so that the directory is clear and waiting for the next file.

Sorry this isn't any more detailed, but I wasn't directly involved in the programming.

I hope it helps!

[tt]|_ |_ _ | _ _
|, | )(/, |(_)| )
'[/tt]

 

[JayKusch]

Question to both ... is the file that is sent place in a folder that is emptied each day? Or can it be set up that way? If so, there are ways of doing what you need!

Thanks

J. Kusch

 

[balistikb]

No mine is not. After the process, the file is renamed and put back in the folder.

 

[cybercop23]

I have the same issue you do. My brut eforce solution was to make sure there were no other similar file names and then rename the file to something static using cmdexec as the first step of my job. I've also rename the files when pulling files from vendor's FTP sites so my DTS only knows of one static name.

Don't you wish Microsoft will include something in the DTS system to allow for imports and exports that have system date as part of the file name?

AB

 

[nigelrivett]

It's easier using bcp
see

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

You can also use an activex script in the dts package to check the directory and set the source of the insert task.

Or you could use an SP to check for the file then run the package with the filename as a parameter then use a dynamic properties task to set it for the import.

Usually dts isn't an appropriate product for text file imports.

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

 

[balistikb]

Wow Thanks All, I will have to try some of the suggestions. Unfortunatly I am new to SQL so some of the suggestions might be too advanced.

 

[nigelrivett]

>> Don't you wish Microsoft will include something in the DTS system to allow for imports and exports that have system date as part of the file name?

That's usually done via an activex script to set the source or destination - or via a rename as you have.

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