Dynamically Configure Excel in Foreach Loop Container

Introduction

Below are the steps to configure an Excel dynamically in foreach loop container:

1. Create a Package name DynamicExcelSrc.dtsx.
2. Add two package variables varFilePath and varFullFilePath, of string type.
3. These variables will be used to store the path of our Excel sources.
4. Now add a ForEach Loop container named FELC_ProcessExcelFiles.
5. Go to the Collection tab, select Enumerator as Foreach File Enumerator.
6. In the Enumerator Configuration section, browse the file which we want to load.
7. Select the extension of the file; in our example, it is “*.xlsx”, this means we will process all those files with extension “*.xlsx”.

   Please refer to the screenshot below:

   

8. Now we will configure it with the variable which we added.
9. Go to the “Expression”, click on it.
10. Once you click on it “Property Expression Editor” will popup, this is where we can configure our folder path.
11. Now select “Directory” from “Property” and click on “Expression”.
12. This will open a new popup ExpressionBuilder.
13. Now select the second variable, i.e., varFullFilePath, this will store the full file path and file name with extension.
14. In the same way, now we will select the property FileSpec; here in the expression we need to give the extension of the file as: “*.xlsx”.
15. Please refer to the screenshot below:

    

16. Now go to the “Variable Mapping” tab, select the variable varFullFilePath. This will store the full path and file of the source.
17. We have now configured our Foreach Loop container.
18. Now drag and drop a Data Flow Task (DFT) named DFT_SRCEmployee.
19. Double click on it, and add an “Excel Source” named EXL_SrcEmployee.
20. Double click the Excel source, go to Connection Manager.
21. Select the connection, i.e., the source file for extraction.
22. Select the name of the Excel sheet and go to the columns to select the columns which we need to extract.
23. When you see the “Connection Manager” area, the Excel connection manager will be added.
24. Rename it to EXL_SrcEmployess.
25. Now we will configure this Excel so that it can dynamically select the files.
26. Right click on the newly created Excel connection manager, i.e., “EXL_SrcEmployess” and select Properties.
27. In the property window, first go to the “ConnectionString” property and copy the connection string. It will look as below:

    Provider=Microsoft.ACE.OLEDB.12.0;
    Data Source=C:\MSBI_Training_Materails\Destination_ForEach\TESTEmp1.xlsx;
    Extended Properties="Excel 12.0 XML;HDR=YES";"

28. Now once you copy this connection string, go to the expression property.
29. Select the “ConnectionString” property and paste it in the expression window, then do modification as shown below:

    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+@[User::varFullFilePath]+";Extended 
      Properties=\"Excel 12.0 XML;HDR=YES\";"

30. Please make a note of this step, as this step will only fetch the Excel files from the source folder.
31. Once this is done, we have configured our Excel Connection Manager.
32. Next step, we will move the processed files from the source folder to the destination folder.
33. For this, create a destination folder.
34. Now drag & drop FileSystemTask, go to the editor of FileSystemTask.
35. In the “General” tab, go to “Source Connection”.
36. Here we have two properties: IsSourcePathVariable and SourceVariable.
37. We will set IsSourcePathVariable to True.
38. In SourceVariable, we will select the second variable, i.e., varFullFilePath.
39. This will point to the current file which is processed by our ForEachLoop container.
40. Now select the Operation as MoveFile.
41. Go to “Destination Connection”, here we will set our IsDestinationPathVariable to false.
42. In DestinationConnection, select the folder where you want to move your files.
43. The third property is OverwriteDestination, we will set this to true.
44. This will overwrite the files when we are moving.
45. Once done, execute the package.