Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

naxy (Programmer) (OP)
19 Jun 06 14:01
Hi all,

1) This is in a SQL Server 2000 DTS Package
2) The flat file is on the server itself (duh)
3) The flat file always has a date stamp appended to the end
4) The date stamp isn't always the date the file was FTP'd to the server

I need to be able to check for the existance of a flat file on the server. It is a required file for the DTS to execute successfully. The flat file comes down ni a format as such: "MyFile_06192006". The date stamp is always at the end, but will not always be the current date (it's relative to the data within the flat file). Before we added the date stamp, I used FSO.FileExists. However, nothing I try now to trim off the datestamp seems to work.

I'm open to all ideas!
SQLDenis (Programmer)
19 Jun 06 14:08
How do you know what date to check for?
This will list all the files in that folder in descending order
so the latest one should be on top


CREATE TABLE #tempList (Files VARCHAR(500))

EXEC MASTER..XP_CMDSHELL 'dir c:\ ' --change this

--delete all directories
DELETE #tempList WHERE Files LIKE '%<dir>%'

--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'

--delete the null values

--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading spaces
UPDATE #tempList SET files =LTRIM(files)

--split data into size and filename
SELECT LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM #tempList

Denis The SQL Menace
SQL blog:

Personal Blog:

gmmastros (Programmer)
19 Jun 06 14:10
Take a look at xp_FileExist

For example...

master..xp_fileexist 'C:\Folder\MyFile_06192006'


Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

SQLDenis (Programmer)
19 Jun 06 14:12
I think the problem is George that he doesn't know the timestamp on that file so xp_FileExist won't work (if I understood correctly)

Denis The SQL Menace
SQL blog:

Personal Blog:

gmmastros (Programmer)
19 Jun 06 14:15

I see what you mean.  Nice post by the way.


Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

SQLDenis (Programmer)
19 Jun 06 14:19
BTW if you don't need the size add /b


Denis The SQL Menace
SQL blog:

Personal Blog:

naxy (Programmer) (OP)
19 Jun 06 14:32
Denis hit the nail on the head. If I knew the date stamp, I would be able to check for its existance as is. However, because the date stamp changes each day, and also because it is not always the current day, I need to be able to look for the file name, minus the last 9 characters. For example, if the file name is "MyFile" and it contains data for 06/13/2006, it will be sent to the server as "MyFile_06132006". Tomorrow it may come down as "MyFile_06082006", or "MyFile_06202006". The date is not something that can be manipulated and there is no pattern to it.

Also, I can't look for the last updated file because we are sent anywhere from 10 to 30 flats a day, depending on the day and the BCP scheduler. The file in question is sent near the beginning of all the processes.

Ideally, I need to find a way to look for the file name only, minus the date stamp. Perhaps this may be better suited in another language forum, since I'm using the ActiveX scripting for this part.
SQLDenis (Programmer)
19 Jun 06 14:39
This should do it then, modify ther last piece

SELECT LEFT(files,PATINDEX('% %',files)) AS Size,
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName
FROM #tempList
WHERE RIGHT(files,LEN(files) -PATINDEX('% %',files)) like 'MyFile%'

Denis The SQL Menace
SQL blog:

Personal Blog:

SQLDenis (Programmer)
19 Jun 06 14:42
If there is only one file in that folder loop thru the folder


Set fso = CreateObject("Scripting.FileSystemObject")
set mainfolder=fso.GetFolder(directoryname)
Set filecollection = mainfolder.Files
For Each file In filecollection
      MsgBox( file.Name) -- here you go, throw this in a global variable

Denis The SQL Menace
SQL blog:

Personal Blog:

mrdenny (Programmer)
19 Jun 06 16:16
Here is a very cool way to see the files in a folder without shelling out via xp_cmdshell.  Using standard Microsoft provided code.


/*Create the table to get the file names into*/
CREATE TABLE tempdb.dbo.FileList ([FileName] varchar(255), depth int, [file] bit)

/*Get the file names into the table*/
insert into tempdb.dbo.FileList
exec master.dbo.xp_dirtree 'd:\temp\', 1, 1

/*Delete the directories records*/
delete from tempdb.dbo.FileList
where [file] = 0

/*Get the list of files*/
select [FileName]
from tempdb.dbo.FileList
You can then use what ever code you need to in order to process the file.

As the filename is in a table you can use a dynamic properties task to set a connection object.  A bulk insert command would also work nicly.

MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible.  All it takes is a little research. (Me)

nixie2 (IS/IT--Management)
23 Jun 06 10:59
Denis, could you help me out with something, I'm kind of stuck with a SQL problem. You seem to know a lot about SQL programming, so I think this should be easy for you.

I'm getting a:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.

Do you want the code?
SQLDenis (Programmer)
23 Jun 06 11:10
start here  thread183-1234084

Denis The SQL Menace
SQL blog:

Personal Blog:

nixie2 (IS/IT--Management)
29 Jun 06 5:12
Denos, could you please look at that thread to, I posted my question there, I haven't got a clue what I should do.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close