SSIS - Adding Columns to existing table and load data

• sunshine

  SSC Enthusiast

  Points: 174

  More actions

  August 23, 2019 at 10:23 pm

  #3674111

  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: 80342

  More actions

  August 24, 2019 at 11:10 pm

  #3674211

  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: 1004592

  More actions

  August 25, 2019 at 1:35 am

  #3674221

  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.

  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)
  

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738083

  More actions

  August 26, 2019 at 3:01 pm

  #3674422

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

• latkinson

  SSCommitted

  Points: 1707

  More actions

  September 4, 2019 at 1:00 pm

  #3677183

  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