如何在SSIS中加載和存檔多個文件

情境

幾周前，我的客戶要求我實施一個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