Identifying the SSIS Tools

The tools have changed from SQL Server 2000 as well. In SQL Server 7/2000, you designed and managed packages using Enterprise Manager. SQL Server 2005 now has SQL Server Management Studio (SSMS), which is used to manage not just SQL Server but also Integration Services, Reporting Services, and Notification Services. To design and maintain packages, you use another tool, BIDS. You deploy packages using DTExec.exe, DTExecUI.exe, or DTUtil.exe. Finally, if you want to edit DTS packages, you will still use the old DTS Designer, either by having a SQL Server 2000 instance on the same machine or by installing the feature pack.

You’ll explore the tools in Exercise 6.1 by building and deploying a simple package from start to finish. Although it is a trivial example, its purpose is to let you concentrate as much as possible on concepts and not get too bogged down in the finest level of detail.

Exercise 6.1: Creating an SSIS Package

The goal in this exercise is to create an SSIS package that will export the Person.Contact table from the AdventureWorks database to a Microsoft Excel file called Contacts.xls. During the export, you need to add a derived column that will consist of FirstName plus a space plus LastName .

To create an SSIS package, follow these steps:

1. Open BIDS by clicking Start Programs Microsoft SQL Server 2005 SQL Server Business Intelligence Development Studio. This application is really the Visual Studio 2005 integrated development environment (IDE) that has some special project options added. You can see that if you create a new project and select Business Intelligence Projects, you have a few options to choose from-Integration Services Project is the one you want, of course.

2. Click the Browse button to select a folder (we’re using a folder called SSIS Practice) and title this project ExportTableToExcel-which is exactly what you’ll be doing in a moment. Notice that Solution Name is set to the same value, as shown here. Solutions are groups of one or more projects that you can open all at once. For more complex solutions, you might want to change the solution name, but for now the default of having it the same as the project name works fine.

   

3. Click OK, and after a few seconds while BIDS works, you should see the screen shown here.

   

4. Double-click Data Flow Task (listed under Control Flow Items on the left) to add it to the control flow. Control flow is where you handle high-level workflow design. For this package, it will be your only control flow task.

5. Double-click Data Flow Task on the Control Flow tab, or just click the Data Flow tab. Data Flow is also for handling workflow, only at the data level. You can think of the Data Flow task as a container that can hold one or many data tasks that are organized by workflow. One potentially confusing point is that although there can be many different Data Flow tasks within a package, there is only one Data Flow tab. Make sure it is displaying the details of the right Data Flow task before making changes.

6. Right-click the Connection Managers tab at the bottom of the window, and then select New OLE DB Connection. Connection managers are a way to describe how and what to connect to (they’re similar to DSNs, if you have used them in the past).

7. Click New. Enter your server name, set your logon method, and then select the Adventure-Works database. Testing the connection using the button at the lower right is always a good idea; then click OK, and then click OK once more to get back to the main screen.

8. On the Connection Managers tab, you should now see one connection defined. Double-click (or drag if you prefer) an OLE DB source from the Data Flow sources to add it to the Data Flow design window, as shown here.

   

9. The small circle (it’s red on a monitor) with the X in it to the right side is a tip that something is missing or wrong. If you click/hover, you’ll get a tooltip that will help you figure out the problem. In this case, it tells you that you haven’t assigned a connection manager to the source.

10. Fortunately, this is easy to fix! Double-click the OLE DB source. You need to set the connection manager to the one you just created. This points the source to a specific server and database. Change the data access mode to SQL command, and then for the SQL command text enter this query:

     select title, firstname, lastname, emailaddress, phone from person.contact

11. Under Data Flow Transformations in the Toolbox, double-click Derived Column Transform to add it to the Data Flow. This is where you will transform the data before you write it out to Excel.

12. Right now the two tasks aren’t connected, so you need to do that-the derived column will be using the rows provided by the OLE DB source. Connecting them is often called wiring up because it feels a bit like running wires between tasks to direct the flow. Grab the green arrow from the OLE DB Source box, and drag it to the Derived Column box. The view should now look something like the one shown here.

    

13. The physical positioning of the objects doesn’t affect their execution, but it is generally recommended that the flow should be from top to bottom and from left to right if possible, just to make it consistent and easy to read.

14. You’ll notice that most controls have two outputs, one green and one red. The green is the good output that contains data being sent on to the next task. The red output is for handling error conditions.

15. Double-click the Derived Column task. For Derived Column Name, type FullName . Just to the right of that is Derived Column, and you want to set that to Add As New Column. (If you click the drop-down list, you’ll see that you can also replace a column with your new value.) The expression is going to be FirstName + " " + LastName, so you can just enter that, as shown here. You could also drag variables, columns, and operators from the two upper windows if you preferred. Make sure that the data type is set to Unicode String. You can ignore the remaining columns in this case. Click OK.

    

16. The expressions are case sensitive! The expression will show up in red in the editor if it is failing to validate, and you will also get an error when you click OK if something is wrong.

17. You’ve got a little bit of work invested now, so save your package just in case the power fails. Choose File Save, press Ctrl+S, or click the Save button on the toolbar.

18. To write the results to disk, you’ll need a new connection manager and a Data Flow destination. You’ll do the connection manager first. Right-click the Connection Managers tab, and this time select New Connection, then select Excel, and finally click Add. For the filename, you’ll keep it simple; enter C:\contacts.xls . You’ll go with the default Excel version and leave the box checked to include column names in the first row. Click OK, and now you have your second connection manager.

19. Double-click or drag an Excel destination from Data Flow destinations onto the Data Flow task. Drag the green arrow from Derived Column to Excel Destination to finish wiring up your package.

20. Double-click Excel Destination. The connection manager and data access mode should be preset, but you need to create a sheet name. Click the New button, and you’ll get a Create Table statement that will be used for the sheet. (We’re treating the Excel file as a database, and sheets are equivalent to tables.) Click OK to accept.

21. Click Mappings in the list box at the left to have SSIS autogenerate the mappings from the input (the Derived Column task) to the output (the Excel sheet you just defined). Click OK. The package should now look something like this.

    

22. Save the package again. Now see what happens when you run it. Choose Debug Start Debugging, click the green arrow on the toolbar, or press F5-all these will execute the package. You should see the tasks start to change colors as operations progress; we’re hoping that they will all be green, indicating success. The workflow lines show the number of records processed, and you should see 19,971 rows piped from OLE DB Source to Derived Column and then from Derived Column to Excel Destination.

23. Now you just need to check the file contents to make sure you have the right number of rows and you have the new FullName column. Open the Excel file to verify.

24. If you have done any development, you might find the next part a little strange. Even though the package has executed successfully, the debugger is still running. Any changes you make at this point will not take effect until you stop and reexecute the package. You can stop the debugger by clicking the link at the bottom or by choosing Debug Stop Debugging. Once the debugger has stopped, all the steps will change from green back to white.

25. Now you’ll go back and document what you did just a bit. If you click each of the steps in the Data Flow task, you can rename them to make the diagram easier to follow. The revised Data Flow is shown here.

    

26. You can do the same to the step you have on the Control Flow tab. You’re also going to add an annotation by right-clicking the tab, selecting Add Annotation, and then typing in some notes. One note about the note: they don’t wrap automatically. If you need to enter a lot of text you have to enter line breaks manually so that it doesn’t scroll off the screen. The revised Control Flow is shown here.

    

27. Save the package again.

28. The next step is to deploy the package (and then you can take a break!). You have two options for deploying; the first is just copying the files to a folder on the server, and the second is copying the files into the MSDB database on a SQL Server instance. We’ll discuss the pros and cons of each in a bit, but for now we’ll just walk you through a SQL Server deployment.

29. Choose Project Properties to open the Project Configuration dialog box. Click Deployment Utility in the pane on the left, change the CreateDeploymentUtility value from False to True, and then click OK. The property is a bit misleading. It means that when you build the project, you’ll also get a manifest file that contains a bunch of information about the package. You can then use that manifest to easily deploy your package. To build (which means compile in some cases but is the process of making sure all is well), right-click the project in the Solution Explorer, and click Build.

30. Now you can see what you have on disk. Using Explorer, find your project folder, and then drill down until you get to a folder called Deployment. Here is the path:

     C:\SSIS Practice\ExportTableToExcel\ExportTableToExcel\bin\Deployment

31. The file you’re looking for has the long-winded extension of .SSISDeploymentManifest. If you want to open it with Notepad, you’ll see that it’s just an XML file that for the practice case doesn’t have a lot in it. From Explorer, right-click, and select Deploy. It might take a few seconds for the deployment utility (DTExecUI.exe) to load.

32. Skip over the first intro page, and you should arrive at the page shown here.

    

33. Change the deployment method to SQL Server Deployment, and click Next. Enter your server name (the default is usually localhost, and that usually works fine for practice), select an authentication method, and then click Next.

34. On the next page you’re given a default path to copy any other files needed to support your package (dependencies). You don’t have any in this case, but in more complex packages you might have DLLs or other files. If you do have dependencies, they should go in the default folder. You can change it if needed, but you also have to make changes in a config file so that the SSIS service can find the files. Stick with the default for now, click Next, and then click Next again to start the install.

35. The last step in deployment should show success. Click Finish, and you’re done! Take a break from studying, and when you come back, you’ll start looking at SSMS and how to manage your deployed packages.