SSIS - Adding Columns to existing table and load data

  • sunshine

    SSC Enthusiast

    Points: 114

    I am working in SSIS (Sql server version 2012) , I have a requirement to add two new columns to a destination table which already has five years of data. How can i add these two new columns and bring data to the destination table which already has 5 years of data.

    Thanks

  • Site Owners

    SSC Guru

    Points: 80385

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Jeff Moden

    SSC Guru

    Points: 994555

    sunshine wrote:

    I am working in SSIS (Sql server version 2012) , I have a requirement to add two new columns to a destination table which already has five years of data. How can i add these two new columns and bring data to the destination table which already has 5 years of data.

    Thanks

    1. Don't use SSIS to add columns.
    2. Post the CREATE TABLE statement for the table you want to add the columns to, the names and datatypes of the new columns, and any indexes and constraints (especially any FK's) you may have on the table.  This will help us help you determine the best method.
    3. We also need to know the average row size of the the rows in the clustered index of the table.

     

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715401

    Are you going to default the old rows of data to some value in the new columns?

  • latkinson

    Ten Centuries

    Points: 1174

    Is the previous 5 years worth of data available for you to retrieve the 2 new columns for your historical data?

    If its not, then you can only modify your SSIS package to incorporate the 2 new columns in a going forward basis, with the old data having null values or a value indicating unavailable.

    if that historical data is available, I'd go with either a standalone SSIS package to upsert the existing rows, or use the T-SQL Merge statement to perform the update

    It goes without saying that your existing process and destination table will have to be modified to incorporate the new columns.

     

    Luther

     

Viewing 5 posts - 1 through 5 (of 5 total)

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