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:

  1. Step happens before the one that calls the SSIS import package/ or runs as a separate job.
  2. 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)
              DECLARE @fexists int
              EXEC master.dbo.xp_fileexist @fpath, @fexists output
              SELECT cast(@fexists as bit)


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

Need Your Help

how to Differentiate colored layer and transparent layer of Image?

image cocos2d-iphone layer cclayer

I want to Differentiate colored layer and transparent layer of image, anyone can know , how its possible ?

Jquery: how to automatically force a textbox to keyup in every 1 hour

javascript jquery ajax

I want to automatically fire the below function on every 1 hour. This is actually for currency converter, currently my below function takes action only if users enter/changes any value in the first

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.