Post reply

How to add new column in SSIS Package at run time

  • December 5, 2011 at 5:30 am

    #1416609

    hxkresl (12/4/2011)

    Jeff,

    would you consider looking at one more issue? I just added another column to the excel source so that it would map to an existing varchar (15) column on the SQL destination called BusinessOwner. The package *this time* executes without error but nothing populates the destination table.

    Flow is: Excel Source->Derived Column->Data Conversion->OLE DB Connection.

    Inside the derived column data flow object I added the conditional statement

    ISNULL(BusinessOwner) ? "myalias" : BusinessOwner.

    with the expectation that it will put my alias in the destination column for every row that is empty.

    In data converstion data flow object I specify string [DT_STR] as data type to match the varchar(15).

    Like I said, no errors running the pkg but nothing populates the BusOwner column on the destination table.

    Thanks so much. Helen

    Did you map the new column in the OLE DB Destination?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 5, 2011 at 10:19 am

    #1416851

    Hi Jeff. Well, I thought my OLE DB output was overwriting destination table each time I ran the package but I was wrong. When I truncated the destination table and reran the pkg I found the import populated as needed.

    Ridiculous that it took me so long to try truncate... Thanks for standing by.

  • December 5, 2011 at 7:52 pm

    #1417094

    hxkresl (12/5/2011)

    Hi Jeff. Well, I thought my OLE DB output was overwriting destination table each time I ran the package but I was wrong. When I truncated the destination table and reran the pkg I found the import populated as needed.

    Ridiculous that it took me so long to try truncate... Thanks for standing by.

    Then it is I who have learned. 🙂 Thanks for the very considerate feedback, Helen.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 5, 2011 at 11:25 pm

    #1417117

    Either you can clean up the data or in the Destination uncheck the checkbox "Check constrainte"

    Thanks,

    Nikesh

  • December 6, 2011 at 12:06 am

    #1417122

    nike_tly (12/5/2011)

    Either you can clean up the data or in the Destination uncheck the checkbox "Check constrainte"

    Thanks,

    Nikesh

    I'm not following. Which question are you trying to answer?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 6, 2011 at 3:55 am

    #1417194

    hxkresl (12/4/2011)

    OK, thank you very much for replying!!!

    so, I've changed my approach. I've manually added the column in source file and on destination sql table as smallint.

    My flow is:

    Excel Source -> Derived Column -> Data Conversion -> OLE DB Destination and my error is now only at the last data flow task.

    1. Excel Source maps FiscalYearNumber

    2. Derived Column does conversion so that for a given empty column it will supply the value "2012" FiscalYearNumber==”0”? “2012”: [FiscalYearNumber]

    3. Data Conversion converts data type for this column to two-byte signed integer.

    4. OLE Destination goes red with the following error:

    [OLE DB Destination [684]] Error: There was an error with input column "Copy of FiscalYearNumber" (1170) on input "OLE DB Destination Input" (697). The column status returned was: "The value violated the integrity constraints for the column.".

    Will you please give me some idea why?

    whenever you get this kinda error it must be a duplicate records (primary key violation) or column does not allow null values...it means you are going to insert null values where you are not supposed to do that...well is that FiscalYearNumber column defined not null or primary key? check this out and if it is like what i said....put a conditional split not to allow null values...before destination or check for duplicate values.........

    Thanks,
    Charmer

  • December 6, 2011 at 4:58 am

    #1417233

    Charmer (12/6/2011)

    hxkresl (12/4/2011)

    OK, thank you very much for replying!!!

    so, I've changed my approach. I've manually added the column in source file and on destination sql table as smallint.

    My flow is:

    Excel Source -> Derived Column -> Data Conversion -> OLE DB Destination and my error is now only at the last data flow task.

    1. Excel Source maps FiscalYearNumber

    2. Derived Column does conversion so that for a given empty column it will supply the value "2012" FiscalYearNumber==”0”? “2012”: [FiscalYearNumber]

    3. Data Conversion converts data type for this column to two-byte signed integer.

    4. OLE Destination goes red with the following error:

    [OLE DB Destination [684]] Error: There was an error with input column "Copy of FiscalYearNumber" (1170) on input "OLE DB Destination Input" (697). The column status returned was: "The value violated the integrity constraints for the column.".

    Will you please give me some idea why?

    whenever you get this kinda error it must be a duplicate records (primary key violation) or column does not allow null values...it means you are going to insert null values where you are not supposed to do that...well is that FiscalYearNumber column defined not null or primary key? check this out and if it is like what i said....put a conditional split not to allow null values...before destination or check for duplicate values.........

    Good answer. That's why it was used to solve the problem about 6 posts back. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 7, 2011 at 1:56 am

    #1417850

    Jeff Moden (12/6/2011)

    Charmer (12/6/2011)

    hxkresl (12/4/2011)

    OK, thank you very much for replying!!!

    so, I've changed my approach. I've manually added the column in source file and on destination sql table as smallint.

    My flow is:

    Excel Source -> Derived Column -> Data Conversion -> OLE DB Destination and my error is now only at the last data flow task.

    1. Excel Source maps FiscalYearNumber

    2. Derived Column does conversion so that for a given empty column it will supply the value "2012" FiscalYearNumber==”0”? “2012”: [FiscalYearNumber]

    3. Data Conversion converts data type for this column to two-byte signed integer.

    4. OLE Destination goes red with the following error:

    [OLE DB Destination [684]] Error: There was an error with input column "Copy of FiscalYearNumber" (1170) on input "OLE DB Destination Input" (697). The column status returned was: "The value violated the integrity constraints for the column.".

    Will you please give me some idea why?

    whenever you get this kinda error it must be a duplicate records (primary key violation) or column does not allow null values...it means you are going to insert null values where you are not supposed to do that...well is that FiscalYearNumber column defined not null or primary key? check this out and if it is like what i said....put a conditional split not to allow null values...before destination or check for duplicate values.........

    Good answer. That's why it was used to solve the problem about 6 posts back. 😉

    Sorry i was not seeing your post.....

    Thanks,
    Charmer

  • December 7, 2011 at 2:07 am

    #1417852

    But Guess What, Charmer? I have another problem and I bet you can help me 🙂

    Dataflow tasks are as follows:

    OLE DB source -> OLE DB destination

    The source is a SQL staging table, the destination is the SQL target table. The datatypes for the columns I want to bring from staging to destination are already matching, so should not require data converstion, YET, I am getting a datatype error.

    [OLE DB Destination [119]] Error: There was an error with input column "POAmountLC" (146) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".

    Unbelievable.

    Datatype of POAmountLC in the SQL tables is Decimal (15,2), and in SSIS I have tried using DT_numeric and DT_decimal, the latter of which is compatible with Decimal (15,2).

    Can you help?

  • December 7, 2011 at 2:14 am

    #1417858

    hxkresl (12/7/2011)

    But Guess What, Charmer? I have another problem and I bet you can help me 🙂

    Dataflow tasks are as follows:

    OLE DB source -> OLE DB destination

    The source is a SQL staging table, the destination is the SQL target table. The datatypes for the columns I want to bring from staging to destination are already matching, so should not require data converstion, YET, I am getting a datatype error.

    [OLE DB Destination [119]] Error: There was an error with input column "POAmountLC" (146) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".

    Unbelievable.

    Datatype of POAmountLC in the SQL tables is Decimal (15,2), and in SSIS I have tried using DT_numeric and DT_decimal, the latter of which is compatible with Decimal (15,2).

    Can you help?

    is there any null values going into that column...?

    Thanks,
    Charmer

  • December 7, 2011 at 2:14 am

    #1417859

    hxkresl (12/7/2011)

    But Guess What, Charmer? I have another problem and I bet you can help me 🙂

    Dataflow tasks are as follows:

    OLE DB source -> OLE DB destination

    The source is a SQL staging table, the destination is the SQL target table. The datatypes for the columns I want to bring from staging to destination are already matching, so should not require data converstion, YET, I am getting a datatype error.

    [OLE DB Destination [119]] Error: There was an error with input column "POAmountLC" (146) on input "OLE DB Destination Input" (132). The column status returned was: "The value violated the integrity constraints for the column.".

    Unbelievable.

    Datatype of POAmountLC in the SQL tables is Decimal (15,2), and in SSIS I have tried using DT_numeric and DT_decimal, the latter of which is compatible with Decimal (15,2).

    Can you help?

    What constraints do you have on that column in the destination database? A unique index?

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • December 7, 2011 at 2:21 am

    #1417865

    Ah yes, Charmer. It was simple and you are correct.

    CHA CHA CHA. It WAS a constraint. Those darn Nulls get me every time.

    I hope I have finally learned. Thank you ALL.

  • December 7, 2011 at 2:27 am

    #1417870

    hxkresl (12/7/2011)

    Ah yes, Charmer. It was simple and you are correct.

    CHA CHA CHA. It WAS a constraint. Those darn Nulls get me every time.

    I hope I have finally learned. Thank you ALL.

    it happens...we don't give attention to silly things when you have learned a lot....:-)

    Thanks,
    Charmer

  • December 7, 2011 at 4:12 pm

    #1418417

    I have one more problem....but I don't want to double post. However it is at this link.

    http://www.sqlservercentral.com/Forums/Topic1218249-148-1.aspx?Update=1

    :rolleyes:

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply