Azure Logic，用于从SQL到FTP获取数据 [英] Azure Logic for getting data from SQL to FTP

问题描述

我的任务是从SQL中获取数据，并将数据作为CSV文件上传到FTP服务器.

I've a task of taking data from SQL and uploading the data as a CSV file up to an FTP server.

现在，我已经为单个SQL行做到了这一点. 我遇到的问题是遍历所有行(foreach循环)并将这些行作为CSV文件的内容插入.我在foreach循环中尝试了FTP创建文件任务，但是一次只能访问一行以设置为文件的内容-我需要所有行！

Now I've done this for a single SQL row just fine. The problem I'm having is looping over all rows (foreach loop) and inserting these rows as the content of the CSV file. I've tried a FTP Create File Task inside a foreach loop, but I can only access a single row at a time to set as the file's content - I need all the rows!

还要记住的是，这些文件将有200k +行.

Also to keep in mind is that these files will have 200k+ rows.

我当然可以为此编写一个C#控制台应用程序，但是由于我不编写任何代码就容易实现了这一目标，因此看来这将是值得的.

I could of course just write a C# console app for this but the ease at which I got this far without writing any code makes it seem like it will be a worthwhile endeavor.

推荐答案

我们最近为此场景添加了表"原语，但仍在进行设计器支持，但您可以在代码视图中使用它.

We recently added "Table" primitive for this scenario, support in designer is still work in progress, but you can use it in code view.

在以下情况下，我从SQL Azure中的表中获取行，使用SQL查询(名字，姓氏)中的数据生成带有两列的CSV，然后通过电子邮件发送.

In below scenario, I'm getting rows from a table in SQL Azure, producing an CSV with two columns using data from the SQL query (First Name, Last Name), then send it via e-mail.

"Get_rows": {
    "inputs": {
        "host": {
            "api": {
                "runtimeUrl": "https://logic-apis-southcentralus.azure-apim.net/apim/sql"
            },
            "connection": {
                "name": "@parameters('$connections')['sql']['connectionId']"
            }
        },
        "method": "get",
        "path": "/datasets/default/tables/@{encodeURIComponent(encodeURIComponent('[SalesLT].[Customer]'))}/items",
        "queries": {
            "$top": 10
        }
    },
    "runAfter": {},
    "type": "ApiConnection"
},
"tableCsv0": {
    "inputs": {
        "columns": [
            {
                "header": "First Name",
                "value": "@item()?['FirstName']"
            },
            {
                "header": "Last Name",
                "value": "@item()?['LastName']"
            }
        ],
        "format": "csv",
        "from": "@body('Get_rows')?['value']"
    },
    "runAfter": {
        "Get_rows": [
            "Succeeded"
        ]
    },
    "type": "Table"
},
"Send_an_email": {
    "inputs": {
        "body": {
            "Body": "@body('tableCsv0')",
            "Subject": "Subject",
            "To": "[e-mail]"
        },
        "host": {
            "api": {
                "runtimeUrl": "https://logic-apis-southcentralus.azure-apim.net/apim/office365"
            },
            "connection": {
                "name": "@parameters('$connections')['office365']['connectionId']"
            }
        },
        "method": "post",
        "path": "/Mail"
    },
    "runAfter": {
        "tableCsv0": [
            "Succeeded"
        ]
    },
    "type": "ApiConnection"
}

这篇关于Azure Logic，用于从SQL到FTP获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！