O'Reilly logo

Professional Microsoft® SQL Server® Analysis Services 2008 with MDX by Denny Guang-Yeu Lee, Robert Zare, Sethu Meenakshisundaram, Matt Carroll, Sivakumar Harinath

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required