How to add new column in SSIS Package at run time

  • Hi All,

    I am new to SSIS, I created one new package and i want to add one new column in the destination table at package runtime.

    How can do this

    Anybody know this please tell me the solution.

    Kindest Regards,

    Sarath Vellampalli

  • In the 'data flow' editor add the 'Derived Column' component between your source and destination. There you can add new columns as well as perform some clean up operations on the source columns. I typically use this to add the time the rows are being inserted and perform any kind of cleanup on the source data.

    Hope this Helps!

    --

    Ben


    Cheers,

    Ben Sullins
    bensullins.com
    Beer is my primary key...

  • Hi Ben,

    thanks for your help on SSIS, but how can use that task, can u tell me the procedure of 'Derived Column Task' for adding new column to destination table.

    while i am trying to add new column to destination it shows some error like expression property was empty.

    please send the steps of this task.

    Kindest Regards,

    Sarath Vellampalli

  • Have you been through either the samples for SSIS or Books OnLine (BOL) (see here for a 'how-to' for this exact topic)

    Steve.

  • Thanks Steve

    Kindest Regards,

    Sarath Vellampalli

  • Sarath Vellampalli (5/7/2007)


    Hi All,

    I am new to SSIS, I created one newpackage and i want to add one new column in the destination table at package runtime.

    How can do this

    Anybody know this please tell me the solution.

  • Sarath Vellampalli (5/7/2007)


    I created one newpackage and i want to add one new column in the destination table at package runtime.

    If you want to add a column at runtime in the schema of the destination table, you need to issue an ALTER TABLE ADD ... command in an Execute SQL Task. Your dataflow will however crash like it has never crashed before, as SSIS has strongly-typed metadata (aka you cannot change it at runtime).

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

  • In my case I added a fiscalyearcolumn to the destination table, for which there is no corrolary in the source spreadsheet, but the value will always be '2012' for each imported row.

    Since your situation was close to mine, would you please share with me the name of the data flow object you use to generate the values that must end up in the destination table, during run-time?

  • Sarath Vellampalli (5/7/2007)


    i want to add one new column in the destination table at package runtime.

    Even in the world of ETL, never mind that of an RDBMS, this is normally a very bad idea. What type of column are you adding and what is the business reason behind adding it?

    --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)

  • 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?

  • when I look at the input properties for fiscalyearnumber column it shows expected datatype DT-I2 which is what I am supposed to use when SQL table expects smallint.

  • hxkresl (12/4/2011)


    The column status returned was: "The value violated the integrity constraints for the column.".

    Will you please give me some idea why?

    Any nulls in the data? Does the table have a NOT NULL constraint on that column?

    --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)

  • That is an excellent question. When I do the following for the conversion data flow, the import works!

    ISNULL([FiscalYearNumber])? "2012" : [FiscalYearNumber]

    Thank you.

  • 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

  • Gosh, Helen... my apologies. I've reached the limit of my SSIS knowledge (which is quite small, actually) and don't know the answer to your latest problem. Hopefully, someone else will chime in soon.

    --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)

Viewing 15 posts - 1 through 15 (of 28 total)

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