Avatar of Favorable
Favorable
Flag 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?
Microsoft SQL Server

Avatar of undefined
Last Comment
Favorable

8/22/2022 - Mon
Tony303

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

Disable the xp_cmdShell
Then

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

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

THEN Run your CmdShell process.

THEN Switch the CMD shell off again in another step

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
GO

EXEC sp_configure 'show advanced options', 0
RECONFIGURE
GO
Favorable

ASKER
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.
Tony303

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Alpesh Patel

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
Favorable

ASKER
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.
Tony303

Yeah, lets take a look.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Favorable

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Favorable

ASKER
Close out question.  No correct solution posted