Dynamically Create Tables Based on an Access Table

  • Leeann617

    SSC Veteran

    Points: 262

    Comments posted to this topic are about the item Dynamically Create Tables Based on an Access Table

  • Jonathan Mallia

    SSCertifiable

    Points: 5192

    Hi and thanks a lot for this article. I found it very interesting and I'm pretty sure this can be useful in a number of similar situations.

    Any chance to share the SSIS package?

  • jrobinson 7382

    SSC Journeyman

    Points: 78

    Fantastic script! Thanks for sharing it, and especially all the detail.

  • steve.ingle1949

    Grasshopper

    Points: 17

    Hi,

    Nice article. One thing troubles me...

    How is your approach going to be affected by Microsoft deprecating OleDB?

    Regards

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9942

    Nice article. I tried to do something like this a while back and failed. I'm sure I will have occasion to use it in the future.

  • Feifarek

    SSC Veteran

    Points: 247

    Thank you! I need to perform this exact same task, and you have saved me wrestling with digging through the documentation and trying to find the best way to transfer the data structure from Access to SQL Server.

  • corey lawson

    Hall of Fame

    Points: 3592

    This works very similarly with the ODBC .net classes instead of the OleDb classes - same GetSchema & GetTableSchema methods, same/similar result sets, etc. (been there, done this).

    What is really painful is then deciding to do the data pump in SSIS instead of using BCP.

    I'd say...don't do this unless you're doing something like creating the resultant package to be run first in BIML or using CozyRoc's custom controls. It requires doing "design time" package manipulation with the SSIS design-time libraries in a script task to set up the pipeline, map fields, etc. It's a royally fragile, not well documented, PITA.

  • Leeann617

    SSC Veteran

    Points: 262

    I'm not able to share the entire package. Thank you for the comments I hope you find this helpful

  • Leeann617

    SSC Veteran

    Points: 262

    Thank you for the comments for the question

    How is your approach going to be affected by Microsoft deprecating OleDB?

    Answer

    This is food for thought will post another article dealing with it as I encounter it

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

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