SSIS. How can I use a result set as input to a SQL task and get the correct data types?
I am trying to concatenate records from an Oracle database table to my local SQL table.
I have a variable for a package which is an object called OWell.
I have a dataflow task that gets Oracle data as a SQL statute (select well_id, well_name from OWell order from Well_ID) and then a conversion task to convert well_id from DT_STR length 15 to DT_WSTR; and convert the username from DT_STR of length 15 to DT_WSTR of length 50. It is then stored in the OWell recordset.
The reason for the conversions is the table I want to add records to has an identification field: SSIS shows well_id as DT_WSTR with length 15, well_name as DT_WSTR with length 50.
Then I have a SQL task that connects to a local database and tries to add records that are not already there. I've tried different things, using OWell as a result set and referencing it in my SQL statement. I currently have the ResultSet set to None and the following SQL status:
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 a Paramater 0 parameter called OWell_ID from my User :: OWell variable. Parameter 1, called OWell_Name, refers to the same variable. Both are set to VARCHAR, although I also tried NVARCHAR. I don't have a result set.
I am getting the following error: Error: 0xC002F210 while inserting records into FLEDG, executing SQL task: Executing query "Insert into WELL (WELL_ID, WELL_NAME) Select OWELL ..." failed with the following error: "Error while fetching the result into a variable of type ( DBTYPE_STR) ". Possible reasons for the failure: problems with the request, the ResultSet property is not set correctly, the parameters are not set correctly, or the connection is not established correctly.
I don't think this is a datatype issue, but rather due to the fact that somehow I am not using the result set correctly. How exactly am I supposed to reference this recordset in my SQL task so that I can use the two fields of the recordset and add the missing records?
a source to share
Your problem is that you are trying to read the object variable in the sql task and you are referencing that variable in the sql task.
You can use the foreach loop task to do what you are trying to do. You can set an enumerator for each object variable (recordset) and map its columns to variables, which can then be passed as parameters to your sql task. Your sql code in the example above has another flaw in that you are trying to refer to a variable in your package as if it were a table in your database. You need to change your sql to be something likeInsert into well(?,?)
However, this approach ignores where you can check if records exist before inserting them. The best general approach would be to do it all in the data stream.
Do whatever you do in your selection from Oracle Dataflow. In the final step, instead of using a recordset assignment pointing to the USER :: OWell variable, add a search from the local sql table. Set for your sql statementselect WELL.WELL_ID from WELL
... In the Columns tab in your search match Well_ID from your data stream (left fields) to Well_ID from your search (right fields), dragging the well_id field from left to right to form a connector between the fields. At the bottom of the dialog box, click Customize Error Output and set the error column value for the search output string to be redirected. Select "OK" to save and close this search. Then add the oledb destination to the data stream and connect it to the search error output (red arrow). Specify the destination in the sql table and map the columns from the data stream to the corresponding columns in the output table. This will pass rows from oracle datastream that do not exist in sql table to bulk insert of sql table.
a source to share
To output the missing rows, we either used a search task and then routed the frivolous rows to a regular OLEDB object (you just don't specify the id column) or (where we were comparing the entire table) the SQLBI.com TableDifference component and redirected the new rows to a similar OLEDB item.
Individual INSERTs on a SQL Command task are not very fast.
a source to share