Check Flat File's Existance via DTS Package

[naxy]

Hi all,

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

Question:
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!
Thanks!!!

 

[SQLDenis]

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

INSERT INTO #tempList
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
DELETE #tempList WHERE Files IS NULL

--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
ORDER BY FileName DESC

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Take a look at xp_FileExist

For example...

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



-George

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

 

[SQLDenis]

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:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

Denis,

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

-George

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

 

[SQLDenis]

BTW if you don't need the size add /b

EXEC MASTER..XP_CMDSHELL 'dir c:\ /b'

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[naxy]

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]

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:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

If there is only one file in that folder loop thru the folder

directoryname="c:\test\"
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
Next

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

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.

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

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

http://www.mrdenny.com

 

[nixie2]

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]

start here thread183-1234084

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[nixie2]

Denos, could you please look at that thread to, I posted my question there, I haven't got a clue what I should do.