Post reply

Create Tables at Package runtime

  • July 10, 2008 at 7:28 am

    #126444

    Hi All

    Bit of a newbie question this:

    I have a simple SSIS package that takes data from my source using a Select * command, and populates a destination SQL table. The destination table is emptied prior to this task executing.

    The problems start when the source table is changed - usually a new column being added. I need to get this new column into my destination table. It is simple enough to delete the destination table before the task begins, but how do I tell SSIS to create a new table at runtime?

    Apologies if this is something that should be on page 1 of a manual, but I'm really new to SSIS!

    Thanks

    Chris

    Do not write in this space.

  • July 10, 2008 at 7:36 am

    #840808

    You probably need a "Transfer SQL Server Objects" Task

    you need to specify an SMO connection,

    There are a whole load of options

    including the drop and re-create objects, and CopySchema.

    Its was a bit flakey, but if you have the latest service packs it should work find.

  • July 10, 2008 at 7:43 am

    #840816

    Thanks Tom, I probably should have said that the source isn't SQL, but a Lotus Notes database being accessed via ODBC, so I'm not sure that your idea will work.

    Chris

    Do not write in this space.

  • July 10, 2008 at 8:04 am

    #840841

    Chris,

    I don;t know of another way to do this within SSIS, but there are several scripts available that will generate a CREATE TABLE statement for you on SQL Server - from the INFO_SCHEMA - there may be one available for Lotus notes, then its just a matter of calling that stored procedure output the results to a file and execute the file on the destination connection.

  • July 10, 2008 at 8:23 am

    #840861

    I'll take a look.

    Thanks Tom

    Chris

    Do not write in this space.

  • July 10, 2008 at 9:00 am

    #840900

    SSIS is not going to be happy with you plan.

    Even though your SQL Statement is a "SELECT *", SSIS gets the meta-data and stores it in the input and output buffer sections of every component in the data flow. None of the components will see the new columns without modifying the package.

    SSIS is not designed to dynamically handle schema changes. You could get it to do this, but it would take custom code to modify the package before the data flow reached it's validation phase.

  • July 10, 2008 at 9:28 am

    #840924

    Thanks Michael

    I reckon the simplest solution is to educate the one other person within the organisation who has the ability to modify the Lotus Notes structure - and make sure he tells me what he is planning to do, and when!

    Thanks again

    Chris

    Do not write in this space.

Viewing 7 posts - 1 through 6 (of 6 total)

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