I am trying to merge records from an Oracle database table to my local SQL table.
我试图将Oracle数据库表中的记录合并到我的本地SQL表。
I have a variable for the package that is an Object, called OWell.
我有一个变量用于作为Object的包，称为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.
我有一个数据流任务，它将Oracle数据作为SQL语句获取（从Well_ID中选择well_id，来自OWell顺序的well_name），然后转换任务以将well_id从长度为15的DT_STR转换为DT_WSTR;并将well_name从长度为15的DT_STR转换为长度为50的DT_WSTR。然后将其存储在记录集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.
转换的原因是我要添加记录的表具有标识字段：SSIS将well_id显示为长度为15的DT_WSTR，well_name为长度为50的DT_WSTR。
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:
然后，我有一个连接到本地数据库的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)
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.
对于参数映射，我从我的变量User :: OWell中获得了名为OWell_ID的Paramater 0。名为OWell_Name的参数1来自同一个变量。两者都设置为VARCHAR，虽然我也尝试过NVARCHAR。我没有结果集。
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.
我收到以下错误：错误：0xC002F210在插入记录到FLEDG，执行SQL任务：执行查询“插入WELL（WELL_ID，WELL_NAME）选择OWELL ...”失败，出现以下错误：“提取时出错将结果转换为类型变量（DBTYPE_STR）“。可能的失败原因：查询问题，“ResultSet”属性设置不正确，参数设置不正确或连接未正确建立。
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?
我不认为这是一个数据类型问题，而是我不知道我没有正确使用结果集。我究竟应该如何在我的SQL任务中引用该记录集，以便我可以使用这两个记录集字段并添加缺少的记录？
2 个解决方案
#1
2
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.
您的问题是您正在尝试将对象变量读入sql任务，并在sql任务中引用该变量。
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(?,?)
要执行您要执行的操作，可以使用foreach循环任务。您可以将a的枚举数设置为对象（记录集）变量，并将其列映射到变量，然后将这些变量作为参数传递到sql任务中。上面示例中的sql代码有另一个缺陷，即您尝试引用包中的变量，就好像它是数据库中的表一样。您需要将您的SQL更改为插入井（？，？）
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.
从Oracle数据流中选择您正在做的所有事情。在最后一步，不是使用指向变量USER :: OWell的记录集目标，而是从本地sql表添加查找。在那里设置你的sql语句，从WELL中选择WELL.WELL_ID。在查找中的列选项卡上，将数据流（左侧的字段）中的Well_ID与查找中的Well_ID（右侧的字段）匹配，方法是将well_id字段从左向右拖动以在框之间形成连接。在对话框的底部，单击“配置错误输出”，并将查找输出行的错误列值设置为“重定向行”。选择确定以保存并关闭此查找。接下来，将oledb目标添加到数据流并将其连接到查找的错误输出（红色箭头）。将目标指向sql表，并将数据流中的列映射到输出表中的相应列。这会将sql表中不存在的oracle数据流中的行传递到sql表的批量插入中。
#2
1
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.
为了推断缺失的行，我们使用了查找任务，然后将未完成的行定向到普通的OLEDB目的地（显然你不提供标识列）或者（我们在比较整个表的地方）SQLBI.com TableDifference组件并将新行路由到类似的OLEDB目的地。
Individual INSERTs in SQL Command task aren't terribly quick.
SQL Command任务中的单个INSERT并不是非常快。
#1
2
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.
您的问题是您正在尝试将对象变量读入sql任务，并在sql任务中引用该变量。
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(?,?)
要执行您要执行的操作，可以使用foreach循环任务。您可以将a的枚举数设置为对象（记录集）变量，并将其列映射到变量，然后将这些变量作为参数传递到sql任务中。上面示例中的sql代码有另一个缺陷，即您尝试引用包中的变量，就好像它是数据库中的表一样。您需要将您的SQL更改为插入井（？，？）
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.
从Oracle数据流中选择您正在做的所有事情。在最后一步，不是使用指向变量USER :: OWell的记录集目标，而是从本地sql表添加查找。在那里设置你的sql语句，从WELL中选择WELL.WELL_ID。在查找中的列选项卡上，将数据流（左侧的字段）中的Well_ID与查找中的Well_ID（右侧的字段）匹配，方法是将well_id字段从左向右拖动以在框之间形成连接。在对话框的底部，单击“配置错误输出”，并将查找输出行的错误列值设置为“重定向行”。选择确定以保存并关闭此查找。接下来，将oledb目标添加到数据流并将其连接到查找的错误输出（红色箭头）。将目标指向sql表，并将数据流中的列映射到输出表中的相应列。这会将sql表中不存在的oracle数据流中的行传递到sql表的批量插入中。
#2
1
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.
为了推断缺失的行，我们使用了查找任务，然后将未完成的行定向到普通的OLEDB目的地（显然你不提供标识列）或者（我们在比较整个表的地方）SQLBI.com TableDifference组件并将新行路由到类似的OLEDB目的地。
Individual INSERTs in SQL Command task aren't terribly quick.
SQL Command任务中的单个INSERT并不是非常快。