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

问题描述

我创建了一个 SSIS 包，可以将 Excel 文件导入到我的数据库中.我创建了一个变量，我想将其用作 Excel 连接管理器的 Excel 文件路径.

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.

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

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.

运行它的 T-SQL 代码是什么?这是我目前所拥有的:

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 = 'MYCOMPUTERMYSERVER'

SET @params = '/set package.variables[ExcelSource].Value;""Y:excelFileTest 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 转义值.对于运行带有复杂命令行参数的包时遇到的另一个问题，我无法解决这个问题.如果有人可以提供相反的信息，请告诉我，我将修改/删除我的回复.

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.

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

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?

如果是今天的日期，您可以在变量上使用表达式 something 以提供的格式粘贴到当前日期中.

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.

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

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";

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

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.

另一个选项是使用外部配置来提供运行时值.SSIS 提供了许多开箱即用的配置选项.我更倾向于为此目的使用 SQL Server，但您的选择是

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 表
• XML 文件
• 环境变量
• 注册表值
• 父包

在我看来，最后 3 个是特殊用例，对您的问题不是特别方便，但为了完整性，我已经列出了它们.无论您使用什么配置选项，单击 SSIS、包配置、选中启用配置按钮并使用向导设置您的配置类型都应该是一件简单的事情.

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.

使用外部配置的第二个选项是做你正在做的事情，提供命令行选项来控制包行为(不需要包更改).相反，您可以用 xp_commandshell 换取一些自定义 PowerShell.我认为 PS 只是 2008+ 的一个选项，但您可以编写一个相当简单的脚本来导入 SSIS 对象模型、创建应用程序的实例、打开现有包、应用命令行参数并运行应用程序.我可能可以根据 在这里回答

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

1) 您看到Option 12.0 is not valid"的原因是因为 xp_cmdshell 贪婪并且急切地将命令行选项中的空格解析为单独的参数.如果您开始搜索 xp_cmdshell 的限制，您会在参数中的空格导致问题的地方获得大量成功.

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) 据我所知，SQL 代理作业是静态的.能够将它们配置为使用可变值参数(在运行时评估的东西)调用任何步骤(sql、ssis 等)真是太棒了，但总的来说，我还没有找到一种干净的方法来这样做.

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) 如果您不打算更改包以确定正确"文件是什么，使用配置或滚动您自己的调用方法(PS 是 SQL 代理中的作业步骤类型)，您可以尝试一个低- 使用现有逻辑构建 dtexec 调用的技术解决方案，但将所有这些都包含在 .bat 文件中.xp_cmdshell 然后调用批处理文件，它应该没有处理参数名称中的空格的麻烦.

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.

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