Logic app - Load metadata in to Azure SQL Table

Question

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.

Answer 1

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.