Saturday, November 12, 2005

SSIS and Oracle Sequences

So I need to load a table, and I need an auto-incrementing field filled in. Easy enough I think....oops...I'm using Oracle and their infernal sequences. Now compared to the identity field in SQL Server or the Auto-Number in Access, these things are just abominations. Two ways to get around this, so here we go.....

  1. Generate your own sequence numbers....pretty easy with the Script component and a variable. Search the web, email me or get Donald Farmer's book on scripting and this is done in a minute and works like a champ. Why? This now becomes an in-memory operation on the set of data, and it is quick....
  2. Use the OLE DB command to do an insert and reference the MySequence.NextVal in your insert statement. Don't try this at home if you have a lot of rows to generate numbers for. After about 50,000 records, your going to want to get some coffee.....
There you go, so of now and create some numbers on the fly....

Scott Barrett

posted by Scott Barrett @ 12:21 AM   1 comments

1 Comments:

At 9:21 AM, Blogger dunlavy said...

Place a trigger on the Oracle table that replaces a null value sent to it with the next sequence number.

No round trips to the database and you can re-use with other packages.

CREATE OR REPLACE TRIGGER myTableTrigger
BEFORE INSERT
ON myTable
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (
old.mySequence is null
)
DECLARE

myNextVal NUMBER;

BEGIN
myNextVal := 0;

SELECT mySequence.NEXTVAL INTO myNextVal FROM dual;
:NEW.mySequence := myNextVal;

EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ;
/

 

Post a Comment

<< Home