Process to check for file and then run DTS package

[sonname]

Hi
Here is my question. Is it possible with Windows 2000 or NT to have a process that monitors a specific folder for a file, then if a file is inserted in this folder, it would trigger a DTS package that would import the text file, then put the file in another folder.

Thanks in advance

 

[rajeevnandanmishra]

Hi sonname,
For checking the existence of file i had created the following procedure :

create procedure checkFile
(@filename varchar(100), @output int=0 output) as
declare @text varchar(100), @sqlstring varchar(255)
select @sqlstring='dir '+@filename
create table #temp_checkFile (id int identity(1,1), text varchar(100) null)
insert #temp
exec master..xp_cmdshell @sqlstring
set rowcount 1
select @text=text from #temp_checkFile
order by id desc
set rowcount 0
if @text='File Not Found'
select @output=0
else
select @output=1
drop table #temp_checkFile
return

And in any other procedure i used it like
declare @out int
exec checkFile 'myFileNameWithFullPath', @out output
select @out
myFileNameWithFullPath can be a UNC or hard drive but it is a hard drive then it search on the sql server only.

Same way you can write a procedure to move the file. (remember move command can only be executed on the same drives, i.e. if you want to move the required file on some other drives you have to use copy followed by a delete)

Now what you have to do is
1. create your required procedure
2. check for file existence
3. if file exist execute the dts package and
move the file


It works for me. Hope it work for you also.

 

[rajeevnandanmishra]

Sorry sonname,
change insert #temp to insert #temp_checkFile in above procedure.

 

[tlbroadbent]

Here is a simpler approach. Use the undocumented extended stored procedure - xp_fileexist.

Declare @rc int
Exec master.dbo.xp_fileexist "\\slsql04\ddnxfr\data\global.csv",@rc Output
If @rc=1
Begin
Exec xp_cmdshell 'DTSRun ....'
End

Check this link for more info on executing the package for T-SQL.

http://www.sqldts.com/main.asp?nav=1,6,210,0

I recommend moving the file using a step in the DTS package. You can also move it from the stored procedure using xp_cmdshell as suggested. MOVE will work from one drive to another or to/from UNCs but MOVE fails if the file already exists. Therefore, you would need to delete the destination file first if it exists. Or do as suggested and copy then delete the source file. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[aalmeida]

I had the same situation a year ago, I had several files been FTP'ed to several folders to a NT server, well the MS NT implementation of FTP does not support a call to cmd.exe that would allow me to start a sp after the file had finished downloading.
That prompt me to look for a FTP package that would allow me that funtionality, and I found FTP Serv-U from Deerfield that Allow me to plug-in an INI file that would call an vb app and then I applyed the bussines logic regarding the file to this vb app, it worked fine without problems and also had the advantage of load teh file as soon as it is available.
Later CA came up with an Tranfer SW that runs on top of FTP that allows the execution of remote call to cmd.exe what make the life even easier. The CA sw is DTO and is part of CA's TNG.
What turned us from schedulling File Search jobs or DTS packages was that 1st we had 1200 folders to search for a set of 3 files in each one, and no specific time time for the set to come in therefore would be very process intensive on the server and also that would give a high possibility of running over my toes when the process could run on the same file twice.
I hope this help you

 

[rajeevnandanmishra]

Hi Terry,
I tried to find/ execute the procedure xp_fileexist, but no success. It seems not to be on my SQL (6.5 & 7.0). Please let me know how to find/ execute it. And are there other un-documneted xp procedure (If yes, please let me about them also, because currently, i am using SPs created by me.)

 

[tlbroadbent]

I found xp_fileexist in both SQL 7 and SQL 2000. We have standard installations so I assume the XP should be in your SQL 7 master DB. I cannot tell you if it existed in SQL 6.5. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[rajeevnandanmishra]

Thanx Terry,
I had found it in SQL2K. And had realised that a few of the dll's are being deleted by the SA (i don't know why???) and that list includes xpstar.dll which is used for this procedure.

I had asked my senior, to question SA about this.
Hope he will come with a logical answer.