locked
Returning multiple schema objects to an ADO.NET Schema For Each Loop Enumerator RRS feed

  • Question

  • I am trying to use SSIS as a profiling tool to capture and recreate schemas and then load them with data.  I can use the For Each Loop's ADONET Schema Generator to grab a collection of tables but I would like to grab both the tables and the columns to recreate the basic schema in a new system.  Can I enumerate more than one object?
    Thursday, April 19, 2012 6:41 PM

Answers

  • You can obtain the DDL (or particularly the create table scripts) using SMO as this is not SSIS's primary task to produce them out of the box.

    Arthur My Blog

    • Proposed as answer by Eileen Zhao Monday, April 23, 2012 9:00 AM
    • Marked as answer by Eileen Zhao Thursday, April 26, 2012 6:10 AM
    Friday, April 20, 2012 5:59 PM

All replies

  • Might be better off just querying the Information_Schema.Columns view if that is what you are looking for.  That would give you all the info you need to recreate tables and columns

    select * from INFORMATION_SCHEMA.COLUMNS


    Chuck

    Thursday, April 19, 2012 7:07 PM
  • Shredding this ADO recordset (that would contain the needed number of objects with their corresponding schemas should work), see how:

    http://www.sqlis.com/post/Shredding-a-Recordset.aspx

    Creating the objects is then trivial using Execute SQL Task where the variable is the source of the SQL statements.


    Arthur My Blog

    Thursday, April 19, 2012 7:15 PM
  • How does that result in programmatically re-creating the schema in a new instance?  My understanding is that a For Each Loop would be needed to take the dataset and then create variable driven expressions to loop through each create table and columns statement.

    Friday, April 20, 2012 12:57 PM
  • Thanks Arthur, since I am capturing the tables and associated columns of a schema, the object source variable consumed by the ADO Enumerator will have variable numbers of columns coming in per recordset.  Will variable mapping a single variable, say myColumns, effectively consume a varied column set that can be consumed in an expression?  The specialized handling of the table and column structure is what I thought I was buying with the ADO.NET Schema Enumerator.

    Friday, April 20, 2012 1:10 PM
  • I thought you capture the schema as a single text field. e.g. the entire CREATE TABLE statement would comprise one field. So your approach should be:

    Recordset:

    Column1 - Table Name Column 2 - DDL

    e.g.

    tblTest  CREATE TABLE dbo.tblTest (id_tblTest int not null, blah char (50), ... ) on PRIMARY

    tblSample ... CREATE TABLE etc.

    This creates entities of course, but to apply changes you need to come up with the ALTER statements, this is more work, and outside the scope of your question.

    So back to it, when you iterate over such a recodset you get one variable with the table, or db name, and its corresponding DDL.


    Arthur My Blog

    Friday, April 20, 2012 3:14 PM
  • so this is assuming that i already have the create table scripts stored somewhere?  i dont see where in the example i am pulling the schema definitions programmatically from the data source using a for each loop.

    Friday, April 20, 2012 4:42 PM
  • You can obtain the DDL (or particularly the create table scripts) using SMO as this is not SSIS's primary task to produce them out of the box.

    Arthur My Blog

    • Proposed as answer by Eileen Zhao Monday, April 23, 2012 9:00 AM
    • Marked as answer by Eileen Zhao Thursday, April 26, 2012 6:10 AM
    Friday, April 20, 2012 5:59 PM