Knight's Microsoft® Business Intelligence 24-Hour Trainer: Leveraging Microsoft SQL Server® Integration, Analysis, and Reporting Services with Excel® and SharePoint®

40



LESSON 5 Using the Control Flow

Hints

You will use a Foreach Loop Container and an Execute SQL task.



The Execute SQL task requires a database connection.



Step-by-Step

1. In BIDS select File  New  Project.

2. Select Integration Services Project.

3. Name the project BI Trainer Project 5.

4. Place a check next to “Create directory for solution.” Click OK.

5. Ensure that the Control Flow tab is active.

6. Right-click the Connection Manager of the Control Flow tab and select New OLE DB

Connection.

7. In the Connection Manager screen, type localhost in the drop-down list labeled “Server

name” and type or select AdventureWorks2008 in the drop-down list labeled “Select or enter

database name.” Click OK twice.

8. Right-click the connection in the Connection Manager and select Rename. Change the name

of the connection to AdventureWorks2008.

9. Ensuring that you click the white space in the Control Flow designer space, right-click and

select Variables.

10. Click the item in the Variable menu labeled New Variable.

11. Type results for the variable name and change the data type to Object.

12. Select New Variable from the Variable menu again.

13. Type businessEntityID for the variable name and

accept Int32 as the data type.

14. When you are done, your variables should look

like what is shown in Figure 5-6.

15. Click the toolbox tab, then drag an Execute SQL

task onto ...