Here's one way, using ADO.Net + ODBC connection... (this is more or less from my working SSIS 2008/2010 ScriptTask C# code...)

The methodology is similar for ADO.Net + SqlConnection. SqlConnection also has a GetSchema() method.

var conn = new OdbcConnection();

conn = (OdbcConnection)Dts.Connections["connMgrName"].AcquireConnection(null);

var tbls = conn.GetSchema("Tables");

var tblCols = conn.GetSchema("Columns");

var sql = new StringBuilder();

foreach(DataRow t in tbls.Rows) {

sql.AppendFormat("create table {0}(", t["TABLE_NAME"]);

foreach(DataRow c in tblCols.Rows) {

//the actual field names returned by GetSchema("COLUMNS") can change depending on the source ODBC driver...

//grok conn.GetSchema("DataTypes") to see what the specific driver returns for data types...

//for some ADO.Net drivers, there are XML docs here that show the mappings used by SSIS:

// C:\Program Files (x86)\Microsoft SQL Server\100\DTS\MappingFiles

// I don't know if other 3rd-party drivers do or not...

var dataType = c["TYPE_NAME"].ToString().Trim();

var colName = c["COLUMN_NAME"].Value;

var colLength = (int)c["COLUMN_SIZE"].Value;

var colRadix = c["NUM_PREC_RADIX"];

var colDigits = c["DECIMAL_DIGITS"];

//this is naieve code below, assuming 1:1 mapping between source and destination data type names

//and nothing fancy, like varchar(n) vs varchar(max) for SQL Server...

//To see all the different metadata collections returned by a given ODBC Datasource,

//grok the output from GetSchema("MetaDataCollections").

switch(dataType) {

case "CHAR":case "VARCHAR":

sql.AppendFormat("{0} {1}({2}),", colName, dataType, colLength);

default: //Integer, SmallInt, DateTime, etc...

sql.AppendFormat("{0} {1},", colName, dataType);

}

}

sql.Append(")");

//need to fix the last line or so...

sql.Replace( ",)", ")" );

}

MessageBox.Show(sql.ToString(), "Create table DDL...");

//return the sql string to a package variable so it can be used later in an ExecuteSQL task

Dts.Variables["User::pkgCreateTableSql"].Value = sql.ToString();

Dts.TaskResult = (int)ScriptResults.Success;

----