如何通过ADF将FTP服务器上的.DAT文件加载至MS SQL Server表？

方案1：直接用ADF通用文件格式解析DAT文件 #

• 跳过ADLS中转，直接在ADF中创建FTP源数据集，选择通用文件格式：
  • 若你的DAT是固定宽度格式，在数据集的「架构」选项卡手动定义每个字段的起始位置和长度，匹配文件结构即可。
  • 若文件用特殊字符做分隔（比如竖线|、分号;，甚至控制字符\x1F），可以给CSV格式数据集指定这个自定义分隔符，就能正常解析。

方案2：二进制存ADLS后用SQL外部表读取 #

• 先把DAT文件以二进制格式上传到ADLS，然后在MS SQL Server中创建外部数据源指向ADLS，再定义外部表匹配文件结构：

  CREATE EXTERNAL DATA SOURCE ADLSDataSource
  WITH (
      LOCATION = 'abfss://<容器名>@<存储账户>.dfs.core.windows.net/',
      CREDENTIAL = <你的存储凭据>,
      TYPE = BLOB_STORAGE
  );
  
  CREATE EXTERNAL TABLE dbo.DAT_Table (
      Column1 VARCHAR(50),
      Column2 INT,
      -- 按实际字段定义结构
  )
  WITH (
      LOCATION = '/存储路径/你的文件.dat',
      DATA_SOURCE = ADLSDataSource,
      FILE_FORMAT = (
          FORMAT_TYPE = FIXED_WIDTH,
          FORMAT_OPTIONS (
              FIELD_WIDTHS = '50,4,...' -- 对应每个字段的宽度
          )
      )
  );
  

  • 之后可以直接查询外部表，或用INSERT INTO把数据导入到本地SQL表。

方案3：ADF数据流解析后直连SQL #

• 在ADF中创建数据流，FTP源读取DAT文件后用转换工具解析：
  • 固定宽度文件用substring函数提取字段，比如substring(Column_1, 1, 10)提取前10个字符作为第一个字段。
  • 带特殊标记的文件用split函数结合自定义分隔符拆分字段。
  • 解析完成后直接将数据流输出到MS SQL Server表。

方案4：PowerShell脚本中转处理 #

• 写脚本从FTP下载DAT文件，本地解析后直接插入SQL Server：

  # 从FTP下载文件
  $ftpUrl = "ftp://你的FTP服务器/文件路径/file.dat"
  $localPath = "C:\temp\file.dat"
  $webClient = New-Object System.Net.WebClient
  $webClient.Credentials = New-Object System.Net.NetworkCredential("用户名", "密码")
  $webClient.DownloadFile($ftpUrl, $localPath)
  
  # 解析固定宽度文件示例
  $data = Get-Content $localPath | ForEach-Object {
      [PSCustomObject]@{
          Column1 = $_.Substring(0, 10).Trim()
          Column2 = [int]$_.Substring(10, 5)
          -- 按实际字段定义
      }
  }
  
  # 批量插入SQL Server
  $connectionString = "Server=你的SQL服务器;Database=目标库;Integrated Security=True;"
  $bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($connectionString)
  $bulkCopy.DestinationTableName = "dbo.目标表"
  $dataTable = [System.Data.DataTable]::new()
  $dataTable.Columns.Add("Column1", [string])
  $dataTable.Columns.Add("Column2", [int])
  foreach ($row in $data) {
      $dataRow = $dataTable.NewRow()
      $dataRow["Column1"] = $row.Column1
      $dataRow["Column2"] = $row.Column2
      $dataTable.Rows.Add($dataRow)
  }
  $bulkCopy.WriteToServer($dataTable)
  

  • 可将脚本部署到Azure自动化账户，定时执行同步任务。

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