Dynamically Create Tables Based on an Access Table

Recently, I was assigned the task of importing several Access tables into a SQL Server 2008 R2 database via an SSIS package. Easy enough, but the twist was that the Access table definitions could change at any time.  Users could add, remove or modify columns as they saw fit. The challenge was to determine a way to dynamically create and load the ever changing Access tables into corresponding tables on the SQL Server database.   

This challenge was met by using a script task in Visual Studio 2008 to create the tables and load the data. The main workhorse in the task is the DataReader object.  The DataReader has a method called GetSchemaTable, which returns the metadata of a table. Once the metadata for each column in the Access table was obtained, a create table statement was generated and executed on the target SQL Server catalogue. Finally, SqlBulkCopy was used to load the Sql Server table with the Access table data. 

A detailed breakdown of the script task is listed below. Please note anything in brackets <TableName> are placeholders to be replaced with the applicable object names.

Step 1 Create a Connection to Access source

System.Data.OleDb.OleDbConnection conn = new
System.Data.OleDb.OleDbConnection();
conn.ConnectionString = accesscon.ToString();
conn.Open();         

Step 2 Execute the command to populate the OleDbDataReader object with the source table data

OleDbCommand cmd = new OleDbCommand("Select * from <TableName>", conn);  
OleDbDataReader reader = cmd.ExecuteReader();

Step 3 Call the GetSchemaTable method to populate a datatable with the source table  metadata.

DataTable dt = reader.GetSchemaTable();

Step 4 Loop through the datatable retreiving the column name and datatype definition

The key to understanding how this works is to know how to find and map the metadata properties in the DataTable Object. Adding a break point to the script task as it loops through the rows of the object that contains the Schema data allows us to see the mapping.

The Row in the DataReader is populated with the metadata associated with the row.

Using the Name of the property of the Row (located in the Value Column) gather the information needed to create a matching table in SQL Server.

//Loop thru each row in the data table
//Get the column name using the {ColumnName} property of the row
name = "[" + (string)Row["ColumnName"] + "]";
//Next determine the Data type using the {DataType} property of the row
    if (Row["DataType"].ToString().Contains("DateTime"))
    {
     datatype = "[DateTime]";
    }
    else if (Row["DataType"].ToString().Contains("Int") ||
             Row["DataType"].ToString().Contains("Boolean"))
    {
    datatype = "[bigint]";
    }
    else if (Row["DataType"].ToString().Contains("Double"))
    {
    datatype = "[Money]";
    }
    else
    {
    datatype = "[varchar]";
    }
//If the column type is Character use the {ColumnSize} property
// to determine the size of the column in the new table being created
if (datatype.ToString().Contains("varchar"))
   {
   size = (int)Row["ColumnSize"];
   }
   else
   {
   size = 0;
   }
//switch statement to set the size of the columns (optional)

switch (size)
   {
   case 0:
   length = "";
   break;
   default:
   if(size > 700)
   {
    length = "(8000)";
   }
   else
   {
   length = "(" + (string)size.ToString() + ")";
   }
   break;
   }
 //Use metadata gathered to populate the table create statement.

abc = abc + " " + name.ToString() + " " + datatype.ToString() + " " + length.ToString() + ", ";

Step 5 Create a connection to the SQL Server database

string con = cm.ConnectionString;
System.Data.OleDb.OleDbConnection sqlConnection1=
new System.Data.OleDb.OleDbConnection(con.ToString());
sqlConnection1.Open();

Step 6 Execute the commands to create the SQL Server table

string drop = string.Empty; 
drop = "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[<Schema>].[<TableName>]') AND type in (N'U')) 
DROP TABLE [<Schema>].[<TableName>]";
string create = "Create table [<Schema>].[<TableName>] ( " + abc.ToString() + ")";
create = create.Replace(", )", ")");
OleDbCommand droptable = new OleDbCommand(drop, sqlConnection1);
droptable.CommandType = CommandType.Text;
droptable.ExecuteNonQuery();
OleDbCommand createtable = new OleDbCommand(create, sqlConnection1);
createtable.CommandType = CommandType.Text;
createtable.ExecuteNonQuery();

Step 7 Load the source data into the newly created target table

dtable.Load(reader);
SqlBulkCopy bulkcopy = new
SqlBulkCopy(con.ToString().Replace("Provider=SQLNCLI10.1;" ,                                       "").Replace("Auto Translate=False;" , ""));
bulkcopy.DestinationTableName =[<Schema>].[<TableName>]";
bulkcopy.WriteToServer(dtable);

Step 8 Close the objects

sqlConnection1.Close();
reader.Close();
conn.Close();

Prior to writing the script task, it was necessary to know the names of the column datatypes used in the definition of the Access tables. Notice in Step 4, the {DataType} property contains the column datatype. Next a series of IF statements are used to test the datatype and determine the corresponding column datatype definitions for the SQL Server table.

The GetSchema method is an easy way to retrieve the column datatypes from the source. Calling GetSchema with the parameters below will return the datatype of each column of the source table.

DataTable dataTypes = conn.GetSchema("Columns", new string[] { null, null,                                        "<TableName>" });
string colName;
System.Type colType;
for (int i = 0; i < dataTypes.Columns.Count; i++)
    {
     colName = dataTypes.Columns.ColumnName;
     colType = dataTypes.Columns.GetType();
    }

Executing the code snippet above with a break point allows us to see the source table contains DataType of Int32. Once the system datatype name is known,  the logic to test for this type can be included and the target table column datatype can be based on the source table column datatype.

For additional information about the GetSchema command see: http://msdn.microsoft.com/en-us/library/ms254934(v=vs.110).aspx

For additional detail about the GetSchemaTable command see: http://msdn.microsoft.com/en-us/library/system.data.datatablereader.getschematable(v=vs.110).aspx