ADF复制活动中使用字符串变量动态选择列的解决方法

核心需求 #

需要实现SQL Server表到数据湖的动态复制，支持任意行列规模的表自动同步，核心转换规则：

• 所有geometry类型列自动拆分为两个新列
• 其中一列存储geometry对应的WKT字符串
• 另一列存储该geometry对应的SRID空间参考信息

示例测试表结构 #

以如下Stations表为例：

CREATE TABLE [dbo].[Stations](
    [Id] [uniqueidentifier] NOT NULL,
    [StationNumber] [nvarchar](256) NOT NULL,
    [StationName] [nvarchar](256) NULL,
    [Location] [geometry] NOT NULL,
    [LocationTypeTypeListItemId] [uniqueidentifier] NOT NULL,
    [mLastModified] [datetimeoffset](7) NOT NULL,
    [LocationRef] [geometry] NULL)

已实现的逻辑 #

动态列拼接T-SQL #

编写了如下T-SQL脚本，通过查询系统视图动态生成查询列的拼接字符串：

SELECT STRING_AGG(select_string, ', ') AS sstring
FROM (
        SELECT Object_Schema_name(c.object_id) as [SCHEMA_NAME]
    , object_NAME(c.object_id) AS TABLE_NAME
    , c.NAME AS COLUMN_NAME
    , t.NAME AS DATA_TYPE
    , CONCAT(c.NAME, ' AS ', c.NAME) AS select_string
        FROM sys.all_columns c
            INNER JOIN sys.types t ON t.user_type_id = c.user_type_id
        where Object_Schema_name(c.object_id) <> 'sys'
            and object_NAME(c.object_id) = 'Stations'
    UNION
        SELECT Object_Schema_name(c.object_id) as [SCHEMA_NAME]
    , object_NAME(c.object_id) AS TABLE_NAME
    , t.NAME AS DATA_TYPE
    , CONCAT(c.NAME, '.STSrid AS ', c.NAME, 'Srid') AS select_string
        FROM sys.all_columns c
            INNER JOIN sys.types t ON t.user_type_id = c.user_type_id
        where Object_Schema_name(c.object_id) <> 'sys'
            and t.name = 'geometry'
            and object_NAME(c.object_id) = 'Stations'
 ) AS temp

该脚本单独执行时可以输出符合预期的列拼接结果，格式示例如下：

Id AS Id, StationNumber AS StationNumber, StationName AS StationName, 
 Location.STAsText() AS Location, Location.STSrid AS 
 LocationSRID, [...]

复制活动配置与报错 #

在数据工厂的复制数据活动中，通过Lookup活动执行上述T-SQL获取拼接好的列字符串，活动中使用的查询语句写法如下：

'SELECT ' + [@{split(activity('Lookup1').output.value[0].sstring, ',')}] +
' FROM ' + [@{item().table_schema}].[@{item().table_name}]

执行复制活动时返回如下报错：

The identifier that starts with '["Active AS Active",
" DistanceToOutletKm AS DistanceToOutletKm"," Id AS Id",
" Location AS Location"," Location.STSrid AS Locatio' is
too long. Maximum length is 128.

待解决问题 #

如何正确配置复制数据活动，使其能正确识别动态生成的列列表字符串，实现动态列选择与geometry列自动转换的需求？

内容的提问来源于stack exchange，提问作者Bjarne Thorsted