SSIS - 如何使用结果集作为 SQL 任务中的输入并获取正确的数据类型？
我正在尝试将 Oracle 数据库表中的记录合并到本地 SQL 表中。
我有一个名为 OWell 的包变量，它是一个对象。
我有一个数据流任务，以 SQL 语句的形式获取 Oracle 数据（从 OWell order by Well_ID 中选择 well_id, well_name），然后有一个转换任务，将 well_id 从长度为 15 的 DT_STR 转换为 DT_WSTR；并将well_name从长度为15的DT_STR转换为长度为50的DT_WSTR。然后将其存储在记录集OWell中。
转换的原因是我想要添加记录的表有一个标识字段：SSIS 将 well_id 显示为长度为 15 的 DT_WSTR，well_name 显示为长度为 50 的 DT_WSTR。
然后，我有一个连接到本地数据库的 SQL 任务，尝试添加尚不存在的记录。我尝试过各种方法：使用 OWell 作为结果集并在 SQL 语句中引用它。目前，我将 ResultSet 设置为 None，并使用以下 SQL 语句：
Insert into WELL (WELL_ID, WELL_NAME)
Select OWELL_ID, OWELL_NAME
from OWell
where OWELL_ID not in
(select WELL.WELL_ID from WELL)
对于参数映射，我有来自变量 User::OWell 的参数 0，称为 OWell_ID。名为 OWell_Name 的参数 1 来自同一变量。两者都设置为 VARCHAR，尽管我也尝试过 NVARCHAR。我没有结果集。
我收到以下错误： 将记录插入 FLEDG，执行 SQL 任务时出现错误：0xC002F210：执行查询“插入 WELL（WELL_ID、WELL_NAME） Select OWELL...”失败，并出现以下错误：“将结果提取到类型 (DBTYPE_STR) 的变量中时发生错误”。可能的失败原因：查询问题、“ResultSet”属性未正确设置、参数未设置正确，或者连接未正确建立，
我不认为这是数据类型问题，而是我没有正确使用结果集，因此我应该如何在 SQL 任务中引用该记录集。我可以使用两个记录集字段并添加缺少的记录吗？
I am trying to merge records from an Oracle database table to my local SQL table.
I have a variable for the package that is an Object, called OWell.
I have a data flow task that gets the Oracle data as a SQL statment (select well_id, well_name from OWell order by Well_ID), and then a conversion task to convert well_id from a DT_STR of length 15 to a DT_WSTR; and convert well_name from a DT_STR of length 15 to DT_WSTR of length 50. That is then stored in the recordset OWell.
The reason for the conversions is the table that I want to add records to has an identity field: SSIS shows well_id as a DT_WSTR of length 15, well_name a DT_WSTR of length 50.
I then have a SQL task that connects to the local database and attempts to add records that are not there yet. I've tried various things: using the OWell as a result set and referring to it in my SQL statement. Currently, I have the ResultSet set to None, and the following SQL statment:
Insert into WELL (WELL_ID, WELL_NAME)
Select OWELL_ID, OWELL_NAME
from OWell
where OWELL_ID not in
(select WELL.WELL_ID from WELL)
For Parameter Mapping, I have Paramater 0, called OWell_ID, from my variable User::OWell. Parameter 1, called OWell_Name is from the same variable. Both are set to VARCHAR, although I've also tried NVARCHAR. I do not have a Result set.
I am getting the following error:
Error: 0xC002F210 at Insert records to FLEDG, Execute SQL Task: Executing the query "Insert into WELL (WELL_ID, WELL_NAME)
Select OWELL..." failed with the following error: "An error occurred while extracting the result into a variable of type (DBTYPE_STR)". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I don't think it's a data type issue, but rather that I somehow am not using the resultset properly. How, exactly, am I supposed to refer to that recordset in my SQL task, so that I can use the two recordset fields and add records that are missing?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的问题是您正在尝试将对象变量读入 sql 任务，并在 sql 任务中引用该变量。
要执行您想要执行的操作，您可以使用 foreach 循环任务。您可以将 foreach 的枚举器设置为对象（记录集）变量，并将其列映射到变量，然后将这些变量作为参数传递到 SQL 任务中。上面示例中的 sql 代码还有另一个缺陷，即您试图引用包中的变量，就好像它是数据库中的表一样。您需要将您的 sql 更改为类似
Insert into well(?,?)然而，这种方法省略了您可以在插入记录之前检查记录是否存在的步骤。更好的整体方法是在数据流中完成这一切。
执行您在 Oracle 数据流中选择的所有操作。在最后一步，不使用指向变量 USER::OWell 的记录集目标，而是从本地 SQL 表添加查找。将您的 sql 语句设置为
select WELL.WELL_ID from WELL。在查找中的列选项卡上，通过从左向右拖动 well_id 字段以在框之间形成连接器，将数据流中的 Well_ID（左侧字段）与查找中的 Well_ID（右侧字段）进行匹配。在对话框底部，单击“配置错误输出”，并将查找输出行的错误列值设置为“重定向行”。选择“确定”保存并关闭此查找。接下来，将 oledb 目标添加到数据流并将其连接到查找的错误输出（红色箭头）。将目标指向 SQL 表，并将数据流中的列映射到输出表中的相应列。这会将oracle数据流中不存在于sql表中的行传递到sql表的批量插入中。Your problem is that you are trying to read an object variable into a sql task, and refer to that variable in the sql task.
To do what you are trying to do, you can use a foreach loop task. You can set the enumerator of a for each to an object (recordset) variable and map its columns to variables that you can then pass as parameters into your sql task. Your sql code in the example above has another flaw in that you are trying to reference a variable in your package as if it were a table in your database. You need to change your sql to be something like
Insert into well(?,?)This approach however leaves out the step where you can check to see if the records exists before you insert it. A better overall approach would be to do this all in a dataflow.
Do everything you are doing in your select from Oracle dataflow. At the last step, instead of using a recordset destination pointing to variable USER::OWell, add a lookup from the local sql table. Set your sql statement there to be
select WELL.WELL_ID from WELL. On the columns tab in your lookup match Well_ID from your dataflow (fields on the left) to Well_ID from your lookup (fields on the right) by dragging the well_id field from the left to the right to form a connector between the boxes. At the bottom of the dialog box, click on Configure Error Output and set the error column value for the lookup output row to be Redirect Row. Choose OK to save and close this lookup. Next, add a oledb destination to the data flow and connect it to the error output of the lookup (the red arrow). Point the destination to the sql table and map the columns from the dataflow to the appropriate columns in the output table. This will pass the rows from the oracle dataflow that do not exist in the sql table into the bulk insert of the sql table.为了推断丢失的行，我们要么使用查找任务，然后将未找到的行定向到普通的 OLEDB 目标（显然，您只是不提供标识列）或（我们在比较整个表的情况下）SQLBI.com TableDifference 组件 并将新行路由到类似的 OLEDB 目标。
SQL 命令任务中的单个 INSERT 速度并不是很快。
To infer missing rows we either used a lookup task and then directed the unfound rows to an ordinary OLEDB destination (you just don't supply the identity column, obviously) or (where we were comparing a whole table) the SQLBI.com TableDifference component and routed the new rows to a similar OLEDB destination.
Individual INSERTs in SQL Command task aren't terribly quick.