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?
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?
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.
How about using SQL agent rather than SSIS...
http://www.mssqltips.com/sqlservertip/2014/replace-xpcmdshell-command-line-use-with-sql-server-agent/
http://www.mssqltips.com/sqlservertip/2014/replace-xpcmdshell-command-line-use-with-sql-server-agent/
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
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
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.
My difficulty is replacing the parameter of the sp.
Yeah, lets take a look.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Close out question. No correct solution posted
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