Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

19.3. Automating Execution of SSIS Packages

Integration Services works on a slightly different model compared to Analysis Services. With Analysis Services, you deploy projects to the server; not so with Integration Services. There are three ways to make a package available to Integration Services; first through SQL Server, second through the file system, and finally through the SSIS Package Store (which is a variant of the file system solution). Here we'll look at the file system approach. Specifically, from BIDS, you save off a package in the form of an XML file (a .dtsx file). You might be wondering why the extension dtsx. As mentioned earlier, SSIS was originally called DTS in SQL Server 2000 and hence the file extension starts with dts. You can see the source for a .dtsx by right-clicking a package in Solution Explorer and selecting View Code. After you check that out, do the following:

1. In BIDS Solution Explorer, click one of your working packages and select File Save <filename> As and give the file a descriptive name. In this way you save off an XML version of the package to the file system.

2. Next, open up SSMS and connect to Integration Services; open it in Object Explorer, and right-click Stored Packages. At this point you can select Import Package as shown in Figure 19-25.

   Figure 19.25. Figure 19-25

3. The Import Package dialog will appear and you should change Package Location ...