How to enable xp_cmdshell for SQL server?

We will get below error if xp_cmdshell is not enabled for SQL Server.

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.

Below is script to enable xp_cmdshell.

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Different ways to execute/store SSIS packages

Before going ahead, we should be aware about how many ways we can store SSIS packages

1. File system
2. SQL Server (Integration Service)
3. SQL Server Integration Service Catalog

Now, we will discuss how to execute SSIS packages for above storage.

1) Execute Package Utility (DtExecUI)

a) Go to Run –> Type “DtExecUI” –> Enter will open DtExecUI Utility

b) You can choose package source from all above storage and execute SSIS package.

c) You can follow below link to know more about this tool.

http://technet.microsoft.com/en-us/library/ms180378.aspx

2) dtexec Utility (SSIS Tool)

a) Execute SSIS package located on File System. Here, we are calling package which is located on “C:\Sample.dtsx”­­ and setting 2 variables of package.

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

/f "C:\Sample.dtsx"

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"

b) Execute SSIS package located on SQL Server.

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

/dts "\"\File System\Sample\""

/SERVER ServerName

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"

c) Execute SSIS package located on Integration Service Catalog.

"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe"

/ISSERVER "\SSISDB\SSISFolder\SSISProject\Sample.dtsx"

/SERVER "ServerName"

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"

Reference: http://technet.microsoft.com/en-us/library/ms162810(v=sql.105).aspx

3) Execute package using xp_cmdshell for above DTExec.exe command. If we will use this command, it will refer DTExec.exe from database server. Below is script to execute SSIS package located on Integration Service Catalog.

exec master.dbo.xp_cmdshell 'C:\"Program Files"\"Microsoft SQL Server"\110\DTS\Binn\DTExec.exe /ISSERVER "\SSISDB\SSISFolder\SSISProject\Sample.dtsx"

/SERVER "ServerName"

/Par "$ServerOption::SYNCHRONIZED(Boolean)";True

/Set \Package.Variables[User::Var1].Properties[Value];"Val1"

/Set \Package.Variables[User::Var2].Properties[Value];"Val2"'

4) Execute Package from SQL Server Agent Service

a) Got to SQL Server Agent Service –> Jobs –> Right click New Job. Give job name “SampleJob”.

b) Go to Steps –> New. Give step name “Execute SampleJob” and select type as “SQL Server Integration Services Package”. You also can change Run as if you have proxy account created for SQL Server Integration Service.

c) You can choose package source from all above storage and complete other steps for jobs.

5) Execute Package using SSISDB.Catalog procedure. Below is script to execute package which is located at below location.

 

Query:

Declare @p_execution_id int

-- Creates an instance of execution in the Integration Services catalog.

EXEC [SSISDB].[catalog].[create_execution]

@folder_name = 'SSISFolder',

@project_name = 'SSISProject',

@package_name = 'Sample.dtsx',

@reference_id = null,

@use32bitruntime = 0,

@execution_id = @p_execution_id OUTPUT

-- Set property value OR Set variable value.

exec [SSISDB].[catalog].[set_execution_property_override_value]

@execution_id = @p_execution_id,

@property_path = '\Package.Variables[User::Var1].Properties[Value]',

@property_value = 'Val1',

@sensitive = 0

-- Set parameter value to wait till package execution is not completed.

EXEC [SSISDB].[catalog].[set_execution_parameter_value]

@p_execution_id,

@object_type=50,

@parameter_name=N'SYNCHRONIZED',

@parameter_value= 1;

-- Starts an instance of execution in the Integration Services catalog.

EXEC [SSISDB].[catalog].[start_execution] @p_execution_id

-- Get the status of package execution.

Select status from [SSISDB].[catalog].[executions] where execution_id = @p_execution_id