Logic app - Load metadata in to Azure SQL Table
I have a file in sharepoint folder and i am trying to get the metadata of the file and load the metadata in to Azure Sql table. Below is my flow-
Output i am getting at compose action is -
{ "ItemId": 137, "Id": "%252fShared%2bDocuments%252fPOC%252fPOC1%252fPOC2%252fTemp1.xlsx", "Name": "Temp1.xlsx", "DisplayName": "Temp1.xlsx", "Path": "/Shared Documents/POC/POC1/POC2/Temp1.xlsx", "LastModified": "2024-03-03T12:17:41Z", "Size": 15941, "MediaType": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "IsFolder": false, "ETag": ""{0F37F5BE-5437-4FDF-AD84-7B0B1BA19E07},2"", "FileLocator": "dataset=aHR0cHM6Ly9taWNyb3NvZnRhcGMuc2hhcmVwb2ludC5jb20vdGVhbXMvU2hhcmVwb2ludFRlc3Qy,id=JTI1MmZTaGFyZWQlMmJEb2N1bWVudHMlMjUyZlBPQyUyNTJmUE9DMSUyNTJmUE9DMiUyNTJmVGVtcDEueGxzeA==" }
Query i am using in "Execute a SQL Query" action
INSERT INTO [ado].[tblmetadata] (FileName,SourcePath,FolderName,LastModified,DestinationPath) Values ('@{body('Compose')['ItemId']}',@{body('Compose')['Name']}',@{body('Compose')['Path']}',@{body('Compose')['DisplayName']},@{body('Compose')['LastModified']}',@{body('Compose')['IsFolder']}')
But i am getting an error.
what should be the issue in here and if any alternate way to perform this task.
I am able to insert the file metadata into SQL table using the following way-
workflow-
- In Execute a SQL query (V2) action, I am using below query to fetch the data from Compose action and insert it into the table.
INSERT INTO [dbo].[tblmetadata] (Id,FileName,SourcePath,FolderName,LastModified,DestinationPath) Values ('@{outputs('Compose')?['ItemId']}','@{outputs('Compose')?['Name']}','@{outputs('Compose')?['Path']}','@{outputs('Compose')?['DisplayName']}','@{outputs('Compose')?['LastModified']}','@{outputs('Compose')?['IsFolder']}')
Code-
{
"definition": {
"$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
"actions": {
"For_each": {
"actions": {
"Condition": {
"actions": {
"Compose": {
"inputs": "@body('Get_file_metadata')",
"runAfter": {
"Get_file_metadata": [
"SUCCEEDED"
]
},
"type": "Compose"
},
"Execute_a_SQL_query_(V2)": {
"inputs": {
"body": {
"query": "INSERT INTO [dbo].[tblmetadata] (Id,FileName,SourcePath,FolderName,LastModified,DestinationPath) Values ('@{outputs('Compose')?['ItemId']}','@{outputs('Compose')?['Name']}','@{outputs('Compose')?['Path']}','@{outputs('Compose')?['DisplayName']}','@{outputs('Compose')?['LastModified']}','@{outputs('Compose')?['IsFolder']}')"
},
"host": {
"connection": {
"referenceName": "sqldw"
}
},
"method": "post",
"path": "/v2/datasets/@{encodeURIComponent(encodeURIComponent('default'))},@{encodeURIComponent(encodeURIComponent('default'))}/query/sql"
},
"runAfter": {
"Compose": [
"SUCCEEDED"
]
},
"type": "ApiConnection"
},
"Get_file_metadata": {
"inputs": {
"host": {
"connection": {
"referenceName": "sharepointonline"
}
},
"method": "get",
"path": "/datasets/@{encodeURIComponent(encodeURIComponent('**********'))}/files/@{encodeURIComponent(items('For_each')?['{Identifier}'])}"
},
"type": "ApiConnection"
}
},
"else": {
"actions": {}
},
"expression": {
"and": [
{
"equals": [
"@items('For_each')?['{IsFolder}']",
false
]
}
]
},
"type": "If"
}
},
"foreach": "@body('Get_files_(properties_only)')?['value']",
"runAfter": {
"Get_files_(properties_only)": [
"SUCCEEDED"
]
},
"type": "Foreach"
},
"Get_files_(properties_only)": {
"inputs": {
"host": {
"connection": {
"referenceName": "sharepointonline"
}
},
"method": "get",
"path": "/datasets/@{encodeURIComponent(encodeURIComponent('*********'))}/tables/@{encodeURIComponent(encodeURIComponent('e2b8***0ea6'))}/getfileitems",
"queries": {
"viewScopeOption": "RecursiveAll"
}
},
"runAfter": {},
"type": "ApiConnection"
}
},
"contentVersion": "1.0.0.0",
"outputs": {},
"triggers": {
"When_a_HTTP_request_is_received": {
"kind": "Http",
"type": "Request"
}
}
},
"kind": "Stateful"
}
Output-
Modify your Insert query, you will get the output too.