如何在SSIS中加载和存档多个文件

2020-09-29

加入收藏

情境

几周前，我的客户要求我实施一个SSIS包，该包允许他们从FTP服务器下载文件并进行存档。 代理商数据，发票数据和销售数据每天都会上传到FTP服务器。他们需要下载它们并导入他们的仓库数据库。

方法

为了实现此要求，我使用了两个主要的SSSI任务，即FTP任务和Foreach循环容器
FTP任务：用于从FTP服务器下载文件
Foreach循环容器： 用于加载每个下载的文件

开发环境

用于Visual Studio 2013 数据文件的 SQL Server 2014 Developer SQL Server数据工具

我为代理商，发票和销售数据创建了3个文件。每个文件都有3列（Col1，Col2和Col3）
代理机构
Col1 | Col3 | Col3A
| 1 | 3
发票
Col1 | Col3 | Col3A
| 1 | 3
销售
Col1 | Col3 | Col3A
| 1 | 3

FTP服务器

出于演示目的，我使用了支持FTP服务的DriveHQ服务。我创建了AAA_ArchivedData文件夹，然后上传了 代理商数据，发票数据和销售数据。
我建议您创建一个免费会员帐户，因为您可以使用它们来配置FTP任务以进行下一步

创建登台表

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Agency]') AND type in (N'U'))
BEGINCREATE TABLE [dbo].[Agency](
	[Col1] [varchar](50) NULL,
	[Col2] [varchar](50) NULL,
	[Col3] [varchar](50) NULL
) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sales]') AND type in (N'U'))
BEGINCREATE TABLE [dbo].[Sales](
	[Col1] [varchar](50) NULL,
	[Col2] [varchar](50) NULL,
	[Col3] [varchar](50) NULL
) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sales]') AND type in (N'U'))
BEGINCREATE TABLE [dbo].[Sales](
	[Col1] [varchar](50) NULL,
	[Col2] [varchar](50) NULL,
	[Col3] [varchar](50) NULL
) ON [PRIMARY]

实施SSIS包

创建一个名为Load_Multiple_CSV_Fi les.dtsx 的新程序包
创建变量
ArchivedFolder：F： SSIS-Pr actice Loa d_Multiple _CSV_Files ArchivedF在较旧的 位置存储归档文件文件连接文件类型OLEDBConnection： 与SQL数据库服务器的连接字符串
值：数据源= HSSSC1PCL01198 SQLSERVER2014；初始目录=演示;提供程序= SQLNCLI11.1;集成安全性= SSPI;数据源：数据库服务器名称
初始目录：数据库名称
提供程序：连接到SQL数据库服务器的驱动程序。您需要更改值以满足您的环境。我使用SQLNCLI11.1的原因是SQL Server Engine是2014。如果您的版本低于SQL 2012的版本，则应为10.0 / 10.1
Integrated Security：这意味着我在连接到SQL Database Server时使用窗口身份验证模式。SourceFolder：F： SSIS-Prac tice Load_ Multiple_C SV_Files D ataFile 在哪里存储从FTP服务器下载的文件在“连接管理器”面板上创建连接
创建OLEDBConnection并将OLEDBConnection变量分配给ConnectionString属性为CSV文件创建3个连接（代理商，发票和销售），并将FileConnection变量分配给ConnectionString属性创建ArchivedFolder连接，并将ArchivedFolder变量分配给ConnectionString属性
拖放FTP任务组件
双击打开FTP任务编辑器创建新的FTP连接以打开FTP连接管理器编辑器服务器设定服务器名称：ftp.drivehq.com服务器端口：默认为21。FTP站点侦听此端口。如果您使用其他端口，则需要确保该端口未被防火墙阻止。证书
用户名：您在DriveHQ注册的帐户密码：您在DriveHQ注册的密码选件选中“使用被动模式”：这意味着客户端将启动数据连接。如果取消选中，则表示您使用活动模式。在活动模式下，服务器端启动数据连接，在这种情况下为DriveHQ。单击测试连接以确保您可以成功连接到DriveHQ单击“确定”返回“ FTP任务编辑器”转到文件传输选项卡局部参数IsLocalPathVariable：正确LocalVariable：User :: SourceFolder。我们指定从DriveHQ下载数据文件的位置操作方式
操作：接收文件。有很多操作，例如发送文件，接收文件，创建本地目录等。在这种情况下，我们要下载文件，因此我们使用接收文件IsTransferAscii：True接受Ascii代码远程参数
IsRemotePathVariable：假远程路径：/ AAA_ArchivedData/* . txt。该选项指定了我们在DriveHQ上下载数据文件的位置。AAA_ArchivedData是在上述步骤中创建的，我还上传了测试文件。* .txt指定我们下载所有扩展名为txt的文件点击确定
拖放执行SQL任务组件
该任务是截断之前创建的表
拖放Foreach循环容器组件
双击以打开Foreach循环编辑器转到收藏夹标签枚举器：Foreach文件枚举器添加表达式目录：@ [User :: SourceFolder]枚举器配置
文件：*。txt表示仅加载文本文件检查完全合格转到变量映射
用户：： FileConnection索引= 0点击确定将脚本任务组件拖放到Foreach循环中，并将其命名为Parse File Name
此任务用于检测哪种文件类型（代理商，发票或销售）双击打开脚本任务编辑器ReadOnlyVariables：User :: FileConnectionReadWriteVariables：User :: FileType单击编辑脚本...，然后输入以下代码
public void Main（）{//待办事项：在此处添加代码string filepath = Dts.Variables [“ FileConnection”]。Value.ToString（）; 字符串文件名= Path.GetFileName（filepath）; if（filename.ToUpper（）。Contains（“ INVOICE”））{Dts.Variables [“ FileType”]。Value =“ Invoice”; } else if（（（filename.ToUpper（）。Contains（“ AGENCY” ）））{Dts.Variables [“ FileType”]。Value =“ Agency”; } else if（（（filename.ToUpper（）。Contains（“ SALES”））））{Dts.Variables [“ FileType”]。Value =“ Sales”; } else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; } Variables [“ FileType”]。Value =“ Agency”; } else if（（（filename.ToUpper（）。Contains（“ SALES”））））{Dts.Variables [“ FileType”]。Value =“ Sales”; } else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; } Variables [“ FileType”]。Value =“ Agency”; } else if（（（filename.ToUpper（）。Contains（“ SALES”））））{Dts.Variables [“ FileType”]。Value =“ Sales”; } else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; } } else if（（（filename.ToUpper（）。Contains（“ SALES”））））{Dts.Variables [“ FileType”]。Value =“ Sales”; } else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; } } else if（（（filename.ToUpper（）。Contains（“ SALES”））））{Dts.Variables [“ FileType”]。Value =“ Sales”; } else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; } 值=“销售”；} else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; } 值=“销售”；} else Dts.Variables [“ FileType”]。Value =“未知”; Dts.TaskResult =（int）ScriptResults.Success; }点击确定拖放“数据流任务”组件并将其命名为“ 加载发票”
在“ 解析文件名”脚本任务和“ 加载发票”之间建立连接打开优先约束编辑器在连接上双击
评估操作：表达式表达式： @ [User :: FileType] ==“发票e”单击逻辑或。点击确定双击加载发票数据流
拖放平面文件源组件并将其命名为发票文件
平面文件连接管理器：发票列：所有列拖放OLEDB目标，名称为“ 发票目标”
OLEDB连接管理器：OLEDBConnection数据访问视图：表和视图-快速加载表或视图的名称：发票使用平面文件源进行映射拖放数据流任务组件并将其命名为Load Agency
重复我们为发票实施的所有步骤，但将优先约束编辑器的表达式更改为 @ [User :: FileType] ==“ Agency ” SSIS_Multiple_CSV_LoadAgency.PNG拖放数据流任务组件并将其命名为Load Sales
重复所有步骤，我们实现了发票，但优先约束编辑器的变化表达对 @ [用户::文件类型] ==“销售”拖放文件系统任务组件
双击打开文件系统任务编辑器IsDestinationPathVariable：假DestinationConnection：ArchivedFolder。这是我们之前创建的文件夹连接OverwriteDestination：False操作：移动文件。有很多操作，但是在成功处理数据文件之后，我们使用“移动文件”来存档数据文件IsSourcePathVariable：正确SourceConnection：用户:: FileConnection

执行SSIS包

最后，我们的程序包已经完成，我们需要执行它以查看其工作原理？

检查文件夹 F： SSIS-Practice Load_Mult iple_CSV_F iles DataF ile，以确保没有数据文件
检查文件夹 F： SSIS-Practice Load_Mult iple_CSV_F iles Archi vedFolder 以确保没有数据文件
从代理商，发票和销售表中查询数据以确保它们为空

执行程序包执行程序包后

验证结果

优秀，导入的数据和数据文件也按我们的预期保存到了文件夹中。在我的示例中，存档文件夹是本地的，您仍然可以使用FTP Task组件将文件上传到任何地方。

结论

在现实生活中，有许多方案需要您将SSIS组件组合在一起。希望您通过本示例对如何使用FTP任务和Foreach循环容器同时处理多个文件有一个概述。