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.

Thanks,
Steve
SCMHCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohammedUCommented:
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.
0
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
           below:
     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,
Steve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.