Forum Discussion
Need Help with Dynamic Folder Strcuture for Files Loaded to a LakeHouse
Hi kewaynes333
Thanks for using Fabric Community.
Here’s a general approach you can take:
-
Lookup Activity: Use a Lookup activity to retrieve the list of tables that you want to copy. This could be a list of tables in your source database or a list of files in your ALDS container.
-
ForEach Activity: Use a ForEach activity to iterate over the list of tables/files retrieved by the Lookup activity. Inside the ForEach activity, you can include a Copy Data activity.
-
Copy Data Activity: In the Copy Data activity, you can specify the source (your ALDS container) and the destination (your LakeHouse). You can use dynamic content to specify the table/file name in the source and the folder structure in the destination based on the
dateSoldcolumn.
Here’s an example of how you can specify the folder structure dynamically:
@concat(formatDateTime(convertTimeZone(item().dateSold, 'UTC', 'yyyy/MM'), 'UTC'), '/', item().tableName)
This will create a folder structure like Year/Month/TableName in your LakeHouse for each table/file.
You can refer to this link:
Fabric Lakehouse Loading using Data Pipelines & Notebooks – Inspired by MS End-to-End Tutorials (serverlesssql.com)
Hope this helps. Please let me know if you have any further questions.
Hi,
Thanks for the response and guidance. I run into an error saying that the convertTimeZone function doesn't accept an integer. I believe this is because my dateSold column is in Unix timestamp format (milliseconds, I believe).
I've tried to account for this:
@concat(formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item().dateSold, 1000)), 'yyyy/MM'), '/', item().tableName)
But now I get this error:
The expression 'concat(formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item().dateSold, 1000)), 'yyyy/MM'), '/', item().tableName)' cannot be evaluated because property 'tableName' doesn't exist, available properties are 'dateSold, propertyType, lotAreaValue, address, imgSrc, price, bedrooms, longitude, latitude, listingStatus, zpid, listingSubType, contingentListingType, daysOnZillow, bathrooms, livingArea, country, currency, lotAreaUnit, hasImage'.
Any thoughts?
Hi kewaynes333
Error Property ‘tableName’ doesn’t exist:
The error message indicates that the property tableName does not exist in the current context. The available properties are ‘dateSold, propertyType, lotAreaValue, address, imgSrc, price, bedrooms, longitude, latitude, listingStatus, zpid, listingSubType, contingentListingType, daysOnZillow, bathrooms, livingArea, country, currency, lotAreaUnit, hasImage’. If you intended to use one of these existing properties, you should replace tableName with the correct property name.
@concat(formatDateTime(addSeconds('1970-01-01T00:00:00Z', div(item().dateSold, 1000)), 'yyyy/MM'), '/', item().<correct_property_name>)
Replace <correct_property_name> with the actual property you want to use. If you still encounter issues this might require a deeper investigation from our engineering team about your workspace, and the logic behind it to properly understand what might be happening.
Please go ahead and raise a support ticket to reach our support team: https://support.fabric.microsoft.com/support
Please provide the ticket number here as we can keep an eye on it.
Thank you.Hi kewaynes333
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
ThanksHi @kewaynes333
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. Otherwise, will respond back with the more details and we will try to help.
Thanks