Link to home
Start Free TrialLog in
Avatar of Favorable
FavorableFlag for United States of America

asked on

How to replace xp_cmdshell with SSIS package in Stored Procedure

We want to disable xp_cmdshell on our server.  However, on a particular database there is 1 stored procedure that writes a file using this (sp_create_bla bla_dlr_file) and it has exec xp_cmdshell embedded inside the sp.  This stored procedure runs once weekly through a  Sql Job.

I would like you to replace this with an SSIS package?

Once this is complete, I want to disable xp_cmdshell.

What exactly are the steps to accomplish this?
Avatar of Tony303
Flag of New Zealand image

How about a different approach. Temporarily use the cmd shell.

Disable the xp_cmdShell

In the SQL Job, add a Step called Start have the step use this...

EXEC sp_configure 'show advanced options', 1

EXEC sp_configure 'xp_cmdshell', 1

THEN Run your CmdShell process.

THEN Switch the CMD shell off again in another step

EXEC sp_configure 'xp_cmdshell', 0

EXEC sp_configure 'show advanced options', 0
Avatar of Favorable


my boss is requiring that I have it turned off completely. Then schedule SSIS package on all the Stored Proc that have the exec xp_cmdshell.
If you wish to create SSIS package to export data to Excel file in directory then follow the steps.

1. Create PAckage.
2. Add DataFlow task
3. SQL Source
4. Excel Destination
5. Map source to destination columns.
6. Deploy to SQL SErver or Directory.
7. SAme as execute using SQL Job
Tony303, if you don't mind can i place my script for modifications using the above replacement  using SQL agent rather than SSIS..

My difficulty is replacing the parameter of the sp.
Yeah, lets take a look.
Avatar of Favorable
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Close out question.  No correct solution posted