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.
(1).Design Control Flow
As before, I firstly open “SQL Server Business Intelligence Development Studio” and create a new Integration Services Project.
Drag the first “Execute SQL Task” object from Control Flow in left tool to designer surface
Drag the “Foreach Loop Container” object from Control Flow in left tool to designer surface
Drag the second “Execute SQL Task” object from Control Flow into the inner “Foreach Loop Container” object
Drag a green arrow in the first “Execute SQL Task” to “Foreach Loop Container” object
So does that I have already finished the initial “Control Flow Design”
(2).Create Connection ManagerOLE DB Connection
Right-click Connection Manager surface next to select “New OLE DB Connection…” in menu
Click “New” button
Select the right value as Provider, Server Name, Authentication, Database Name and click “OK” button if “Test Connection” is normal.
Finally, it will create a new Connection Managers as “TEST-SQL-03.AdventureWorks”
File ConnectionRight-click Connection Manager surface next to select “New File Connection…” in menu
Select the right value as Usage type, Folder and click “OK” button
Finally, it will create a new Connection Managers as “SQLFILE”
(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”
Select the right value as Connection Type, Connection, SQLSourceType and click SQLStatement button
Write down T-SQL language for creating Testing Table if it no exit and inserting some value to this Table
沒有留言:
張貼留言