SSIS ODBC SQL参数 [英] SSIS ODBC SQL parameters

问题描述

我在odbc源数据流任务中有一条SQL命令，需要获取参数，但是没有添加参数的选项.
我试图将数据库添加为与ODBC提供程序的ADO.NET连接，但是也没有可用的参数.还尝试将其作为OLEDB连接，但是没有可用于ODBC的提供程序. 所需的变量已设置，我只是不能将它们添加为参数.

I've got a SQL command in an odbc source data flow task that needs to take parameters, but the option to add them isn't there.
I tried to add the database as an ADO.NET connection with an ODBC provider, but there were also no parameters available. Also tried it as an OLEDB connection, but there's no provider available for ODBC. The variables needed are set, I just can't add them as parameters.

所以我想知道的主要事情是:
是否有办法在ODBC源中将参数添加到SQL命令中?
我可以使用ODBC的OLEDB提供程序吗?
我可以直接在查询中访问包变量吗?这会让我开放进行sql注入吗?像这样.

So the main thing I'm wondering are:
Is if there's a way to add parameters to a SQL command in an ODBC source
Is there an OLEDB provider for ODBC I can use?
Can I access the package variables directly in the query? Will that leave me open for a sql injection? Like this.

"SELECT * FROM MyTable WHERE [id] = " + @[User::id]

这是更改后的表达式

Here's the altered expression

"SELECT Name, PhoneNum, Address FROM PERSON<br>
WHERE Name = '" + @[User::Name] + "'<br>
AND PhoneNum = '" + @[User::PhoneNum] + "'"

它在源代码中生成此代码:

It generates this in the source:

SELECT Name, PhoneNum, Address FROM PERSON<br>
WHERE Name = ''<br>
AND PhoneNum = ''

会根据值填写引号吗?

推荐答案

您必须在数据流之外使用表达式生成器.转到控制流"选项卡，选择包含ODBC或ADO Net Source的数据流，然后查看属性窗口.您将在此处看到ODBC或ADO Net Source的属性，以及"Expressions"属性，可以在其中设置用于生成动态SQL的表达式.

You have to use the expression builder outside the data flow. Go to the control flow tab, select the Data Flow that contains the ODBC or ADO Net Source, and look at the properties window. You'll see the properties for the ODBC or ADO Net Source there, as well as an "Expressions" property, where you can set the expression to generate your dynamic SQL.

编辑

这是您要更改属性的位置的图像.您需要在数据流中设置通用SQL语句，单击确定"，然后返回控制流.然后右键单击您的数据流，然后单击属性.在表达式下，您可以选择您的SQL命令并使用变量进行构建.

Here's an image of where you'd change the property. You need to set a generic SQL statement in your data flow, click okay, and go back into the control flow. Then right right click on your data flow and click properties. Under expressions, you can select your SQL command and build it with variables.

这篇关于SSIS ODBC SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！