Options for checking if file exists on DB Server Filesystem
I am maintaining a Classic ASP web application accessing a database(SQL Server 2012) on a seperate server.
There is alot of integration work with this legacy application. Alot of which involves import files hosted on the database server. These are processed by SSIS packages run by an SQL Server Agent job and launched by ASP COM+ objects
I would like to be able to check if an import file exists or not ON THE DATABASE SERVER(separate from the web server) prior to running the import packages. So it should be able to fail and return an error that indicates that a file was not found.
Our database and web server used to be on the same machine. Now they have been separated.
This functionality should be generic so that I can apply it on all imports by, ideally, passing the file path as a parameter.
With that environment and Classic ASP Limitation, these are the options I have considered:
a) Adding a step to the SQL Server Agent Job that runs TSQL follows:
- Step happens before the one that calls the SSIS import package/ or runs as a separate job.
- It relies on the SQL Server Agent job "success" or "failure" returns indicating file found or not-found respectively
Problem: As far as I know it is not generic. It would mean a job for every import type as only one job can be processed at a time and jobs do not support parameters(is).
b) Using the undocumented xp_fileexist stored procedure as follows
CREATE PROCEDURE [dbo].[spFileExists] @fpath varchar(255) AS begin DECLARE @fexists int EXEC master.dbo.xp_fileexist @fpath, @fexists output SELECT cast(@fexists as bit) end GO
Problem: I have tested this approach and it accesses the filesystem OF THE WEBSERVER instead of the intended filesystem of the DATABASE server. If I put the file in the web server using the expected path, it returns 1 indicating that it is there. If I delete it and place in the DB server, removing from the Web server, it returns 0.
Why would it be checking in the Web server filesystem?
c) Creating a SQL Server CLR assembly in a .NET language and trying to use .NET filesystem operations to access the database filesystem. Havent tried yet
d) Using sqlcmd Havent tried yet
Any best practice or experiences would be most helpful.
I ended up revising the COM+ code line by line and revisiting the environment configs. b) now works I cannot reproduce the issue I had but my best assumption is that it was application config related.
So I have resorted to using xp_fileexist though I understand it is not good practice to use undocumented SPs (they can be removed without warning in next versions).