Previously, I have posted some SSIS solutions which would handle dynamic column mapping in data loading. This time, I was wondering how to handle dynamic column mapping when the source is an excel file. I was quick to realize that it would be entirely a C# solution and the program should be able to handle any change in the number or name of columns. In simple words, you give any excel file and the program will load it into database. I used GetOleDbSchemaTable in order to fetch the schema information from the excel file, then built a similar table in the database. Excel data types and SQL data types do not match. Sometimes, they make me panic while loading files using SSIS. In my program also, I had to do some change in the data types; like WChar becomes NVARCHAR, Double becomes INT. Yes, I could use an XML file/database table to save a detailed mapping of this conversion of excel data types to SQL data types to make my program more capable and versatile. But, the conversion is hard-coded at the moment. Another challenge was to get all the data loaded to SQL table without using any column names or hard coded column mapping. SQLBulkCopy came to my rescue. By the way, I had to use a dataview over the table returned by GetOleDbSchemaTable because it was returning the column names in alphabetical order. So, I had to sort the rows using “ORDINAL_POSITION”
using System;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Windows;
namespace ConsoleApplication2
{
class Program
{
public static void Main()
{
try
{
///*****************Establish Excel Connection*****************///
String strExcelConn = “Provider=Microsoft.ACE.OLEDB.12.0;”
+ “Data Source=C:\\files\\Book1.xlsx;”
+ “Extended Properties=’Excel 8.0;HDR=Yes'”;
OleDbConnection connExcel = new OleDbConnection(strExcelConn);
OleDbCommand cmdExcel = new OleDbCommand();
cmdExcel.Connection = connExcel;
///*****Get schema info and Read Sheet Name and save it as TableName *******//
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string TableName = dtExcelSchema.Rows[0][“TABLE_NAME”].ToString();
string Query = “CREATE TABLE “ + TableName + “(“;
/*****Get schema info and read the column names and build Query to Create similar SQL table*****/
//
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, TableName, null });
connExcel.Close();
string FieldNames = “”;
DataView dv = new DataView(dtExcelSchema);
dv.Sort = “ORDINAL_POSITION”;
foreach (DataRowView rowView in dv)
{
DataRow row = rowView.Row;
Query += row[“COLUMN_NAME”].ToString() + ” “ + ((OleDbType)row[“DATA_TYPE”]).ToString() + “,”;
FieldNames += row[“COLUMN_NAME”].ToString() + “,”;
}
/////******************Replace data types************/////
Query = Query.TrimEnd(“,”.ToCharArray()) + “)”;
Query = Query.Replace(“WChar”, “NVARCHAR(500)”);
Query = Query.Replace(“Double”, “INT”);
FieldNames = FieldNames.TrimEnd(“,”.ToCharArray()) + “”;
/******************read records from excel file************/
cmdExcel.CommandText = “Select “ + FieldNames + ” from [“ + TableName + “]”;
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter();
connExcel.Open();
da.SelectCommand = cmdExcel;
da.Fill(ds);
connExcel.Close();
//******** Establish SQL Server connection and create table*****//
SqlConnection conn = new SqlConnection();
string sqlConn = “Data Source=YourServerName;Initial Catalog = YourDBName; Integrated Security = SSPI;Persist Security Info=False;”;
conn.ConnectionString = sqlConn;
SqlCommand cmd = new SqlCommand(Query);
cmd.Connection = conn;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
//*****************Do bulk copy to table*****************//
DataTable dt = new DataTable();
dt = ds.Tables[0];
SqlBulkCopy sbc = new SqlBulkCopy(conn);
sbc.DestinationTableName = TableName;
conn.Open();
sbc.WriteToServer(dt);
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Now, imagine that we received an ad-hoc request from our source data provider that total Hardware Sales for Apple should be 9000 and that of Google should be 12000. There have been some unreported sales for both these companies which do not fit into the available sub categories (phone and tab). So, the requirement becomes likes this: For Apple, Phone sales is 1000 and Tab Sales is 7000, but total Hardware Sales is 9000 (1000 greater than children sum) For Google, Phone sales is 8000 and Tab Sales is 0, but total Hardware Sales is 12000 (4000 greater than children sum) To solve this, first we have to insert Hardware sales for all companies into Fact Table. Id for Hardware is 4. Newly inserted records are shown below: 
Now, process the cube and final result looks like this: 
