Click here to Skip to main content
Click here to Skip to main content

Dynamically Configure Excel in Foreach Loop Container

By , 3 May 2012
Rate this:
Please Sign up or sign in to vote.

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:

    ForEachConfig1.JPG

  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:

    ForEachConfig2.JPG

  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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

BIUser_2011

India India
No Biography provided

Comments and Discussions

 
QuestionDynamic Excel Names PinmemberDavid Thurman3-Jan-14 11:01 
QuestionSincere Thanks! PinmemberCaesarsSax11-Dec-13 4:04 
Questionits not working PinmemberMember 823625811-Jul-12 10:36 
AnswerRe: its not working PinmemberMember 99924323-May-13 7:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140415.2 | Last Updated 3 May 2012
Article Copyright 2012 by BIUser_2011
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid