CREATE FUNCTION fnc_IsFileInUse(@filename VARCHAR(1000))
	RETURNS BIT
AS 
    BEGIN
        DECLARE @FS INT
        DECLARE @OLEResult INT
        DECLARE @FileID INT
        DECLARE @source NVARCHAR(255)
        DECLARE @description NVARCHAR(255)
        DECLARE @flag BIT
        SET @source = 'Exist'
        SET @description = 'Exist'
        EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
        IF @OLEResult <> 0 
           BEGIN
                 EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
                 GOTO displayerror
           END
        EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @filename, 1
        IF @OLEResult <> 0 
           BEGIN
                 EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
                 GOTO displayerror
           END
        EXECUTE @OLEResult = sp_OADestroy @FileID
        EXECUTE @OLEResult = sp_OADestroy @FS
        
        displayerror:
        IF @source IS NULL
           AND @description IS NULL 
           BEGIN
                 SET @flag = 1
           END
        ELSE 
           BEGIN
                 SET @flag = 0
           END
        RETURN @flag
    END

CREATE FUNCTION fnc_IsFileInUse(@filename VARCHAR(1000))
	RETURNS BIT
AS 
    BEGIN
        DECLARE @FS INT
        DECLARE @OLEResult INT
        DECLARE @FileID INT
        DECLARE @source NVARCHAR(255)
        DECLARE @description NVARCHAR(255)
        DECLARE @flag BIT
        SET @source = 'Exist'
        SET @description = 'Exist'
        EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
        IF @OLEResult <> 0 
           BEGIN
                 EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
                 GOTO displayerror
           END
        EXECUTE @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @filename, 1
        IF @OLEResult <> 0 
           BEGIN
                 EXEC sp_OAGetErrorInfo NULL, @source OUTPUT, @description OUTPUT 
                 GOTO displayerror
           END
        EXECUTE @OLEResult = sp_OADestroy @FileID
        EXECUTE @OLEResult = sp_OADestroy @FS
        
        displayerror:
        IF @source IS NULL
           AND @description IS NULL 
           BEGIN
                 SET @flag = 1
           END
        ELSE 
           BEGIN
                 SET @flag = 0
           END
        RETURN @flag
    END

 try
        {
            using (File.Open(filepath, FileMode.Open, FileAccess.Read, FileShare.None)) { }
        }
        catch (Exception)
        {
            return false;
        }
        return true;

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;


namespace IsFileInUse
{
    public class IsFileInUse
    {

        [SqlFunction()]
        public static SqlInt32 isFileInUse(string fullFileName)
        {
            SqlInt32 FILE_IS_FREE = 0;
            SqlInt32 FILE_IS_IN_USE = 1;
            SqlInt32 FILE_IS_MISSING = 2;

            SqlInt32 intResult = FILE_IS_FREE;

            try
            {
                using (File.Open(fullFileName, FileMode.Open, FileAccess.Read, FileShare.None)) { }
            }
            catch (Exception e)
            {
                if (e.GetType() == typeof(FileNotFoundException))
                {
                    intResult = FILE_IS_MISSING;
                }
                else
                {
                    intResult = FILE_IS_IN_USE;
                }
            }

            return intResult;
        }
    }
}

CREATE ASSEMBLY [IsFileInUse]
FROM 'C:\IsFileInUse.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

CREATE FUNCTION fnc_IsFileInUse(@fullFileName NVARCHAR(MAX))
RETURNS INT
EXTERNAL NAME IsFileInUse.[IsFileInUse.IsFileInUse].isFileInUse

DECLARE @intResult INT
SELECT @intResult = dbo.fnc_IsFileInUse('c:\bigSoonToBeImportedFile.txt')
IF @intResult = 1
    BEGIN
        --report file in use
    END
ELSE IF @intResult = 2
    BEGIN
        -- report file missing
    END
ELSE
    BEGIN
        -- import the file
    END

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_changedbowner 'sa'
GO
ALTER DATABASE databaseNameHere SET TRUSTWORTHY ON
GO