Below are the steps to configure an Excel dynamically in
foreach loop container:
- Create a Package name DynamicExcelSrc.dtsx.
- Add two package variables
- These variables will be used to store the path of our Excel sources.
- Now add a
ForEach Loop container named
- Go to the Collection tab, select Enumerator as Foreach File Enumerator.
- In the Enumerator Configuration section, browse the file which we want to load.
- 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:
- Now we will configure it with the variable which we added.
- Go to the “Expression”, click on it.
- Once you click on it “Property Expression Editor” will popup, this is where we can configure our folder path.
- Now select “Directory” from “Property” and click on “Expression”.
- This will open a new popup ExpressionBuilder.
- Now select the second variable, i.e.,
varFullFilePath, this will store the full file path and file name with extension.
- 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”.
- Please refer to the screenshot below:
- Now go to the “Variable Mapping” tab, select the variable
varFullFilePath. This will store the full path and file of the source.
- We have now configured our Foreach Loop container.
- Now drag and drop a Data Flow Task (DFT) named
- Double click on it, and add an “Excel Source” named
- Double click the Excel source, go to Connection Manager.
- Select the connection, i.e., the source file for extraction.
- Select the name of the Excel sheet and go to the columns to select the columns which we need to extract.
- When you see the “Connection Manager” area, the Excel connection manager will be added.
- Rename it to
- Now we will configure this Excel so that it can dynamically select the files.
- Right click on the newly created Excel connection manager, i.e., “EXL_SrcEmployess” and select Properties.
- In the property window, first go to the “ConnectionString” property and copy the connection string. It will look as below:
Extended Properties="Excel 12.0 XML;HDR=YES";”
- Now once you copy this connection string, go to the expression property.
- Select the “
ConnectionString” property and paste it in the expression window, then do modification as shown below:
Properties=\"Excel 12.0 XML;HDR=YES\";"
- Please make a note of this step, as this step will only fetch the Excel files from the source folder.
- Once this is done, we have configured our Excel Connection Manager.
- Next step, we will move the processed files from the source folder to the destination folder.
- For this, create a destination folder.
- Now drag & drop
FileSystemTask, go to the editor of
- In the “General” tab, go to “Source Connection”.
- Here we have two properties:
- We will set
IsSourcePathVariable to True.
SourceVariable, we will select the second variable, i.e.,
- This will point to the current file which is processed by our
- Now select the Operation as
- Go to “Destination Connection”, here we will set our
DestinationConnection, select the folder where you want to move your files.
- The third property is
OverwriteDestination, we will set this to
- This will overwrite the files when we are moving.
- Once done, execute the package.