locked
SSIS Copy Schema and data from Source to destination RRS feed

  • Question

  • Hello

    Senario:

    Source table: (Initially)

    ID             Name

    1              a

    2               b

     

    Destination:

    ID          Name

    1           a

     

    i applied slowly changing dimention and copied/updated the data from source to destination. It is working fine.

    Now if i add one more column to source table, say age, i want that the destination table also get updated as source along with the data. Is this possible?

     

    Regards

    Rohit 

     

    Wednesday, January 25, 2012 10:05 AM

Answers

  • No. SCD wizard is for detecting changes in the data, not in the metadata.
    There is no out-of-the-box transformation that can do this for you. There are 3rd party tools that allow you to compare schema's between different databases and generate the alter statements for you.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Proposed as answer by COZYROC Wednesday, January 25, 2012 3:51 PM
    • Marked as answer by Eileen Zhao Monday, February 6, 2012 2:58 AM
    Wednesday, January 25, 2012 10:50 AM

All replies

  • So you want to update the metadata of the destination column according to the changes in the source schema?

    That is a bit dangerous, but possible.
    You can dynamically construct an ALTER TABLE ADD COLUMN statement using the INFORMATION_SCHEMA.COLUMNS DMV.
    It will be a quite difficult query, as you need to retrieve missing columns, their datatype, possible precision and nullability.

    Also be aware that the nullability can lead to errors. For example, if you add a NOT NULL column in the source and you add the same one in the destination, you'll get an error as the older rows will contain NULL values for that column (unless you specify a default constraint).


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    Wednesday, January 25, 2012 10:18 AM
  • cant i do it using Slowly changing dimension or some other transformation like that?
    Wednesday, January 25, 2012 10:41 AM
  • No. SCD wizard is for detecting changes in the data, not in the metadata.
    There is no out-of-the-box transformation that can do this for you. There are 3rd party tools that allow you to compare schema's between different databases and generate the alter statements for you.


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Answer #1: Have you tried turning it off and on again?
    Answer #2: It depends...
    • Proposed as answer by COZYROC Wednesday, January 25, 2012 3:51 PM
    • Marked as answer by Eileen Zhao Monday, February 6, 2012 2:58 AM
    Wednesday, January 25, 2012 10:50 AM