Creating a table
First of all, I always write data in a reusable table such as the one below:CREATE TABLE ExcelDataLoad( [Id] INT IDENTITY NOT NULL, [FileName] NVARCHAR(250) NOT NULL, [SheetName] NVARCHAR(250) NOT NULL, [Column1] NVARCHAR(250), [Column2] NVARCHAR(250), [Column3] NVARCHAR(250), [Column4] NVARCHAR(250), [Column5] NVARCHAR(250), [Column6] NVARCHAR(250), [Column7] NVARCHAR(250), [Column8] NVARCHAR(250), [Column9] NVARCHAR(250), [Column10] NVARCHAR(250), [Column11] NVARCHAR(250), [Column12] NVARCHAR(250), [Column13] NVARCHAR(250), [Column14] NVARCHAR(250), [Column15] NVARCHAR(250), [Column16] NVARCHAR(250), [Column17] NVARCHAR(250), [Column18] NVARCHAR(250), [Column19] NVARCHAR(250), [Column20] NVARCHAR(250), CONSTRAINT PK_ExcelDataLoad PRIMARY KEY (Id) )Loading data into this type of table allows me to comfortably edit it: cleaning, converting, mapping, transforming, etc. This kind of table also brings some difficulties:
- Sheet structure is different
- Excel returns an incorrect data type
- SSIS locks your file after reading it

Step 1: I started with adding a first ‘foreach’ loop. Then I set up a parameter with a data directory, enabling it to loop over all the Excel files in that directory.
Step 2: Logging is not really necessary, but I prefer keeping a record of which file I’m processing in my SSIS output.
Step 3: Read the available Excel worksheets.

The following script reads the Excel file and adds the names of each worksheet in our data flow. Then we write all this data to a RecordSet that we can use later on.
string excelFile = Variables.DataDirectory + Variables.FileName; string connString = "Data Source = " + excelFile + "; Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = \"Excel 8.0;HDR=NO;Imex=1;ImportMixedTypes=Text;\""; OleDbConnection objConn = null; DataTable dt = null; try { objConn = new OleDbConnection(connString); objConn.Open(); dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if(dt != null) { foreach(DataRow row in dt.Rows) { Output0Buffer.AddRow(); Output0Buffer.SheetName = row["TABLE_NAME"].ToString(); } } }catch(Exception ex) { //do nothing } finally { if(objConn != null) { objConn.Close(); objConn.Dispose(); } if(dt != null) { dt.Dispose(); } }
Step 4: We add another ‘foreach’ loop that will iterate over all the available worksheets.
Step 5: Logging again is not necessary, but here I keep track of which sheet I’m processing.
Step 6: This is the most important one! Instead of using an Excel File Source, we need to use a Script Source, with as OutputBuffer the maximum number of columns that you could ever get. Of course you need to make sure that your destination table has the same number of columns.

The following script is the most interesting of this blog:
String sheetName = Variables.SheetName; string excelFile = Variables.DataDirectory + Variables.FileName; string connString = "Data Source = " + excelFile + "; Provider = Microsoft.ACE.OLEDB.12.0; Extended Properties = \"Excel 8.0;HDR=NO;Imex=1;ImportMixedTypes=Text;\""; OleDbConnection objConn = null; OleDbCommand objComm = null; OleDbDataReader rdr = null; try { objConn = new OleDbConnection(connString); objConn.Open(); objComm = objConn.CreateCommand(); objComm.CommandText = "SELECT * FROM [" + sheetName + "]"; rdr = objComm.ExecuteReader(); while (rdr.Read()) { if(rdr.FieldCount > 0) { Output0Buffer.AddRow(); int i = 0; while (i < rdr.FieldCount) { if(Output0Buffer.GetType().GetProperty("Column" + (i + 1).ToString()) != null) //if column exists in outputbuffer { Output0Buffer.GetType().GetProperty("Column" + (i + 1).ToString()).SetValue(Output0Buffer, rdr[i].ToString()); } i++; } } } } catch (Exception ex) { //do nothing } finally { if(rdr != null) { rdr.Close(); } if(objComm != null) { objComm.Dispose(); } if (objConn != null) { objConn.Close(); objConn.Dispose(); } }
What does the code do?
The above set of code opens a connection to the Excel file. I SELECT * from the worksheet that I am currently processing and check how many columns are available for each row (in the OleDbDataReader) (dr.Read()) If there are more than 0 columns, then I continue and create a new row in the dataflow. Next, I want to iterate over each column of that row. To do this, I check if the column that I want to fill exists in the OutputBuffer. If so, I can fill this column with a certain cell value I’ve selected (Column/Row combination). Instead of Output0Buffer.Column1 = some_value, this script does it dynamically. This way it does not matter how many columns there are, unused columns will be displayed as NULL.
Final notes
I made use of OLEDB. This allows me to close the connection myself and not get a locked file. By using ExtendedProperties in the ConnectionString, I don’t easily get issues with wrong datatypes. Finally, thanks to the above script I can read any Excel file with any structure. Of course this SSIS Package might need some changes, depending on which requirements you have. Personally, however, I think it’s a good basis to start with. I hope this blogpost was helpful to you and that you can use it now or anywhere in the future.