| Of all the methods for transferring databases, SQL Server Integration Services (SSIS) is the most flexible and robust. Not only can it transfer data, it can transform it. While this transformation capability is quite useful, it is not within the scope of this book. We will consider only the various options available in SSIS for moving data from your source system to your target system; we will not discuss any of the sophisticated data transformations that are possible with SSIS. While the previously described methods required you to spend time in SQL Server Management Studio, in order to use SQL Server Integration Services, you will need to work in the Visual Studio 2005 IDE (Integrated Development Environment). SQL Server ships with a version of this IDE called the "SQL Server Business Intelligence Development Studio," also known as BIDS. If you already have Visual Studio 2005 installed on your development PC, then you will have two methods to get to the same features. When you open either Visual Studio or BIDS, assuming you have installed the client and development tools from SQL Server on your PC, you will see a variety of project options. If you have installed any of the Visual Studio development tools, you will see them in either environment. The same is true for the SQL Server development tools. Either IDE reflects what you have installed. Setting Up an SSIS Project 1. | From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Business Intelligence Development Studio.
| 2. | From the File menu, select New, then Project. This will open the New Project dialog box, shown below. This dialog box will contain all of the project types available to create. For this exercise, select the Business Intelligence Projects node in the Project Types list and the Integration Services Project template from the Templates list. You can then change the name and path of the project and solution. When you have these settings the way you want them, click the OK button.
Tip  | A solution can contain many projects. |
 If this is your first time in either Visual Studio or BIDS, take some time to familiarize yourself with the interface. As you can see below, this is quite different from DTS (Data Transformation Services). The design interface has completely changed and has improved immensely. You will also see four tabs that are your design surfaces. In this example, you will use the Control Flow and Data Flow design surfaces only.
 Tip | We are only touching the surface of SSIS's capabilities. I encourage you to fully explore this tool's capabilities. |
| 3. | Add a Data Flow Task to the Control Flow design surface. Drag-and-drop a Data Flow Task (shown here) from the Control Flow Items toolbox to the Control Flow design surface.
 | 4. | Once you add this task, you can either double-click the task or select the Data Flow tab to open the Data Flow design surface.
| 5. | We are going to concentrate on getting data from the source to the target. In order to set this up, you will need to create two connection managers. Right-click in the Connection Managers pane and select the New OLE DB Connection option from the context menu. Set the Provider to Native OLE DB\SQL Native Client. Click the New button to create a new connection. Fill in the Server Name and select the database name. Click the OK button to create your first connection manager. You will need to do the same for the target server.
 | 6. | Once you have created the connection managers, drag-and-drop an OLE DB Source item (shown below) from the Data Flow toolbox to the Data Flow design surface.
 | 7. | A red circle with an "X" in it on the OLE DB Source icon lets you know that you need to set up the source object. Double-click the icon to start the OLE DB Source Editor, shown below.
 | 8. | Pick the connection manager for your source and pick the table to transfer. From here you can preview the data as well as modify the column list if necessary. The red circle disappears once you have properly set up the data flow source.
| 9. | Next, you will need to drag-and-drop an OLE DB Destination item (shown below) from the Data Flow Destinations section of the toolbox onto the Data Flow design surface.
 Once again, a red circle appears on the icon until you have set up the source. However, with the Destination object, you will also need to set the input columns. Do this by selecting the OLE DB Source icon, then dragging the green arrow from the Source object to the Destination object, connecting the two.
| 10. | Once you have made the connection, you can set up the source properties by right-clicking the OLE DB Destination icon and selecting Edit from the context menu. In the OLE DB Destination Editor, shown below, pick the connection manager, choose the Data Access Mode, select the destination table or view, and map the columns. There are a number of other properties that may be beneficial once you have tested your solution, including Table Lock and Rows Per Batch. You should look into these properties to determine whether or not they are appropriate for your situation.
Tip | If you choose one of the Fast Load options for the Data Access Mode, you are utilizing the bulk load option in the data pump. When using this mode, you have additional options available at the bottom of the dialog box. These options are designed to improve the performance of the load process. See SQL Server Books Online for more information about these options. |
 Tip | You will need to set the column mappings before you can click the OK button. However, if the columns are named the same, you can click the Mappings item in the list on the left and the default mappings will be filled in for you. |
| 11. | Repeat this process for each table or view you want to load. Once you have all of the tables ready to go, your package, that is, the list of data to be transferred, is ready to be deployed and scheduled. We will cover deployment and scheduling in the next section.
Tip | If you want to test this project, you can debug the project as you would in any Visual Studio project by using the Debug menu option or the Debug button in the IDE. |
|
You can use the RowCount Data Flow Transformation, shown below, as the destination, with a Data Viewer to view the data that is being moved.  SSIS gives you much more flexibility during the design and debug process than Data Transformation Services ever did. I encourage you to experiment with this tool as you might find many more uses for it. The advantage of using SSIS is that you can filter the tables that you want to move around. Not only that, you can choose multiple destinations for your transfer, and you can even loop through multiple destinations as long as the schemas and loads are identical. If you need a flexible way to move your data around, this may be the best choice for you. For more information about SSIS, see the SQL Server Books Online topic "Integration Services Overview." |
