Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
how to set oracle sequencer to a column using ssis

Answered how to set oracle sequencer to a column using ssis

  • Thursday, April 19, 2012 11:03 AM
     
     
    Sign In to Vote
    0

    Hi everyone,

    how to set a oracle sequencer to a column.i.e, i am migrating data into oracle database .

    in my table for a column we have to set it to oracle sequencer. this can be done by using trigger

    i.e., in trigger

    If :new.column is NULL THEN

    SELECT table_SEQ.NEXTVAL INTO :NEW.column FROM DUAL;

    in pacakage for this column i am not mapping any thing trigger will populate this value it will work fine .

    but now they mentioned as "NOT NULL" constraint for that column .

    when i am running package it is giving error.i.e., column should not be null.

    Please Suggest me how to set oracle sequencer to column

    Thanks,

    Karteek

    karteek_miryala

     

All Replies

  • Thursday, April 19, 2012 12:53 PM
     
     
    Sign In to Vote
    1

    OK - I think that what you are saying is:

    You built an ssis package that inserts a record into an oracle table and then a trigger updates the New.Column with a sequence number and you are seeing an error because the column has a not null constraint and SSIS will not let you insert the record with a null value.

    If that is the case then you have a couple options that I can think of

    1) Set the new column to a flag value like -1 in the dataflow and change your trigger to update Both NULL and -1.   (Probabally the easiest)

    2) Add an OLEDB command execute to your dataflow to retreive the sequence number and insert it into the flow (it would use the same sql as your trigger and be called once per record)

    Chuck




     
  • Friday, April 20, 2012 3:23 AM
     
     
    Sign In to Vote
    0

    you are saying we have to hardcode that column with value like -1  in the package    and in the trigger  we have to change

    if :new.column is NULL or new.column= -1

      THEN

    SELECT table_SEQ.NEXTVAL INTO :NEW.column FROM DUAL;

    but my requirement is not to change code in trigger.

    can you explain me crearly for the second option. or any other better way

    karteek_miryala

     
  • Friday, April 20, 2012 1:38 PM
     
     
    Sign In to Vote
    0
    Actually it looks like #2 would not work.  There doesn't appear to be a way to get a return value from an OleDB transformation just calling an oracle select query.

    Chuck


     
  • Wednesday, May 02, 2012 6:33 AM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Hi karteek_miryala,

    Please refer to Allan's reply in the following link,
    http://stackoverflow.com/questions/2540615/how-can-i-drop-a-not-null-constraint-in-oracle-when-i-dont-know-the-name-of-t

    Since the issue is most related to Oracle, please post the question to related forum, and there are many experts focus on it.

    Thanks,
    Eileen

     
  • Wednesday, May 02, 2012 1:34 PM
     
     
    Sign In to Vote
    0

    Hi karteek_miryala,

    Please refer to Allan's reply in the following link,
    http://stackoverflow.com/questions/2540615/how-can-i-drop-a-not-null-constraint-in-oracle-when-i-dont-know-the-name-of-t

    Since the issue is most related to Oracle, please post the question to related forum, and there are many experts focus on it.

    Thanks,
    Eileen

    Hi Eileen Zhao ,

    Thanks for your response,

    i gone through

    http://stackoverflow.com/questions/2540615/how-can-i-drop-a-not-null-constraint-in-oracle-when-i-dont-know-the-name-of-t

    it describes  how to set i.e.,  alter a column NOT NULL to NULL.

    in Production can we alter table script ?

    Please NeedHelpFul

    karteek_miryala