Problem creating Oracle sequence numbers within a SSIS (2005) package

• Walter Tucholski

  Ten Centuries

  Points: 1262

  More actions

  March 7, 2006 at 7:05 am

  #91511

  Having issues trying to create unique Oracle sequence numbers within a package in SSIS (2005). Business scenerio is as follows:

  Read in a text file, do some transformations and output rows to an Oracle table with unique sequence numbers. Here is what I am doing.

  Tasks within my Data Flow task:

  1) Flat file source

  2) Transformations

  3) Ole DB command - update an SQL table which contains the sequence number. Sequence number is incremented for each record read.

  4) Lookup - do a lookup to above SQL sequence table above to get the last generated sequence number.

  5) Ole DB destination - output the rows to Oracle.

  The problem is that the lookup always brings back the last sequence number that was created and my Oracle sequence column values contain all the same value. This appears to be caused by the fact that the tasks within my data flow perform set operations. So for example if I am processing 100 records, the Ole DB command gets executed 100 times and the value the Lookup transformation retrieves for the sequence number is 100.

  Does someone have any ideas on how generate the unique seq number for each record read and pass the unique sequence number to the Oracle sequence number column for each text file record read? 

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 7, 2006 at 3:15 pm

  #625289

  Walter i don't know if this helps. you might not have access to change the DDL on the oracle server.

  In my situation, i use a trigger to insert the sequence number if not supplied by the application. In that situation, the column that receives the sequence is kind of the equivilent of a SQL identity field.

  see the example below; i have a column that gets populated via a before insert trigger in oracle; it gets the next value from the sequence, and works for each row if inserting multiple rows.

  here is an example of the trigger with all the DDL i use:

  CREATE TABLE username.TBCOUNTY (

  COUNTYTBLKEY    NUMBER(10)                   NOT NULL,

  INDEXTBLKEY     NUMBER(10)                   NOT NULL,

  STATE           VARCHAR2(2)                  NOT NULL,

  DESCRIP         VARCHAR2(30)                 NOT NULL,

  CODE            VARCHAR2(3)                      NULL,

  STATETBLKEY     NUMBER(10)                       NULL,

  REGIONTBLKEY    NUMBER(10)                       NULL,

  EDREGIONTBLKEY  NUMBER(10)                       NULL,

  CONSTRAINT PK__TBCOUNTY__07970BFE PRIMARY KEY (COUNTYTBLKEY),

  FOREIGN KEY (REGIONTBLKEY)    REFERENCES username.TBREGION(REGIONTBLKEY),

  FOREIGN KEY (EDREGIONTBLKEY)  REFERENCES username.TBEDREGION(EDREGIONTBLKEY),

  FOREIGN KEY (INDEXTBLKEY)     REFERENCES username.GMINDEX(INDEXTBLKEY),

  FOREIGN KEY (STATETBLKEY)     REFERENCES username.TBSTATE(STATETBLKEY)) ;

  --username.TBCOUNTY_SEQUENCE

  CREATE SEQUENCE username.TBCOUNTY_SEQUENCE

   START WITH 1

   NOMAXVALUE

   MINVALUE 1

   NOCYCLE

   CACHE 20

   NOORDER;

  --username.TBCOUNTY_IDENTITY

  --

  CREATE OR REPLACE TRIGGER username.TBCOUNTY_IDENTITY BEFORE INSERT ON username.TBCOUNTY FOR EACH ROW

  WHEN (

  NEW.COUNTYTBLKEY IS NULL

  &nbsp

  DECLARE DUMMY NUMBER;

  BEGIN

  SELECT TBCOUNTY_SEQUENCE.NEXTVAL INTO DUMMY FROM DUAL;

  :NEW.COUNTYTBLKEY:= DUMMY;

  END;

  /

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Walter Tucholski

  Ten Centuries

  Points: 1262

  More actions

  March 8, 2006 at 6:26 am

  #625379

  Lowell,

  thanks for your quick reply. That looks like it will work. My only concern is performance. If I am inserting 1,000,000 rows, the trigger has to be executed 1,000,000 times. I will give it a try.

  thanks again

  Walt

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  March 8, 2006 at 9:44 am

  #625460

  Walter,

  My Oracle skills are a little rusty, but why can't you just create a sequence (Oracle's equivalent of SQL Server identity) for the Oracle table and not bother to generate the sequence numbers on SQL Server.  Your initial post doesn't indicate that you're using the sequence number table for anything but inserting into the Oracle table.

  As I recall, you don't have to use a trigger to increment the sequence and put it into the table that uses it.

  Greg

  Greg