DTExec not finding file

Question

I have a package created, which is loaded onto our SQL server. The package goes into a folder, reads and excel, dumps the excel into a table and then archives it.

When running the package normally (through BIDS) it works perfectly fine.

But when I run it through SSMS using the following line:

exec master.dbo.xp_cmdshell 'C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe /DTS "<package location>" /SERVER "<Server>" /CHECKPOINTING OFF  /REPORTING V'

The section that pulls the Excel name (for each loop in package) reports that it does not find anything.

   Description: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

The package needs to be run in 32-bit mode to read the excel, which is why I am using the 32-bit dtexec location.

Anyone know what's up?

I would not depend on xp_cmdshell for many reasons. [Create job](https://www.mssqltips.com/sqlservertip/1180/sql-server-integration-services-package-scheduling-with-sql-agent/) and start with TSQL `EXEC dbo.sp_start_job 'job_name'` — Lukasz Szozda, Nov 04 '15 at 14:21
This SQL is not run through a job in SSMS, its an automated process (using automate) which just runs the SQL that I have above when the file is dropped into the location. — Caveman42, Nov 04 '15 at 14:23
Or are you saying to create a job, and have the SQL just run the job, instead of the dtexec? — Caveman42, Nov 04 '15 at 14:26
I mean only to exchange xp_cmdshell for job start. Just for testing. Maybe the account that is used with xp_cmdshell does not have permission to get data in directory — Lukasz Szozda, Nov 04 '15 at 14:27
Running it through the job as you suggested is now giving me the error `DTExec: Signature verification failed. The signature status is not present.` — Caveman42, Nov 04 '15 at 14:48
It is working fine now through the job. I deleted the step and re-made it and it is working both when I run the `EXEC dbo.sp_start_job` manually, and through automate. I'd say it was a `dtexec` permissions issue as you said. Thanks for your help! — Caveman42, Nov 04 '15 at 15:00

score 1 · Accepted Answer · answered Nov 04 '15 at 15:45

In my opinion you should use create SSIS Type Job and change:

exec master.dbo.xp_cmdshell 'C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe /DTS "<package location>" /SERVER "<Server>" /CHECKPOINTING OFF  /REPORTING V'

with:

EXEC dbo.sp_start_job 'job_name';

I would also check the account you use when you execute cmd shell. I suspect non sufficient privileges to read specific directory or different environment variable.

DTExec not finding file

1 Answers1