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 FlowAs 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 ConnectionRight-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

沒有留言:
張貼留言