Bcp - dump via xp_cmdshell vs. sp_oaMethod Security

Question

I am using Microsoft SQL Server 2008 R2 and I am trying to upload a table via bcp (with a scheduled task). First of all, I implemented it using xp_cmdshell, but I heard that this should be avoided for security reasons.

So my question is: what about sp_oaMethod? Should it be used? If not, why not? And could you offer other possibilities?

Answer 1

A better solution than xp_cmdshell is to use the SQL agent job step of type "CmdExec" or "Powershell".

CmdExec will be my preference for a single line call to BCP.exe . Sysadmin credentials are required to create a job step.

Powershell is your answer if you need to perform any additional tasks, for example, to clean the extracted file and copy it to a network share.