Loop through excel column names using SSIS

How do I get the field names from a specific excel sheet in an excel file? There was a question asked on SSIS MSDN forum which forced me to blog on this.

Suppose the excel file has more than one sheets and the sheet we are interested in is ImpData which has two columns (Data1 and Data2). The aim is to get these field names and check if these fields are present in a specific sql server table. If fields Data1 and Data2 are not present in the table then add them to the table. Once the columns are added to the table load the excel data to the sql server table.

Step1:Create a table ExcelFields (Field1 Varchar(20))

Step2:Create 4 package scoped string variables SheetName, FieldName, ExcelSheetName and AlterSQL. Set the value for SheetName as ImpData$ as this is the sheet that contains the relevant data to be fetched and loaded to sql server table.

Step3:Create an ado.net connection for the excel file. Use a foreach loop to iterate through excel sheets and save each of the sheet name in ExcelSheetName variable. Check my blog on how to create ado.net connection manager and save the excel sheet name in a variable.

Step4:Have a foreach loop within the foreach loop used to capture the sheet name (Step3) and click on Collection in the left pane in foreach loop editor. Select the enumerator as "Foreach ADO.NET Schema Rowset Enumerator". Under enumerator configuration select the ado.net connection created in Step2. Select "Columns" as Schema from the drop down box.
Click on "SetRestrictions" button and select Table_Catalog, Table_Schema and Table_Name. Highlight Table_Name and Select SheetName variable as shown:

 

Step5:Click on Variable Mappings in left pane and select the variable FieldName (created in Step1) and set the index as 3.

Following is the control flow so far:

Step6: Put a dummy script task inside the outer foreach loop and hook it to the inner foreach loop container. Add a precedence constraint (Evaluation operation as expression and constraint and value as success)using following expression: @[User::SheetName]==@[User::ExcelSheetName].

Step7: Set the evaulate as expression property for variable AlterSQL as true and set the following expression: "Alter Table ExcelFields Add " +  @[User::FieldName] + "  Varchar (50)"

Step8: Add an execute sql task inside the inner foreach loop and set the connection manager to point to the table that needs to be altered. Use AlterSQL as the sql source in execute sql task.

Step9: An execute sql task can be added out of the both the foreach loops and be used to load the excel data to table using the distributed queries mentioned here.

Note: The expression for AlterSQL can be modified to check and ignore if the fields are already present in the table

Loop through excel sheets using SSIS

Control flow of the package once you complete Step1 to Step6 will look like:

Scenario:
I have 2 excel workbooks EPL.xls (with 3 excel sheets inside) and SPANISH.xls (with 2excel sheets inside).All the 5 sheets have same number of columns with same name as well. I want to design a SSIS package to read all these 5 sheets and put all the records in a text file.
Declare 2 string variables ExcelPath and ExcelSheet in the scope of package.

Step1: Take for each loop which will loop through all the excel workbooks inside a particular folder. Edit for each loop as shown in the figure and click on Collection in left pane. My excel workbooks are in F:\MyStuff folder so I have used the location of the workbooks in the Folder option. Since I want to loop trough al the excel files, I have given *.xls in the Files option.

Step2:

Capture the excel workbook name in a string variable. I have defined a variable ExcelPath of type string. Click on variable mappings and select the ExcelPath variable from the drop down box with Index as 0 as shown in the following figure:

Step3:

Create an ADO.NET connection manager. Right click on the connection manager’s pane and select New ADO.NET Connection.Then click on New in Configure ADO.NET Connection Manager window and configure the connection manager as shown in the figure and click OK

Browse to an excel workbook in MyStuff folder as shown in the following figure:

Click All in the left pane and enter Excel 8.0 in Extended Properties and then click Test Connection to verify if the connection is successful. Now an ADO.NET connection manager is created with name as EPL. Go to properties of the EPL and click the ellipsis (…) against the Expressions.In the Property Expression Editor, select Connection String and set the expression as following:

"Data Source=" + @[User::ExcelPath] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;"
Step4: Now take one more For each loop inside the previous one and configure it as shown in the following figure:

Go to variable mappings and select ExcelSheet variable to capture the sheet name and set Index as 2.

Step5:
EPL.xls has 3 work-sheets : MANCHESTERUNITED,CHELSEA AND LIVERPOOL. Go to the variable ExcelSheet and set its value as CHELSEA$.Drag a Data Flow Task inside the inner for each loop. Take a Excel Source inside Data Flow Task and double click the excel source. Click New to create an excel connection manager using a valid excel file which has same structure as the excel files in F:\MyStuff folder. In this example I am taking EPL.xls which is sitting in F:\MyStuff folder. Check the "First row has column names" check box and click OK. Select Data Access Mode as Table Name or Vie Name variable and select the ExcelSheet variable from the "Variable Name" drop down box . Click on Columns in Excel Source edotor to verify the column names and then click OK. Go to the properties of Excel Connection Manager and use the ellipsis (…) against the expressions to set the Connection String property using following expression:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::ExcelPath]+";Extended Properties=\"" + "Excel 8.0;HDR=YES\"" + ";"

Step6:
I will capture all the records from the sheets into one text file. I have created a text file Information.txt. Then grab a Flat File Destination and double click on it to open the editor. Uncheck the "Overwrite data in File" option and click New to create a flat file connection manager. Verify that the Delimited radio button is selected and then click OK. Configure the Flat File connection manager editor as shown in the following figure:

Click on columns then mappings and click OK. Now we are ready to execute the package.