SSIS_FOR迴圈容器執行多次預儲程序
主題： |
SSIS_FOR迴圈容器執行多次預儲程序 |
文章簡介： |
介紹如何在SSIS內利用FOR迴圈容器執行多次預儲程序，將資料傳輸至其他DB Server資料表/不同資料檔的需求。 |
作者： |
陳淑敏 |
版本/產出日期： |
V1.0/2016.12.12 |
1. 前言
• 此篇介紹如何將預儲程序在資料流程工作(Data Flow Task)中當作資料來源，並透過For迴圈容器執行多次預儲程序並將資料寫入另一個DataBase的資料表，此部分也可運用在有需要跨不同DB Server傳輸資料及產出不同的資料檔(Excel，文字檔等)。
2. 開始前準備
實作此SSIS是建立於以下版本的環境：
• SQL Server 2008 R2
• SQL Server Business Intelligence Development Studio
3. 實作演練
• 因要實作跨不同DataBase做資料傳遞，故先建立資料表[tempdb].[dbo].[Customer] / [TestDB].[dbo].[Customer]並新增資料，執行以下語法即可：
USE [tempdb]
GO
/****** Object: Table [dbo].[Customer] Script Date: 12/11/2016 12:34:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [varchar](20) NULL,
[CustomerName] [nvarchar](20) NULL,
[ContactName] [nvarchar](20) NULL,
[ContactTel] [nvarchar](20) NULL,
[Contact_CompanyName] [nvarchar](200) NULL,
[Contact_CompanyAddr] [nvarchar](500) NULL,
[ContactType] [nvarchar](50) NULL,
[Contact_CompanyTel] [nvarchar](20) NULL
) ON [PRIMARY]
GO
USE [TestDB]
GO
/****** Object: Table [dbo].[Customer] Script Date: 12/11/2016 12:49:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [varchar](20) NULL,
[CustomerName] [nvarchar](20) NULL,
[ContactName] [nvarchar](20) NULL,
[ContactTel] [nvarchar](20) NULL,
[Contact_CompanyName] [nvarchar](200) NULL,
[Contact_CompanyAddr] [nvarchar](500) NULL,
[ContactType] [nvarchar](50) NULL,
[Contact_CompanyTel] [nvarchar](20) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'魏小平', N'13x1xx7xxxx', N'福氣房地產總公司', N'', N'家庭聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'連小景', N'13x5xx2xxxx', N'', N'', N'家庭聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'吕小賀', N'13x5xx7xxxx', N'文明資訊公司', N'', N'緊急聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'周小進', N'13x6xx3xxxx', N'明智生技協會', N'', N'緊急聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'游小康', N'13x5xx9xxxx', N'同本人', N'', N'工作聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (960001, N'連小壽', N'林小雨', N'13x3xx1xxxx', N'同本人', N'', N'工作聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'楊小群', N'13x5xx0xxxx', N'', N'', N'家庭聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'陳小恩', N'13x1xx0xxxx', N'', N'', N'家庭聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'谢小静', N'15x6xx2xxxx', N'', N'', N'緊急聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'陳小芳', N'13x0xx0xxxx', N'', N'', N'緊急聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'歐小侃', N'13x7xx0xxxx', N'全世界贸易有限公司', N'無', N'工作聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (111873, N'陳小媛', N'趙小剛', N'15x0xx0xxxx', N'環球贸易有限公司', N'無', N'工作聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'黄小瀟', N'18x5xx3xxxx', N'好康生活用品館', N'無', N'家庭聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'程小龍', N'18x5xx6xxxx', N'真好用金属材料有限公司', N'無', N'家庭聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'盧小城', N'18x5xx5xxxx', N'好便宜金属材料有限公司', N'無', N'緊急聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'董小偉', N'15x5xx6xxxx', N'好好用金属材料有限公司', N'無', N'緊急聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'王小源', N'15x0xx3xxxx', N'天祥小學', N'無', N'工作聯絡人', N'')
INSERT [dbo].[Customer] ([CustomerID], [CustomerName], [ContactName], [ContactTel], [Contact_CompanyName], [Contact_CompanyAddr], [ContactType], [Contact_CompanyTel]) VALUES (116886, N'林小健', N'周小希', N'18x5xx6xxxx', N'天祥小學', N'無', N'工作聯絡人', N'')
• 再建立預儲程序[GetCustomer]，當資料來源執行撈取，執行以下語法即可：
/****** Object: StoredProcedure [dbo].[GetCustomer] Script Date: 12/11/2016 12:34:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetCustomer]
@CustomerID varchar(20)
AS
BEGIN
SET NOCOUNT ON;
select * from TestDB.dbo.Customer
where customerID like case when @CustomerID = '' then '%' else @CustomerID end
END
GO
• 開始實作SSIS：
1 先建立OLE DB連線：TestDB / tempdb
2 拖拉for迴圈容器，這次實作要執行3次迴圈，故在[for迴圈]上按右鍵選編輯進入，填寫(圖一)紅框內的資訊，其中i需要設定為全域變數(範圍=Package)，故填寫(圖二)紅框內的資訊
(圖一)
(圖二)
3 拖拉執行SQL工作進for迴圈容器，取名為[撈取customerID]，這次實作針對同一客戶代碼：960001，故在[撈取customerID]上按右鍵選編輯進入，填寫(圖一)紅框的資訊，其中ResultSet=單一資料列，故點選[結果集]填寫(圖二)紅框的資訊，而其中User::CustomerID，需要設定為全域變數(範圍=Package)，故填寫(圖三)紅框內的資訊
(圖一)
(圖二)
(圖三)
4 拖拉資料流程工作進for迴圈容器，取名為[撈取TestDB的customer資料表匯入tempdb的customer資料表]，在[撈取TestDB的customer資料表匯入tempdb的customer資料表]上按右鍵選編輯進入後，再拖拉OLE DB來源：取名為[TestDB]及OLE DB目的：取名為[tempdb]，將[TestDB]拉綠線到[tempdb]，如(圖一)所示，記得資料連線是OLE DB，同樣的也要拖拉OLE DB的流程項目
(圖一)
4.1 在[TestDB]上按右鍵選編輯進入，填寫(圖二)的紅框資訊，因SQL命令中的參數?號，為承接[撈取CustomerID]回傳的@CustomerID變數值，故點選[參數]填寫(圖三)紅框的資訊
(圖二)
(圖三)
4.2 在[tempdb]上按右鍵選編輯進入，填寫(圖四)的紅框資訊，點選對應即可檢視[TestDB]與[tempdb]資料表欄位的對應
(圖四)
(圖五)
5 完成以上步驟後即可執行SSIS，執行成功狀況如(圖一)所示，之後再來查看[tempdb].[dbo].[Customer]確實已經有寫入6 * 3 = 18筆資料如(圖二)所示
(圖一)
(圖二)
4. 參考來源
• https://msdn.microsoft.com/zh-tw/library/ms139956(v=sql.105).aspx
• https://msdn.microsoft.com/zh-tw/library/ms141696(v=sql.105).aspx
• https://msdn.microsoft.com/zh-tw/library/ms141689(v=sql.105).aspx
