• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 860
  • Last Modified:

Executing a SSIS using xp_cmdshell

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.

1 Solution
I can only think of permissions and xp_cmdshell configuration but as per your posting you don't have those issues...then I have no idea...but...
Have tried using "Execute Process Task" or "Execute Package Task" option? If not you try...
SQL maintenance plan using SSIS then you can use one of these tasks.
SCMHCAuthor Commented:
After spending about 8 hours with Microsoft I did get the SSIS packages to function in a Maintenance Plan.   The following is a summary of the findings

1)  I changed the Execute T-SQL Statement Task In the Maintenance Plan to look like the statement
     exec xp_CMDShell 'DTExec.exe /File "\\srv01file\SSIS-Projects\Unity-CFARS.dtsx" '
     According to Microsoft I did not need the database reference prior to xp_CMDShell, I presume
     because I was in a Maintenance Plan in a Database instance.

2)  The SQL Server Agent runs under UserID: SQLAdmin.  SQLAdmin id part of the
     Domain Administrators Group.  Even though Domain Administrators had Full Control
     on the Folder where the .dtsx package resided, The maintenance package could not access the file.
     I had to give SQLAdmin Full Control specifically to both the Share and the Folder before the
     Maintenance Plan would Execute.

3)  In a Maintenance Plan which had the command:
     exec xp_CMDShell 'DTExec.exe /File "\\srv01file\SSIS-Projects\Unity-CFARS.dtsx" '
     If I changed the Package name to some non-existent Package such as Unity-CFARSxxxxx.dtsx
     The package would register success for completion.  You can however see the error if you
     just run the statement as a query.

Thanks for all the help,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now