Escaping Command Parameters Passed to Xp_Cmdshell to Dtexec

Escaping command parameters passed to xp_cmdshell to dtexec

After looking into this, it appears you have to use the DOS 8.3 notation with xp_cmdshell e.g. c:\progra~1... and you can only have one set of quotes on the arguments.

To get around this limitation, either use the older DOS notation, or put your code in a batch file instead which will run fine.

Source: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4e7024bb-9362-49ca-99d7-1b74f7178a65

Passing a variable through DTEXEC with xp_cmdshell (SQL Server 2008)

The problem you're going to run into is escaping of values with xp_cmdshell. I could not get around that for a different problem I had with running packages with complex commandline arguments. If someone can provide information to the contrary, let me know and I will amend/remove my response.

What I can suggest as an alternate means of solving your problem would be to either let your package determine what the current file is or use a different mechanism for directing that behaviour.

Package, know thyself

This approach is my preferred method. You provide the intelligence to your package to solve the problem. How do you know what the correct Excel file is? You state it has a date in it so how do you know what that date is?

If it's today's date, you can use an expression on the variable something to paste in the current date in the supplied format.

Is it the only file in a folder? Then use a ForEach enumerator of type file to identify all the .xlsx files out there. This question and most excellent answer ;) describe how to use SSIS to import the most recent CSV. It'd be a trivial change to find the most recent Excel file string fileMask = "*.xlsx";

If you have a business rule describing how to determine the right file, I'd be happy to provide insight on how you could use SSIS to implement said rule.

Tell me what you want

The other option is to use external configuration to supply the run-time value. SSIS provides for a number of out of the box configuration options. I'm rather partial to use SQL Server for this purpose but your options are

• SQL Server table
• XML file
• Environment variable
• Registry value
• Parent Package

The last 3 are special use cases in my mind and not particularly handy for your problem but for completeness I've listed them. Whatever configuration option you use, it should be a simple matter to click SSIS, Package Configuration, check the enable configuration button and use the wizard to set up your configuration type.

A second option for using external configuration is to do what you're doing, supplying command-line options to control package behaviour (no package changes required). Instead, you trade in the xp_commandshell for some custom PowerShell. I think PS was only an option from 2008+ but you could write a fairly simple script to import the SSIS object model, create an instance of the Application, open the existing package, apply the command line parameters and run the Application. I could probably cobble something together based on the $app and $package bits from the answer over here

Edits

1) The reason you are seeing "Option 12.0 is not valid" is due to xp_cmdshell being greedy and eagerly parsing the spaces in the command line options as separate arguments. If you start searching on limitations of xp_cmdshell you'll get plenty of hits where spaces in arguments cause problems.

2) To the best of my ability to understand, SQL Agent jobs are static things. It'd be awesome to be able to configure them to call whatever steps (sql, ssis, etc) with variable valued parameters (things evaluated at run-time) but generally speaking, I haven't found a clean means of doing so.

3) If you're intent on not changing the package to determine what the "right" file is, using configurations or rolling your own invocation method (PS is an job step type in SQL Agent), you could try a low-tech solution of using your existing logic to build out the dtexec call but have that all in a .bat file. xp_cmdshell then calls the batch file which should not have the trouble of handling the space in the argument name.

How to call .dtsx file which has input parameters from a stored procedure?

Using DtExec and xp_cmdshell

One way to do that is to run DtExec utility from file system using xp_cmdshell utility inside sql server.

First you have to enable the xp_cmdshell utility:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
-- WITH OVERRIDE disables the configuration value checking if the value is valid
RECONFIGURE WITH OVERRIDE
GO
-- To enable the xp_cmdshell component.
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE 
GO
-- Revert back the advance option
EXEC sp_configure 'show advanced options', 0
GO 
RECONFIGURE WITH OVERRIDE 
GO

Then you can use the following command to execute the package and pass avariable value as parameter:

DECLARE @SQLQuery AS VARCHAR(2000)

DECLARE @ServerName VARCHAR(200) = 'ARSHAD-LAPPY' 

SET @SQLQuery = 'DTExec /FILE ^"E:\DataTransfer.dtsx^" '
SET @SQLQuery = @SQLQuery + ' /SET \Package.Variables[ServerName].Value;^"'+ @ServerName + '^"'

EXEC master..xp_cmdshell @SQLQuery
GO

---

References

• Executing a SSIS Package from Stored Procedure in SQL Server
• Run an SSIS package from the command prompt with DTExec.exe
• DTEXEC Command Line Parameters Using Command Files
• dtexec Utility

Helpful links

• How to Call SSIS Package from the Stored Procedure
• Run an SSIS package from SSMS with Transact-SQL (if using SSISDB)
• How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure (if using SSISDB)

Microsoft SQL xp_cmdshell doesn't like filenames with spaces. Can I replace the space with something else?

You have to put something before quoted path to avoid error C:\Program' is not recognized... so I used CALL statement and it worked for me ...

declare @cmd nvarchar(1000)

set @cmd = 'call "C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe" myDB.dbo.'
exec xp_cmdshell @cmd

running 7-zip from a SQL script

It's possible, yes, but probably a bad idea: permissions are often a problem (as you've found out), paths and working directories will trip you up, and building shell commands in SQL is a pain all round. It would be much easier just to use an external script, and run it from a scheduled job or SSIS package.

If you clarify exaclty why and when you want to run the script from SQL then someone may be able to suggest a better approach. I do exactly the same thing using SSIS and Python, for example.

---

Related Topics