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?
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.