Call the Step connected to a For-Each Step with a group of values. (similar to an array.)

Use the GroupBy functionality for high performance File-Cherry-Pickling and for combining values for use with multivalued SharePoint columns.

When the loop runs, in stead of calling the connected Step for each row, the values assigned to the generated Variables are concatenated separated by ;#. When the group-by conditions are satisfied the connected Step is called.

You can Group-By columns from the source or you can use a Variable also with an associated transformation.
Important: The GroupBy assignments must be the last assignments in the Variables list.

Notice that the variables that defines the group holds a single value while the other variables hold multiple values separated by ;# (the data is grouped)
The format for the multivalued non GroupBy variables is: MyValue1;#MyValu2;#..
(The For-Each GroupBy generates that format. But you can also generate is yourself.)

The 2 main SharePoint use cases
  • Cherry-picking files from one or more sources and then use optimized transfer to SharePoint. By grouping files at the folder level or document library level it is possible to greatly enhances performance especially to Microsoft 365
  • Generating data for multivalued SharePoint columns such as Managed Metadata, Lookup and Choice.
Rules for using the Group-By functionality in the File Management Step
  • FileShare source/destination: Use the column “FileShare Folder”
  • SharePoint destination: Use the column “Override URL”.
    Important: Select only 1 Variable that holds the absolute destination URLs including filename. This variable may NOT be one of the grouping variables.
    In the metadata setup you can use all assignments types and transformations, including:
    [Rename file] to choose a dynamic file name in place of the source file’s name.
    [Alternative destination path] to define the destination folder for the file.
  • The destination library must be one of the grouping values. For example a folder or a Document library
  • The source file references must include the absolute path and filename (for cherry-picking he file). For SharePoint source use the column “Override URL
  • To gain performance, the source data set in the For-Each Step must be sorted by destination library and ideally also the destination path. This to gain best performance in terms og the fewest calls to the FileManagement Step and optimal use of multitasking. If the grouping columns are calculated and the source data set does not have columns suited for sorting then the sorting order can be accomplished by creating a staging table having the calculated values in separate columns. And then use that table for driving the cherry-picking operation for best performance.
  • You may use custom variables also with transformations to generate the absolute path and filename and various metadata values
  • If there is no need for multiple destination SharePoint libraries then
    use the option [x] Group all into 1
    Then all the rows from the source data set will be grouped together and there will be just 1 call to the associated FileManagement Step
  • If source or destination variables has multiple values you cannot merge with constants for example prefixing with a http://.. address. This kind of calculation must be done in the For-Each transformations.
  • You may rename the destination file by assigning the [<Rename file>] property.
  • Transformations are fully supported also for file renaming.
  • SharePoint source: The widest grouping is Site level . Your source grouping values may not span multiple sites
  • SharePoint destination: The widest grouping is Document Library. Your grouping values may not span multiple Document Libraries
  • Recommended max grouping count is 50000. Theoretically there is no limit however the internal string manipulation overhead will eat up the performance gained, unless you have a massive CPU. If you have more files per group consider regrouping using additional columns.

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment