如何在ADF中用数据流活动与派生列转换实现固定长度DAT文件映射至SQL Server

一、数据源与数据集配置 #

1. 配置链接服务 #

• FTP链接服务：在ADF门户新建链接服务，选择FTP类型，填入FTP服务器地址、认证方式（用户名/密码），完成后测试连接确保可用。

  【截图1：FTP链接服务配置页，已填入服务器地址、用户名，测试连接状态显示“成功”】

• SQL Server链接服务：新建链接服务选择SQL Server类型，填入服务器名称、目标数据库名，选择合适的认证方式（如SQL认证），测试连接通过。

  【截图2：SQL Server链接服务配置页，已完成必填项，测试连接验证通过】

2. 创建数据集 #

• FTP源数据集：选择已配置的FTP链接服务，文件格式选“文本格式”，重要：不设置任何分隔符；指定DAT文件存放的FTP目录；勾选“仅列出新增文件”选项，用于后续每日增量处理。

  【截图3：FTP数据集配置页，文件格式为文本，未设置分隔符，已启用“仅列出新增文件”】

• SQL Server目标数据集：关联SQL Server链接服务，直接选择目标表名称。

  【截图4：SQL Server数据集配置页，已选定目标表】

二、数据流核心转换（派生列截取子串） #

1. 新建数据流并添加源节点：创建数据流，添加FTP源节点，关联之前的FTP数据集。预览数据时会看到整行数据是单个字符串列（默认列名Column_1）。

   【截图5：数据流源节点配置页，已关联FTP数据集，预览显示无分隔的整行字符串】

2. 添加派生列转换：点击源节点后的“+”，选择“派生列”转换。根据目标表的列结构，使用ADF内置的substring()函数按固定长度截取子串，示例如下：
   • 目标列ID：substring(Column_1, 1, 5)（假设ID占前5个字符）
   • 目标列UserName：substring(Column_1, 6, 12)（从第6位开始，截取12个字符作为用户名）
   • 所有列均按固定长度规则配置，无需依赖分隔符。

   【截图6：派生列配置页，已添加多个派生列，每个列通过substring函数实现固定位置截取】

3. 添加目标节点并完成映射：添加SQL Server目标节点，关联目标数据集，在“映射”标签页中确认派生列与目标表列的对应关系（可自动匹配或手动调整）。

   【截图7：数据流目标节点映射页，派生列与目标表列已一一对应】

三、增量处理与调度 #

1. 构建管道：在ADF中新建管道，添加数据流活动，关联上述配置好的数据流。
2. 配置每日调度触发器：新建触发器选择“调度触发器”，设置每日执行时间（如凌晨2点），确保每日自动运行。

   【截图8：调度触发器配置页，已设置每日执行一次，执行时间为02:00】

3. 增量逻辑验证：由于FTP数据集已启用“仅列出新增文件”，ADF会自动记录已处理的文件，每日运行时仅处理FTP目录中新增的DAT文件，避免重复加载。

四、测试与验证 #

• 手动触发管道，查看数据流预览数据是否符合目标表结构，检查SQL Server目标表是否成功加载转换后的数据。
• 上传新的DAT文件到FTP目录，再次触发管道，确认只有新增文件的数据被写入目标表，验证增量处理逻辑生效。

内容的提问来源于stack exchange，提问作者Dhananjay Singh