通过DTEXEC通过xp_cmdshell传递变量（SQL Server 2008） [英] Passing a variable through DTEXEC with xp_cmdshell (SQL Server 2008)

问题描述

我已经创建了一个将Excel文件导入到我的数据库中的SSIS包。我创建了一个我想用作Excel文件路径为Excel连接管理器的变量。

我的SSIS包中的变量的名称是ExcelSource和它应该代表完整的路径。我想最终动态设置这个文件，因为文件名包含一个日期。

运行这个T-SQL代码是什么？这是我迄今为止所做的：

  DECLARE @ssisstr VARCHAR（8000）
，@packagename VARCHAR（200） 
，@servername VARCHAR（100）
 
 DECLARE @params VARCHAR（8000）
 --my包名称
 SET @packagename ='MyPackage'
 --my服务器名称
 SET @servername ='MYCOMPUTER\MYSERVER'
 
 SET @params ='/ set \package.variables [ExcelSource] .Value;\Y ：\excelFile\Test File  -  June 11 2012.xlsx\'
 
 SET @ssisstr ='dtexec / sq'+ @packagename +'/ ser'+ @servername +'' 
 SET @ssisstr = @ssisstr + @params 
 
 DECLARE @returncode INT 
 EXEC @returncode = xp_cmdshell @ssisstr 
 SELECT @returncode 
  

解决方案

您将遇到的问题是使用xp_cmdshell转义值。我遇到一个与复杂的命令行参数一起运行程序包的不同问题。如果有人可以提供相反的信息，请通知我，我将修改/删除我的回复。

我可以建议作为解决问题的替代方法是要么让你的包确定当前文件是什么，要么使用不同的机制来指导这种行为。

包，知道你自己

这种方法是我的首选方法。您可以为您的包提供智能来解决问题。您如何知道正确的Excel文件是什么？你说它有一个日期，所以你怎么知道那个日期是什么？

如果是今天的日期，你可以使用一个表达式的变量来粘贴当前日期以提供的格式。

是文件夹中唯一的文件吗？然后使用类型文件的ForEach枚举器来标识所有.xlsx文件。这个问题和最优秀的答案a>;）描述如何使用SSIS导入最新的CSV。找到最新的Excel文件 string fileMask =* .xlsx;

如果您有一个业务规则描述如何确定正确的文件，我很乐意提供有关如何使用SSIS来实现所述规则的见解。

告诉我想要什么

另一个选项是使用外部配置来提供运行时值。 SSIS提供了许多开箱即用的配置选项。我非常偏袒SQL Server用于此目的，但您的选项是

• SQL Server表


• XML文件


• 环境变量


• 注册表值


• 父套餐

最后3个是我心中的特殊用例，并不是特别适合您的问题，而是我列出的完整性。无论您使用什么配置选项，单击SSIS，软件包配置，检查启用配置按钮，并使用向导设置您的配置类型应该是一个简单的事情。

使用外部配置的第二个选择是做你正在做的，提供命令行选项来控制包的行为（不需要包更改）。相反，您在某些自定义PowerShell的xp_commandshell中进行交易。我认为PS只是2008+的选项，但您可以编写一个相当简单的脚本来导入SSIS对象模型，创建应用程序实例，打开现有包，应用命令行参数并运行应用程序。我可能基于$应用程序和$包中的一些东西，从在这里回答

编辑

1）原因你看到选项12.0无效是由于xp_cmdshell是贪心，并热切地解析命令行选项中的空格作为单独的参数。如果您开始搜索xp_cmdshell的限制，您将得到很多命中，参数中的空格导致问题。

2）为了最好的理解我的能力，SQL Agent作业是静态的。能够使用可变值参数（在运行时评估的事情）来配置它们来调用任何步骤（sql，ssis等）是非常棒的，但是一般来说，我没有找到干净的方法。 3）如果您打算不更改包以确定正确文件是什么，使用配置或滚动您自己的调用方法（PS是一项工作SQL代理中的步骤类型），您可以尝试使用现有逻辑构建dtexec调用的低技术解决方案，但将其全部放在.bat文件中。 xp_cmdshell然后调用批处理文件，该文件不应该有麻烦来处理参数名称中的空格。

I have created an SSIS package that imports an Excel file into my database. I have created a variable that I would like to use as the Excel filepath for the excel connection manager.

The name of the variable in my SSIS package is "ExcelSource" and it is supposed to represent the full path. I would like to eventually set this dynamically because the filename contains a date.

What is the T-SQL code to run this? Here is what I have so far:

DECLARE @ssisstr VARCHAR(8000)
, @packagename VARCHAR(200)
, @servername VARCHAR(100)

DECLARE @params VARCHAR(8000)
--my package name
SET @packagename = 'MyPackage'
--my server name
SET @servername = 'MYCOMPUTER\MYSERVER'

SET @params = '/set \package.variables[ExcelSource].Value;"\"Y:\excelFile\Test File - June 11 2012.xlsx\""'

SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
SET @ssisstr = @ssisstr + @params

DECLARE @returncode INT
EXEC @returncode = xp_cmdshell @ssisstr
SELECT @returncode

解决方案

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.

这篇关于通过DTEXEC通过xp_cmdshell传递变量（SQL Server 2008）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！