網頁

2012年4月24日 星期二

Simple Execute SQL Task in SSIS (part 1 of 2)

I want to do a SQL Task in SSI to realize “Execute SQL Task” mechanism. The scenario is
  (i). The first SQL Task is for creating table and import data if the table doesn’t exist
  (ii). The second SQL Task import data by executing sql language in Foreach Loop Container
For do it, I will use the AdventureWorks as example database.
ScreenHunter_01 Apr. 20 12.02(1).Design Control Flow
As before, I firstly open “SQL Server Business Intelligence Development Studio” and create a new Integration Services Project.
ScreenHunter_02 Apr. 20 13.42Drag the first “Execute SQL Task” object from Control Flow in left tool to designer surface
ScreenHunter_03 Apr. 20 13.57Drag the “Foreach Loop Container” object from Control Flow in left tool to designer surface
ScreenHunter_04 Apr. 20 14.00Drag the second “Execute SQL Task” object from Control Flow into the inner “Foreach Loop Container” object
ScreenHunter_05 Apr. 20 14.05Drag a green arrow in the first “Execute SQL Task” to “Foreach Loop Container” object
ScreenHunter_06 Apr. 20 14.09So does that I have already finished the initial “Control Flow Design”
ScreenHunter_07 Apr. 20 14.12(2).Create Connection ManagerOLE DB Connection
Right-click Connection Manager surface next to select “New OLE DB Connection…” in menu
ScreenHunter_08 Apr. 20 16.08Click “New” button
ScreenHunter_09 Apr. 20 16.12Select the right value as Provider, Server Name, Authentication, Database Name and click “OK” button if “Test Connection” is normal.
ScreenHunter_10 Apr. 20 16.13Finally, it will create a new Connection Managers as “TEST-SQL-03.AdventureWorks”
ScreenHunter_12 Apr. 20 16.14File ConnectionRight-click Connection Manager surface next to select “New File Connection…” in menu
ScreenHunter_13 Apr. 20 16.36Select the right value as Usage type, Folder and click “OK” button
ScreenHunter_16 Apr. 20 16.38Finally, it will create a new Connection Managers as “SQLFILE”
ScreenHunter_17 Apr. 20 16.38(3).Configure the first “Execute SQL Task” objectRight-Click the first “Execute SQL Task” to select “Edit…” in menu or Double-Click “Execute SQL Task”
ScreenHunter_18 Apr. 20 16.57Select the right value as Connection Type, Connection, SQLSourceType and click SQLStatement button
ScreenHunter_19 Apr. 20 19.14Write down T-SQL language for creating Testing Table if it no exit and inserting some value to this Table
ScreenHunter_20 Apr. 20 19.56

沒有留言:

張貼留言

Since 2010 Design by Davidwa
©Copyright Davidwa Inc. All rights reserved.