Process task hangs running bcp

We are attempting to use Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00 to run a BCP command from a Process task. We are able to do so without a problem when running it interactively, but when run as a SQL Server Agent job the bcp process never completes and the job hangs indefinitely.

We also tried using a Script task for this, with the same result.

Has anyone else run into this problem?

Thanks,

Ron

What happens if you run it from dtexec? (i.e. from the command-line).

If it executes successfully using dtexec then its most likely a security issue.

-Jamie

|||

Jamie,

Thank you for your reply. Yes, the BCP processing works fine when I run the package interactively in Management Studio. However, I don't see how it could be a security issue, because the account that SQL Server Agent is running under is in the Administrators group on the server, and the BCP output is going to a share on that same server. I double checked, and the Administrators group has full control permission on both the share and the folder.

Is there anyone that has been successful in running BCP in an SSIS Process task under SQL Server Agent?

Thanks,

Ron

|||this link might help: http://support.microsoft.com/kb/918760|||

Thank you for the suggestion, Duane. In this case, though, the package executes, it just hangs on the Process task that executes the BCP command.

The security level of the package is DontSaveSensitive, and the Agent service account has full permissions to the file system, so it does not seem likely that lack of permissions is the issue.

Ron

|||

We stumbled upon the solution for this, but it does seem that a glitch in SSIS may be the root cause.

When running BCP in a Process task, the path to the BCP executable must be provided. When running a package interactively a UNC path name to bcp.exe works fine, but when run as a job the UNC path name somehow causes the process to hang indefinitely. Changing the notation from "\\<servername>\c$\Program Files\..." to "c:\Program Files\..." fixes the problem.

Is there any valid reason that this should be the case?

Thanks,

Ron