Dynamically Create Tables Based on an Access Table

Question

Dynamically Create Tables Based on an Access Table

Leeann617

SSC Veteran

Points: 262
More actions
January 12, 2015 at 9:36 pm

#293642

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

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

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

Jonathan Mallia SSCertifiable Points: 5192 More actions · Answer 1

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: 82 More actions · Answer 2

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

steve.ingle1949 Grasshopper Points: 17 More actions · Answer 3

Hi,

Nice article. One thing troubles me...

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

Regards

Stan Kulp-439977 SSChampion Points: 10062 More actions · Answer 4

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 More actions · Answer 5

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: 3773 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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