{"id":147,"date":"2022-12-15T10:10:36","date_gmt":"2022-12-15T09:10:36","guid":{"rendered":"https:\/\/www.syntera.ch\/blog\/?p=147"},"modified":"2024-07-18T15:38:41","modified_gmt":"2024-07-18T13:38:41","slug":"nested-foreach-loops-in-azure-data-factory","status":"publish","type":"post","link":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/","title":{"rendered":"Nested ForEach loops in Azure Data Factory"},"content":{"rendered":"\n<div class=\"wp-block-columns is-layout-flex wp-container-core-columns-is-layout-f56f613f wp-block-columns-is-layout-flex\">\n<div class=\"wp-block-column is-layout-flow wp-block-column-is-layout-flow\"><div class=\"wp-block-post-author has-medium-font-size\"><div class=\"wp-block-post-author__avatar\"><img alt='' src='https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=48&#038;d=mm&#038;r=g' srcset='https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&#038;d=mm&#038;r=g 2x' class='avatar avatar-48 photo' height='48' width='48' \/><\/div><div class=\"wp-block-post-author__content\"><p class=\"wp-block-post-author__byline\">LEAD BUSINESS INTELLIGENCE<\/p><p class=\"wp-block-post-author__name\">Dimitri B\u00fctikofer<\/p><\/div><\/div><\/div>\n\n\n\n<div class=\"wp-block-column is-vertically-aligned-top is-layout-flow wp-block-column-is-layout-flow\">\n<ul class=\"wp-block-outermost-social-sharing alignright has-small-icon-size has-icon-color is-style-logos-only is-content-justification-left is-layout-flex wp-container-outermost-social-sharing-is-layout-fa5e4718 wp-block-outermost-social-sharing-is-layout-flex\"><li style=\"color: #1a4548\" class=\"outermost-social-sharing-link outermost-social-sharing-link-linkedin has-primary-color wp-block-outermost-social-sharing-link\">\n\t<a href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fwww.syntera.ch%2Fblog%2F2022%2F12%2F15%2Fnested-foreach-loops-in-azure-data-factory%2F&#038;title=Nested%20ForEach%20loops%20in%20Azure%20Data%20Factory\" aria-label=\"Share on LinkedIn\" rel=\"noopener nofollow\" target=\"_blank\" class=\"wp-block-outermost-social-sharing-link-anchor\">\n\t\t<svg width=\"24\" height=\"24\" viewBox=\"0 0 24 24\" version=\"1.1\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" aria-hidden=\"true\" focusable=\"false\"><path d=\"M19.7,3H4.3C3.582,3,3,3.582,3,4.3v15.4C3,20.418,3.582,21,4.3,21h15.4c0.718,0,1.3-0.582,1.3-1.3V4.3 C21,3.582,20.418,3,19.7,3z M8.339,18.338H5.667v-8.59h2.672V18.338z M7.004,8.574c-0.857,0-1.549-0.694-1.549-1.548 c0-0.855,0.691-1.548,1.549-1.548c0.854,0,1.547,0.694,1.547,1.548C8.551,7.881,7.858,8.574,7.004,8.574z M18.339,18.338h-2.669 v-4.177c0-0.996-0.017-2.278-1.387-2.278c-1.389,0-1.601,1.086-1.601,2.206v4.249h-2.667v-8.59h2.559v1.174h0.037 c0.356-0.675,1.227-1.387,2.526-1.387c2.703,0,3.203,1.779,3.203,4.092V18.338z\"><\/path><\/svg>\t\t<span class=\"wp-block-outermost-social-sharing-link-label screen-reader-text\">\n\t\t\tShare on LinkedIn\t\t<\/span>\n\t<\/a>\n<\/li>\n\n\n<li style=\"color: #1a4548\" class=\"outermost-social-sharing-link outermost-social-sharing-link-mail has-primary-color wp-block-outermost-social-sharing-link\">\n\t<a href=\"mailto:?subject=Nested%20ForEach%20loops%20in%20Azure%20Data%20Factory&#038;body=Nested%20ForEach%20loops%20in%20Azure%20Data%20Factory%20&mdash;%20https%3A%2F%2Fwww.syntera.ch%2Fblog%2F2022%2F12%2F15%2Fnested-foreach-loops-in-azure-data-factory%2F\" aria-label=\"Email this Page\"  class=\"wp-block-outermost-social-sharing-link-anchor\">\n\t\t<svg width=\"24\" height=\"24\" viewBox=\"0 0 24 24\" version=\"1.1\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" aria-hidden=\"true\" focusable=\"false\"><path d=\"M20,4H4C2.895,4,2,4.895,2,6v12c0,1.105,0.895,2,2,2h16c1.105,0,2-0.895,2-2V6C22,4.895,21.105,4,20,4z M20,8.236l-8,4.882 L4,8.236V6h16V8.236z\"><\/path><\/svg>\t\t<span class=\"wp-block-outermost-social-sharing-link-label screen-reader-text\">\n\t\t\tEmail this Page\t\t<\/span>\n\t<\/a>\n<\/li>\n\n\n<li style=\"color: #1a4548\" class=\"outermost-social-sharing-link outermost-social-sharing-link-whatsapp has-primary-color wp-block-outermost-social-sharing-link\">\n\t<a href=\"https:\/\/api.whatsapp.com\/send?text=Nested%20ForEach%20loops%20in%20Azure%20Data%20Factory%20&mdash;%20https%3A%2F%2Fwww.syntera.ch%2Fblog%2F2022%2F12%2F15%2Fnested-foreach-loops-in-azure-data-factory%2F\" aria-label=\"Share on WhatsApp\" rel=\"noopener nofollow\" target=\"_blank\" class=\"wp-block-outermost-social-sharing-link-anchor\">\n\t\t<svg width=\"24\" height=\"24\" viewBox=\"0 0 24 24\" version=\"1.1\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" aria-hidden=\"true\" focusable=\"false\"><path d=\"M 12.011719 2 C 6.5057187 2 2.0234844 6.478375 2.0214844 11.984375 C 2.0204844 13.744375 2.4814687 15.462563 3.3554688 16.976562 L 2 22 L 7.2324219 20.763672 C 8.6914219 21.559672 10.333859 21.977516 12.005859 21.978516 L 12.009766 21.978516 C 17.514766 21.978516 21.995047 17.499141 21.998047 11.994141 C 22.000047 9.3251406 20.962172 6.8157344 19.076172 4.9277344 C 17.190172 3.0407344 14.683719 2.001 12.011719 2 z M 12.009766 4 C 14.145766 4.001 16.153109 4.8337969 17.662109 6.3417969 C 19.171109 7.8517969 20.000047 9.8581875 19.998047 11.992188 C 19.996047 16.396187 16.413812 19.978516 12.007812 19.978516 C 10.674812 19.977516 9.3544062 19.642812 8.1914062 19.007812 L 7.5175781 18.640625 L 6.7734375 18.816406 L 4.8046875 19.28125 L 5.2851562 17.496094 L 5.5019531 16.695312 L 5.0878906 15.976562 C 4.3898906 14.768562 4.0204844 13.387375 4.0214844 11.984375 C 4.0234844 7.582375 7.6067656 4 12.009766 4 z M 8.4765625 7.375 C 8.3095625 7.375 8.0395469 7.4375 7.8105469 7.6875 C 7.5815469 7.9365 6.9355469 8.5395781 6.9355469 9.7675781 C 6.9355469 10.995578 7.8300781 12.182609 7.9550781 12.349609 C 8.0790781 12.515609 9.68175 15.115234 12.21875 16.115234 C 14.32675 16.946234 14.754891 16.782234 15.212891 16.740234 C 15.670891 16.699234 16.690438 16.137687 16.898438 15.554688 C 17.106437 14.971687 17.106922 14.470187 17.044922 14.367188 C 16.982922 14.263188 16.816406 14.201172 16.566406 14.076172 C 16.317406 13.951172 15.090328 13.348625 14.861328 13.265625 C 14.632328 13.182625 14.464828 13.140625 14.298828 13.390625 C 14.132828 13.640625 13.655766 14.201187 13.509766 14.367188 C 13.363766 14.534188 13.21875 14.556641 12.96875 14.431641 C 12.71875 14.305641 11.914938 14.041406 10.960938 13.191406 C 10.218937 12.530406 9.7182656 11.714844 9.5722656 11.464844 C 9.4272656 11.215844 9.5585938 11.079078 9.6835938 10.955078 C 9.7955938 10.843078 9.9316406 10.663578 10.056641 10.517578 C 10.180641 10.371578 10.223641 10.267562 10.306641 10.101562 C 10.389641 9.9355625 10.347156 9.7890625 10.285156 9.6640625 C 10.223156 9.5390625 9.737625 8.3065 9.515625 7.8125 C 9.328625 7.3975 9.131125 7.3878594 8.953125 7.3808594 C 8.808125 7.3748594 8.6425625 7.375 8.4765625 7.375 z\"><\/path><\/svg>\t\t<span class=\"wp-block-outermost-social-sharing-link-label screen-reader-text\">\n\t\t\tShare on WhatsApp\t\t<\/span>\n\t<\/a>\n<\/li>\n<\/ul>\n<\/div>\n<\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In a recent project, I tried to copy data from a SharePoint site to an Azure Blob Storage using Azure Data Factory (<a href=\"https:\/\/www.syntera.ch\/blog\/2022\/10\/10\/copy-files-from-sharepoint-to-blob-storage-using-azure-data-factory\/\" target=\"_blank\" rel=\"noreferrer noopener\">Copy files from SharePoint to Blob Storage using Azure Data Factory &#8211; Syntera<\/a>). The goal was to enable employees to upload their expense receipts to their SharePoint site using OneDrive&#8217;s built-in scan function. Using Azure Data Factory these receipts should then get copied into an Azure Blob Storage for further processing, utilizing different services of the Azure cloud. From a structural perspective, each employee needs his\/her own folder and only the corresponding person has permissions to upload files into the folder. Translated into technical terms the implications are: I have to loop over all subfolders (each of them corresponding to an employee) and then loop through each file inside the subfolders. Seems like a pretty simple task, since nested loops are a quite common feature in ETL tools right? &#8230; right? Unfortunately, the answer is no, Azure Data Factory does not allow nested loops. The workaround is to execute another pipeline from within the ForEach loop whereby the executed childpipeline is then allowed to hold another loop. In the following step-by-step guide, I extended my previous solution, enabling it to copy multiple files from multiple folders.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">The Solution<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let\u2019s first have a look at the final pipeline in Azure Data Factory and break it down into individual tasks to solve:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"772\" height=\"268\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/08_Titelpicture-2.png\" alt=\"\" class=\"wp-image-177\" style=\"width:600px;height:208px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/08_Titelpicture-2.png 772w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/08_Titelpicture-2-300x104.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/08_Titelpicture-2-768x267.png 768w\" sizes=\"auto, (max-width: 772px) 100vw, 772px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Prerequisite<\/strong>:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Register SharePoint (SPO) application in Azure Active Directory (AAD).<\/li>\n\n\n\n<li>Grant SPO site permission to a registered application in AAD.<\/li>\n\n\n\n<li>Provision ResourceGroup, Azure Data Factory (ADF) and Azure Data Lake Storage (ADLS) in your Azure Subscription.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">All these steps are explained in detail in the previous blog: <a href=\"https:\/\/www.syntera.ch\/blog\/2022\/10\/10\/copy-files-from-sharepoint-to-blob-storage-using-azure-data-factory\/\">Copy files from SharePoint to Blob Storage using Azure Data Factory &#8211; Syntera<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Inside of ADF main pipeline:<\/strong><\/p>\n\n\n\n<ol start=\"1\" class=\"wp-block-list\">\n<li>WebActivity \u201cGetBearerToken\u201d: Get an access token from SPO via API call.<\/li>\n\n\n\n<li>WebActivity \u201cGetSPOMainFolderMetadata\u201d: Get SPO mainfolder metadata including a list of all subfolders using the SPO access token via API call.<\/li>\n\n\n\n<li>Execute Pipeline inside ForEach loop: Iterate through subfolders in list of subfolders and for each execute the child pipeline<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Inside of ADF child pipeline:<\/strong><\/p>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li>WebActivity \u201cGetBearerToken\u201d: Get an access token from SPO via API call.<\/li>\n\n\n\n<li>WebActivity \u201cGetSPOSubFolderMetadata\u201d: Get SPO subfolder metadata including a list of all files in the SPO target folder using the SPO access token via API call.<\/li>\n\n\n\n<li>CopyActivity \u201cCopy data from SPO to ADLS\u201d inside ForEach loop: Iterate through files in list of files and copy each file to ADLS.<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">In my previous blog, I covered all the steps to loop through subfolders and copy all files in them to a Blob Storage. There is one small adjustment due to executing the pipeline from within another pipeline. This change will be addressed in this blog. If you are interested in a step-by-step guide of that part, please refer to my previous blog.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">1. Create Pipeline in ADF and get access token from SPO<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Open ADF Studio\n<ul class=\"wp-block-list\">\n<li>In Azure portal under&nbsp;<strong>Navigate<\/strong>&nbsp;select&nbsp;<strong>Resource groups<\/strong>&nbsp;and click on the resource group you created<\/li>\n\n\n\n<li>Inside your resource group, you should see the ADF and ADLS resources we created previously<\/li>\n\n\n\n<li>After selecting your ADF resource under&nbsp;<strong>Getting started<\/strong>&nbsp;you should see&nbsp;<strong>Open Azure Data Factory Studio<\/strong>, which will open a new tab<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Create a new pipeline\n<ul class=\"wp-block-list\">\n<li>Under&nbsp;<strong>Author<\/strong>&nbsp;right-click&nbsp;<strong>Pipelines<\/strong>&nbsp;and select&nbsp;<strong>New pipeline<\/strong><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Create a Web activity\n<ul class=\"wp-block-list\">\n<li>Under&nbsp;<strong>General<\/strong>&nbsp;select&nbsp;<strong>Web<\/strong>&nbsp;and drag it to your pipeline<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Under&nbsp;<strong>General<\/strong>&nbsp;choose a suitable name (in my case: GetBearerToken)<\/li>\n\n\n\n<li>In&nbsp;<strong>Settings<\/strong>&nbsp;enter the following information:\n<ul class=\"wp-block-list\">\n<li>URL:&nbsp;<code>https:\/\/<a href=\"https:\/\/accounts.accesscontrol.windows.net\/[Tenant-ID]\/tokens\/OAuth\/2\">accounts.accesscontrol.windows.net\/[Tenant-ID]\/tokens\/OAuth\/2<\/a><\/code>\n<ul class=\"wp-block-list\">\n<li>replace&nbsp;<code>[Tenant-ID]<\/code>&nbsp;with the Directory (tenant) ID from your registered app<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Method: POST<\/li>\n\n\n\n<li>Authentication: None<\/li>\n\n\n\n<li>Headers:\n<ul class=\"wp-block-list\">\n<li>Name: Content-Type<\/li>\n\n\n\n<li>Value:&nbsp;<code>application\/x-www-form-urlencoded<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Body:&nbsp;<code>grant_type=client_credentials&amp;client_id=[Client-ID]@[Tenant-ID]&amp;client_secret=[Client-Secret]&amp;resource=00000003-0000-0ff1-ce00-000000000000\/[Tenant-Name].sharepoint.com@[Tenant-ID]<\/code>\n<ul class=\"wp-block-list\">\n<li>replace&nbsp;<code>[Tenant-ID]<\/code>&nbsp;with the Directory (tenant) ID from your registered app<\/li>\n\n\n\n<li>replace&nbsp;<code>[Client-ID]<\/code>&nbsp;with the Application (client) ID from your registered app<\/li>\n\n\n\n<li>replace&nbsp;<code>[Client-Secret]<\/code>&nbsp;with the value of the generated secret from your registered app<\/li>\n\n\n\n<li>replace&nbsp;<code>[Tenant-Name]<\/code>&nbsp;with the SPO domain name (in my case: syntera)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"955\" height=\"895\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/01_BearerToken.png\" alt=\"\" class=\"wp-image-154\" style=\"width:600px;height:562px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/01_BearerToken.png 955w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/01_BearerToken-300x281.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/01_BearerToken-768x720.png 768w\" sizes=\"auto, (max-width: 955px) 100vw, 955px\" \/><\/figure>\n\n\n\n<ol start=\"6\" class=\"wp-block-list\">\n<li>Select&nbsp;<strong>Debug<\/strong>&nbsp;to check if everything is set up correctly (check the output of the activity to see the access token)<\/li>\n\n\n\n<li>On&nbsp;<strong>General<\/strong>&nbsp;check the box for Secure output to ensure that your access tokens don\u2019t get logged in your pipeline runs<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">2. Get SPO folder metadata (list of subfolders)<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Now that we can retrieve access tokens from SPO we are able to get information from the SPO tenant. In order to copy multiple files from multiple folders we first have to get a list of all subfolders currently located in the mainfolder. To receive this list, we will use an API function from SPO which returns the metadata of a specified folder:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Add another Web activity and connect it with the previous one<\/li>\n\n\n\n<li>Under&nbsp;<strong>General<\/strong>&nbsp;choose a suitable name (in my case: GetSPOMainfolderMetadata) and check the box for secure input (again to ensure the access token does not get logged in your pipeline runs)<\/li>\n\n\n\n<li>Under&nbsp;<strong>Settings<\/strong>&nbsp;put in the following information:\n<ul class=\"wp-block-list\">\n<li>URL:&nbsp;<code>https:\/\/[sharepoint-domain-name].sharepoint.com\/sites\/[sharepoint-site]\/_api\/web\/GetFolderByServerRelativeUrl('\/sites\/[sharepoint-site]\/[relative-path-to-folder]')\/F<\/code>olders\n<ul class=\"wp-block-list\">\n<li>replace&nbsp;<code>[sharepoint-domain-name]<\/code>&nbsp;with your SPO domain name (in my case: syntera)<\/li>\n\n\n\n<li>replace&nbsp;<code>[sharepoint-site]<\/code>&nbsp;with your SPO site name (in my case: blog)<\/li>\n\n\n\n<li>replace&nbsp;<code>[relative-path-to-file]<\/code>&nbsp;with the relative URL of your folder (you can check the folder path in SPO by right-clicking on the folder, selecting&nbsp;<strong>Details<\/strong>&nbsp;and selecting&nbsp;<strong>More details<\/strong>&nbsp;in the bottom of the window on the right)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Method: GET<\/li>\n\n\n\n<li>Authentication: None<\/li>\n\n\n\n<li>Headers (there are two headers):\n<ul class=\"wp-block-list\">\n<li>Name1: Authorization<\/li>\n\n\n\n<li>Value1:&nbsp;<code>@{concat('Bearer ', activity('GetBearerToken').output.access_token)}<\/code>\n<ul class=\"wp-block-list\">\n<li>Replace GetBearerToken with the name you gave to the first web activity<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Name2: Accept<\/li>\n\n\n\n<li>Value2:&nbsp;<code>application\/json<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"727\" height=\"662\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/02_SPOMainfodlerMetadata.png\" alt=\"\" class=\"wp-image-155\" style=\"width:600px;height:547px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/02_SPOMainfodlerMetadata.png 727w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/02_SPOMainfodlerMetadata-300x273.png 300w\" sizes=\"auto, (max-width: 727px) 100vw, 727px\" \/><\/figure>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li>Select&nbsp;<strong>Debug<\/strong>&nbsp;to check if everything is set up correctly (check the output of the activity to see the folder metadata)<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\">3. Iterate through list of SPO subfolders and for each trigger childpipeline<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">With the list of subfolders, we received as an output of the &#8220;GetSPOMainfolderMetadata&#8221; web activity, we can now setup the execution of the childpipeline. For this task we need a ForEach activity to loop through every subfolder in the list and trigger the childpipeline run. To hand over each subfolder URL to the childpipeline we need to introduce a pipeline parameter to provide the corresponding value and use it in the childpipeline run.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Add a ForEach activity and connect it with the previous web activity<\/li>\n\n\n\n<li>Under Settings check the box Sequential and in Items enter the following: <code>@activity('GetSPOMainfolderMetadata').output.value<\/code>\n<ul class=\"wp-block-list\">\n<li>Replace GetSPOFolderMetadata with the name you chose for the second web activity<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"755\" height=\"476\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ForEach.png\" alt=\"\" class=\"wp-image-157\" style=\"width:600px;height:378px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ForEach.png 755w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ForEach-300x189.png 300w\" sizes=\"auto, (max-width: 755px) 100vw, 755px\" \/><\/figure>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>Create a new pipeline, rename it (in my case: CopySPOFiles) and under <strong>Parameters<\/strong> create a new parameter named &#8220;FolderRelativeURL&#8221;. This parameter will be used to hand over the relative URL path of the subfolder to the childpipeline.<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"720\" height=\"198\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ChildpipelineParam.png\" alt=\"\" class=\"wp-image-159\" style=\"width:600px;height:165px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ChildpipelineParam.png 720w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ChildpipelineParam-300x83.png 300w\" sizes=\"auto, (max-width: 720px) 100vw, 720px\" \/><\/figure>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li>Inside the ForEach activity add an Execute pipeline activity and rename it (in my case: Execute CopySPOFiles)<\/li>\n\n\n\n<li>In <strong>Settings <\/strong>under put in the following information:\n<ul class=\"wp-block-list\">\n<li>In Invoked pipeline select your childpipeline<\/li>\n\n\n\n<li>Under Parameters note that the parameter &#8220;FolderRelativeURL&#8221; appears, which we created in the childpipeline and set the value to:\n<ul class=\"wp-block-list\">\n<li><code>@{item().ServerRelativeUrl}<\/code><\/li>\n\n\n\n<li>This references the ServerRelativeUrl field in the .json received from the GetSPOMainfolderMetadata<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"743\" height=\"504\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ExecutePipeline.png\" alt=\"\" class=\"wp-image-160\" style=\"width:600px;height:406px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ExecutePipeline.png 743w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/ExecutePipeline-300x203.png 300w\" sizes=\"auto, (max-width: 743px) 100vw, 743px\" \/><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Setup for childpipeline &#8220;CopySPOFiles&#8221;<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">We now have a working pipeline, which can extract all subfolders of a mainfolder and for each subfolder executes a childpipeline. In my case, the childpipeline is set up to loop through all files in the subfolder and copy them to a Blob Storage. If you want to know how to do that you can follow along my previous blog (Copy files from SharePoint to Blob Storage using Azure Data Factory &#8211; Syntera). The following pipeline is the result of the said blog and if you follow along you should end up with the same picture. The only difference is the parameter FolderRelativeURL, which we created in the last step.<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"815\" height=\"518\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/06_Childpipeline.png\" alt=\"\" class=\"wp-image-162\" style=\"width:600px;height:318px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/06_Childpipeline.png 815w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/06_Childpipeline-300x191.png 300w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/06_Childpipeline-768x488.png 768w\" sizes=\"auto, (max-width: 815px) 100vw, 815px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Enabling the childpipeline to use the dynamically extracted subfolders from the mainpipeline requires one last step. We need to pass the FolderRelativeURL parameter received from the mainpipeline to the &#8220;GetSPOFolderMetadata&#8221;. For this purpose the URL called within the &#8220;GetSPOFolderMetadata&#8221; activity needs to be changed to the following:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>URL: <code>https:\/\/[sharepoint-domain-name].sharepoint.com\/sites\/[sharepoint-site]\/_api\/web\/GetFolderByServerRelativeUrl('@{pipeline().parameters.FolderRelativeURL')\/Files<\/code>\n<ul class=\"wp-block-list\">\n<li>replace&nbsp;<code>[sharepoint-domain-name]<\/code>&nbsp;with your SPO domain name (in my case: syntera)<\/li>\n\n\n\n<li>replace&nbsp;<code>[sharepoint-site]<\/code>&nbsp;with your SPO site name (in my case: blog)<\/li>\n\n\n\n<li><code><code>@{pipeline().parameters.FolderRelativeURL<\/code><\/code>} references the pipeline parameter FolderRelativeURL<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<figure class=\"wp-block-image aligncenter size-full is-resized\"><img loading=\"lazy\" decoding=\"async\" width=\"767\" height=\"679\" src=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/07_ChangeGetSPOFolderMetadata-1.png\" alt=\"\" class=\"wp-image-166\" style=\"width:600px;height:531px\" srcset=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/07_ChangeGetSPOFolderMetadata-1.png 767w, https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/07_ChangeGetSPOFolderMetadata-1-300x266.png 300w\" sizes=\"auto, (max-width: 767px) 100vw, 767px\" \/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">Unfortunately, there is no way around requesting a new BearerToken in the childpipeline, since handing over the token from the mainpipeline would result in logging the token in the pipeline runs. There is currently no possibility to secure parameters exchanged between pipelines in the same way as it is possible to secure outputs between activities.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In a recent project, I tried to copy data from a SharePoint site to an Azure Blob Storage using Azure Data Factory (Copy files from SharePoint to Blob Storage using Azure Data Factory &#8211; Syntera). The goal was to enable employees to upload their expense receipts to their SharePoint site using OneDrive&#8217;s built-in scan function. [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":177,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[12,17],"class_list":["post-147","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-engineering","tag-azure-data-factory","tag-nested-loops"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Nested ForEach loops in Azure Data Factory - Syntera<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Nested ForEach loops in Azure Data Factory - Syntera\" \/>\n<meta property=\"og:description\" content=\"In a recent project, I tried to copy data from a SharePoint site to an Azure Blob Storage using Azure Data Factory (Copy files from SharePoint to Blob Storage using Azure Data Factory &#8211; Syntera). The goal was to enable employees to upload their expense receipts to their SharePoint site using OneDrive&#8217;s built-in scan function. [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/\" \/>\n<meta property=\"og:site_name\" content=\"Syntera\" \/>\n<meta property=\"article:published_time\" content=\"2022-12-15T09:10:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-07-18T13:38:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/08_Titelpicture-2.png\" \/>\n\t<meta property=\"og:image:width\" content=\"772\" \/>\n\t<meta property=\"og:image:height\" content=\"268\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Dimitri B\u00fctikofer\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Dimitri B\u00fctikofer\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/\",\"url\":\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/\",\"name\":\"Nested ForEach loops in Azure Data Factory - Syntera\",\"isPartOf\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/#website\"},\"datePublished\":\"2022-12-15T09:10:36+00:00\",\"dateModified\":\"2024-07-18T13:38:41+00:00\",\"author\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.syntera.ch\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Nested ForEach loops in Azure Data Factory\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#website\",\"url\":\"https:\/\/www.syntera.ch\/blog\/\",\"name\":\"Syntera\",\"description\":\"translating data into business value.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.syntera.ch\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034\",\"name\":\"Dimitri B\u00fctikofer\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g\",\"caption\":\"Dimitri B\u00fctikofer\"},\"sameAs\":[\"http:\/\/www.syntera.ch\"],\"url\":\"https:\/\/www.syntera.ch\/blog\/author\/dimitri\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Nested ForEach loops in Azure Data Factory - Syntera","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/","og_locale":"en_US","og_type":"article","og_title":"Nested ForEach loops in Azure Data Factory - Syntera","og_description":"In a recent project, I tried to copy data from a SharePoint site to an Azure Blob Storage using Azure Data Factory (Copy files from SharePoint to Blob Storage using Azure Data Factory &#8211; Syntera). The goal was to enable employees to upload their expense receipts to their SharePoint site using OneDrive&#8217;s built-in scan function. [&hellip;]","og_url":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/","og_site_name":"Syntera","article_published_time":"2022-12-15T09:10:36+00:00","article_modified_time":"2024-07-18T13:38:41+00:00","og_image":[{"width":772,"height":268,"url":"https:\/\/www.syntera.ch\/blog\/wp-content\/uploads\/2022\/12\/08_Titelpicture-2.png","type":"image\/png"}],"author":"Dimitri B\u00fctikofer","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Dimitri B\u00fctikofer","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/","url":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/","name":"Nested ForEach loops in Azure Data Factory - Syntera","isPartOf":{"@id":"https:\/\/www.syntera.ch\/blog\/#website"},"datePublished":"2022-12-15T09:10:36+00:00","dateModified":"2024-07-18T13:38:41+00:00","author":{"@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034"},"breadcrumb":{"@id":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.syntera.ch\/blog\/2022\/12\/15\/nested-foreach-loops-in-azure-data-factory\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.syntera.ch\/blog\/"},{"@type":"ListItem","position":2,"name":"Nested ForEach loops in Azure Data Factory"}]},{"@type":"WebSite","@id":"https:\/\/www.syntera.ch\/blog\/#website","url":"https:\/\/www.syntera.ch\/blog\/","name":"Syntera","description":"translating data into business value.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.syntera.ch\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/843f812ce16de687e4d4527aee313034","name":"Dimitri B\u00fctikofer","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.syntera.ch\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/9cbefca9184e6b8146d83f72900473b791b31bc6c2a10a8e57f77e9c41f11b45?s=96&d=mm&r=g","caption":"Dimitri B\u00fctikofer"},"sameAs":["http:\/\/www.syntera.ch"],"url":"https:\/\/www.syntera.ch\/blog\/author\/dimitri\/"}]}},"_links":{"self":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/147","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/comments?post=147"}],"version-history":[{"count":18,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/147\/revisions"}],"predecessor-version":[{"id":713,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/posts\/147\/revisions\/713"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/media\/177"}],"wp:attachment":[{"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/media?parent=147"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/categories?post=147"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.syntera.ch\/blog\/wp-json\/wp\/v2\/tags?post=147"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}