使用ADF，如何将文件名加载到SQL Server表中? [英] Using ADF, how to get file names loaded into a SQL Server table?

问题描述

我正在尝试同时使用GetMetadata活动和CopyData活动.我的设置如下所示.

I am trying to use the GetMetadata activity and a CopyData activity together. My setup looks like this.

我试图获取文件名(使用GetMetadata)并将其加载到SQL Server表中的字段中(与CopyData一起使用). CopyData可以正常工作，但是我看不到让GetMetadata获取文件名并将这些文件名传递到表中的任何方法.在我的示例中，源数据中有4个字段与目标表中的4个字段匹配.大概第5个字段将是文件名.显然，它并不是真的像这样工作.我通读了下面的文档，但仍然无法弄清.

I am trying to get files names (using GetMetadata) and load these into a field in a SQL Server table (in conjunction with the CopyData). The CopyData works perfectly fine, but I don't see any way to have the GetMetadata get file names and pass those into a field in a table. In my example, I have 4 fields in the source data which match 4 fields in the destination table. The 5th field, presumably, will be the file name. Apparently, it doesn't really work like this. I read through the documentation below and I still can't figure it out.

https://docs .microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

推荐答案

更新2020年7月

最近已将新功能添加到复制"活动中，以允许您添加列，$$FILEPATH当前是唯一受支持的变量.请参阅此处以获取更多详细信息:

A new feature has been added to the Copy activity recently to allow you add columns, $$FILEPATH is currently the only supported variable. See here for more detail:

https://docs.microsoft.com/zh-CN/azure/data-factory/copy-activity-overview#add-additional-columns-during-copy

原始答案

在数据集中添加额外的列可能被视为Transform，而Azure数据工厂v2(ADF v2)复制任务并不容易将其自身应用于Transform.它可以做很多事情，例如从一种格式(例如csv)转换为其他格式(例如JSON)，但是它是有限的.也许在将来的某个时候，它们会向映射添加一些内容，从而允许添加字符串文字或类似于SSIS派生列功能的内容，但是这些类型的功能似乎正在被添加到映射数据流"中.

Adding an extra column to a dataset might be considered Transform and the Azure Data Factory v2 (ADF v2) Copy Task does not lend itself easily to Transform. It can do a couple of things like convert from one format (eg csv) to other formats (eg JSON) but it is limited. Maybe at some point in the future they add something to the mapping which allows adding string literals or something similar to the SSIS Derived Column feature, but these type of features are getting added to Mapping Data Flows at the moment it seems.

但是，实现此目标的一种方法是将存储过程目标与文件名参数和主数据集的表类型参数一起使用.看起来有点像这样:

One way to achieve this however, is to use a stored procedure target with a parameter for the filename and a table-type parameter for the main dataset. It looks a bit like this:

缺点是您现在必须在数据库(CREATE TYPE)中创建一个支持的表类型，并创建一个存储的proc来处理它，如下所示:

The downside is you now have to create a supporting table-type in your database (CREATE TYPE) and a stored proc to handle it, something like this:

CREATE TYPE dbo.typ_multiFile AS TABLE (
    col1    CHAR(1) NOT NULL,
    col2    CHAR(1) NOT NULL,
    col3    CHAR(1) NOT NULL
)
GO


CREATE OR ALTER PROC dbo.usp_ins_myTable (
    @fileName       AS VARCHAR (100),
    @typ            AS dbo.typ_multiFile READONLY
    )
AS
SET NOCOUNT ON

INSERT INTO dbo.myTable ( [fileName], col1, col2, col3 )
SELECT @fileName, col1, col2, col3
FROM @typ 

RETURN
GO

请注意，如下图所示，复制任务位于ForEach任务内部:

Note the Copy Task is inside a ForEach task, as per this diagram:

这篇关于使用ADF，如何将文件名加载到SQL Server表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！