A lot of our learning comes from different forums we visits on daily or weekly basis. This post is also inspired by one of the popular forum which I recently visited and found a very interesting question on Ms-Excel and SSIS. The reader had asked how he can check in the Excel file, a particular column exist or not before processing the file with SSIS.

This made me to think what solution we can offer to this question. I did some finding with the Excel and BIDS and come out with one of the solution. I am sure there might be a better solution than this but I thought to share it get some more understanding. I would request you to leave some comments if you have a better solution or find this fine. So here goes my solution.

I have an Excel file which has data like this:

I want to check whether the Population column exists or not in the excel file. I created an Excel Connection Manager in my SSIS package which points to this excel file.

I added a Script Task on the control flow tab. I am using the System.Data.OleDb namespace to read the excel schema. I added following code snippet in my script task:

public void Main()
{
OleDbConnection myConn;
DataTable DT;
int blColExist;
blColExist = 0;
myConn = new OleDbConnection(Dts.Connections["ExcelManager"].ConnectionString.ToString());
myConn.Open();
DT = myConn.GetSchema("Columns");

/******************************
* Check the Column name
******************************/
foreach (DataRow DR in DT.Rows)
{
if (DR["Column_Name"].ToString() == "Population")
{
blColExist = 1;
}
}

if (blColExist.Equals(1))
{
System.Windows.Forms.MessageBox.Show("Population Column exist");
}
else
{
System.Windows.Forms.MessageBox.Show("Population Column DOES NOT exist");
}
}

As you see in the code I am reading the excel schema details about Columns. The column_name column return the name of each column in the excel file. I put an IF statement to check whether the “Population” column exist or not. After running the SSIS package I was greeted was this message box.

Wow!, the objective is met and I can check the columns existence. But I am know sure this is the only way. Let me ask it to the reader of this Post.