Inserting data into an Oracle db using SSIS OLE DB Command

Question

I am attempting to do an insert and update(2 seperate flows of course) from a SQL source into an Oracle destination. I followed the process given in this link: http://sqlblog.com/blogs/andy_leonard/archive/2007/07/09/ssis-design-pattern-incremental-loads.aspx

Because my destination contains a column which uses Oracle's Sequence I am unable to use just an OLE DB destination or the Oracle destination(attunity drivers) so I modified the New Rows path(Insert path) to use an OLE DB Command and entered the following T-sql statement in the Componenent Properties SQL Command:

insert into AWQMSO.FIRM (
FIRM_ID,
ACM_FIRM_ID,
MSTR_FIRM_ID,
ACTV_IND,
FIRM_STAT_TXT,
CRTE_BY_USR_ID,
CRTE_DTTM,
LST_UPDT_BY_USR_ID,
LST_UPDT_DTTM)
values (
FIRM_SEQ.Nextval,
?,
?,
?,
?,
?,
?,
?,
?)

The package then fails on the OLE DB Command(insert) with a rather useless error message of:

Error: 0xC0202009 at Data Flow Task, OLE DB Command 1 [571]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E55.

Which is repeated 152 times... the exact number of rows that should be inserted into the destination table.

The update path however works fine(almost...still fighting with a date format) using a similar T-sql statement:

Update AWQMSO.FIRM
set
FIRM_STAT_TXT = ?
,ACTV_IND = ?
,LST_UPDT_BY_USR_ID = ?
where ACM_FIRM_ID = ?

The only difference I can see is in the hard coding of the .Nextval so I tried creating a derived column containing "FIRM_SEQ.Nextval" without the quotes. Changed the FIRM_SEQ.Nextval to a ? and mapped the value to the newly derived field which gives me a more informative error message that the data cannot be inserted into the FIRM_ID field but still leaves me with the same problem.

I have successfully used an Execute SQL task to insert a single row into a statistics table in Oracle with a sequence field using variables and a paremeterized query. That was only a single row though with 4 columns and I don't know how to reproduce the same sort of thing to get each individual row of my dataset into a variable then execute the insert and move to the next row.

Thanks in advance for your help!

0x80040E55 is the OLEDB error for DB_E_BADORDINAL - The specified column did not exist. See the TechNet reference of OLEDB errors here. Share and enjoy. — Bob Jarvis - Reinstate Monica
After implementing a script similar to the one below we discovered the indexes had been corrupted on the DEV box all along and so the original solution above works just fine. — Nathan J.

Robert Anderson Robert Anderson · Accepted Answer · 2014-08-04T00:56:20

You could add a script task which would increment the number and check the format required. e.g. You could first select the nextval from the sequence and using this as the initial value.

In the oledb destination (for the oracle table) you would map the firm ID to the output value of the script task.

To create a script task, you could use code such as this:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    int i;
    public override void PreExecute()
    {
        base.PreExecute();
        i = 0; //************initialize this to the nextval sequence value
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        i++;
        Row.FirmID = i;
    }

}

Inserting data into an Oracle db using SSIS OLE DB Command

1 Answers