"Unable to Open BCP host data-file" using xp_cmdshell from SSMS but command line works

I just got your code to work on my end.

Try this:

Option A

1. Assign Full Control NTFS permissions to 'C:\BCPFiles\bcpFile.dat to the user you will use in SQL Server Management Studio to run the query
2. Re-run your query in SQL Server Management Studio

Option B

Or, just to get past the following error try:

Error = [Microsoft][ODBC Driver 11 for SQL Server]Unable to open BCP host data-file

1. Delete/move your bcpfile.dat file

2. on the command line type (to create a dummy file--yes there will be import errors, but just to get past opening the file...)

   echo .> c:\bcpfiles\bcpfile.dat

3. Re-run your query in SQL Server Management Studio

If option A works, then it's a permissions issue. If option B works (getting past the original error) then you have an open file--even though it doesn't appear to be open. There are utilities that help out with finding these open files, but a cheap and easy way to test is just to reboot the server--hopefully it's a dev/test box.