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.
If you want to explore more with the Source code of this post; please visit Download Zone.
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to read Excel Meta Data?
- SSIS: How to resolve Excel Import 255 character Truncation issue?
- SSIS: Read and Export Excel data from nth Row
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
- SSIS: How to Load XML search result from Search engines like Bing, Google etc?
- SSIS: How to generate HyperLink in Excel output?
- SSIS: How to Compress/Zip your file using 7-Zip?
- SSIS: What is CheckPoints?
- SSIS: Checkpoints implementation
- SSIS: Event Handlers
- SSIS: Data Flow v/s Control Flow
