Skip over Bulk Insert when the file does not exist - sqlserver

Problem/symptom:

In SQL Server Management Studio 2008, the file does not exist then I
get the error "Cannot bulk load. The file "C:/scratch.txt" does not
exist."

when I run the script , the Bulk Insert is
totally ignored because of error: "the file doesn't exist. "

Probably Answer:

1. The problem is that SQL is running under a local account. This doesn't have
access to the remote fileshare. You would need to run under a domain user
account.

2. Depedning on how this is run, either your NT login, or the NT login SQL
Server uses, or the login SQL Agent uses ( if a scheduled TSQL task) must
have the appropriate permissions....

3. SqlServer is running on an admin account of the local box only. The box
has been joined to the domain but the user that sql runs under doesn't exist
in the domain. I would prefer not to have sql run under a domain account if
there is a way to impersonate a user of the domain during the script but if
SQL needs to run under a domain user then I guess that's what needs to be
done. Do you have any further suggestions?

4.Please be aware that this will fail if you service security context is
LocalSystem or a local machine account (assuming the UNC name points to a
remote server).

5.Does the service account that SQL Server runs under have permissions to the networked drive?

If you are running SQL Server under the local system account, it will have no access to anything off the box it is running on.

6.xp_fileexists won't work with networked drive letters, but it will work if you use the UNC share name.

7. IP address does not belong here.
Use proper UNC notation.

8.
IP address will work but you must reference a share on the remote server and the SQL Server service account must have access to that share. Each partitions has a default Administrative share,which is referenced with the partition letter followed by a dollar sign ( $ )

Example:
EXEC master..xp_fileexist '//10.218.4.71/C$/boot.ini'

Results are:
File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1 0 1

Please be aware that if the SQL Server service account does not have access to the share, then "xp_fileexist" will report that the file does not exist and the return code indicates success. "xp_cmdshell" however,does return a non-zero return code.

declare @sprc int
EXEC @sprc = master..xp_cmdshell 'dir //10.218.32.159/c$/boot.ini'
select @sprc as CmdShell_ReturnCode


output
--------------------------------------------------
Logon failure: unknown user name or bad password.
NULL

CmdShell_ReturnCode
-------------------
1

SQL = Scarcely Qualifies as a Language

Source:
http://www.keyongtech.com/2218256-help-with-xp_fileexist
http://www.sqlservercentral.com/Forums/Topic566704-338-1.aspx