Once created a SSIS package, you're probably ready to run it and see what it does. But first, let's look at the options for saving SSIS packages. When you work in BIDS, your SSIS package is saved as an XML file (with the extension dtsx) directly in the normal Windows file system. But that's not the only option. Packages can also be saved in the msdb database in SQL Server itself, or in a special area of the file system called the Package Store.
Storing SSIS packages in the Package Store or the msdb database makes it easier to access and manage them from SQL Server's administrative and command-line tools without needing to have any knowledge of the physical layout of the server's hard drive.
Saving Packages to Alternate Locations
To save a package to the msdb database or the Package Store, you use the File > Save Package As menu item within BIDS.
To store copies of the package you've developed, follow these steps.
- Select File > Save Copy of Package.dtsx As from the BIDS menus.
- Select SSIS Package Store as the Package Location.
- Select the name of your test server.
- Enter the package path.
- Click OK.
- Select File > Save Copy of Package.dtsx As from the BIDS menus.
- Select SQL Server as the Package Location.
- Select the name of your test server and fill in your authentication information.
- Enter ExportDepartments as the package path.
- Click OK.
Running a Package
You can run the final package from either BIDS or SQL Server Management Studio. When you're developing a package, it's convenient to run it directly from BIDS. When the package has been deployed to a production server (and saved to the msdb database or the Package Store) you'll probably want to run it from SQL Server Management Studio.
| SQL Server also includes a command-line utility, dtsexec, that lets you run packages from batch files. |
Running a Package from BIDS
With the package open in BIDS, you can run it using the standard Visual Studio tools for running a project. Choose any of these options:
- Right-click the package in Solution Explorer and select Execute Package.
- Click the Start Debugging toolbar button.
- Press F5.
To run the package that you have loaded in BIDS, follow these steps:
- Click the Start Debugging toolbar button. SSIS will execute the package, highlighting the steps in the package as they are completed. You can select any tab to watch what's going on. For example, if you select the Control Flow tab, you'll see tasks highlighted, as shown in Figure.
2. When the package finishes executing, click the hyperlink underneath the Connection Managers pane to stop the debugger.
3. Click the Execution Results tab to see detailed information on the package, as shown in Figure.
| All of the events you see in the Execution Results pane are things that you can create event handlers to react to within the package. As you can see, DTS issues a quite a number of events, from progress events to warnings about extra columns of data that we retrieved but never used. |
Running a Package from SQL Server Management Studio
To run a package from SQL Server Management Studio, you need to connect Object Browser to SSIS.
Try It!
- In SQL Server Management Studio, click the Connect button at the top of the Object Explorer window.
- Select Integration Services.
- Choose the server with Integration Services installed and click Connect. This will add an Integration Services node at the bottom of Object Explorer.
- Expand the Stored Packages node. You'll see that you can drill down into the File System node to find packages in the Package Store, or the MSDB node to find packages stored in the msdb database.
- Expand the File System node.
- Right-click on the package and select Run Package. This will open the Execute Package utility, shown in Figure .
- Click Execute.
- Click Close twice to dismiss the progress dialog box and the Execute Package Utility.
- Browse for the inserted data
- Click the Execute toolbar button to verify that the package was run. You should see one entry for when the package was run from BIDS and one from when you ran it from SQL Server Management Studio.
