I am trying to run an SSIS package using a T-SQL Task in a SQL 2008 Maintenance Plan.
I created a batch file (c:\Test-Bat\Test-Bat.bat) that executes the following command
DTExec.exe /File C:\Test-Bat\Unity-CFARS.dtsx
If I run the batch file from Windows Explorer or the Command prompt it finishes successfully, extracts data and writes two extracted data files to the C:\Test-Bat\ directory.
If I run the same Batch file from a SQL Maintenance Plan using a T-SQL Task and the command
EXEC master..xp_CMDShell 'c:\Test-Bat\Test-Bat.bat'
it returns Success but does not write the two extracted data files to the C:\Test-Bat\ directory.
I can run another batch file that just copies files in these directories. So I think this indicates that xp_cmdshell is activated properly and the user the job is running under has sufficient (full) rights to the folders that are to be written to.
Any ideas on how to get this process to work?
These will be the scheduled periodic extracts and data transfers, I need to send to other systems.