Automating 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. Well, 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 on a package in Solution Explorer and selecting View Code. After you check that out, do the following:

1. In BIDS Solution Explorer, right-click on one of your working packages and select FileSave <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. Next, open up SSMS and connect to Integration Services; open it in object explorer and right-click on Stored Packages. At this point you can select Import Package… as shown in Figure 16-25.

   
   Figure 16-25

2. The Import Package dialog will appear and you should change Package location from SQL Server to File System. Then click the button associated with Package path to specify the dtsx file you want. If you just click on the Package name text entry box, it will fill in the package name for you. At this point, the dialog should look something like Figure 16-26.

Figure 16-26

With the package in the Stored Packages area, you can easily run it by right-clicking the package name and selecting Run Package. What we really want to be able to do, however, is schedule to have our package run on a regular basis so we don't have to think about it. Scheduling SSIS packages to be run periodically can be accomplished by using the SQL Server Agent. The Agent is its own process which you can access by opening a connection to the Database Engine as shown in Figure 16-27.

Figure 16-27

1. To create a scheduled job, right-click on the Jobs folder and select New Job. Give it an appropriate name and description as shown in Figure 16-28.

Figure 16-28

On the Select a page pane at the top click on Steps and then the New… button that appears at the bottom of the dialog. On the New Job Step page, name your step PROCESS_AS and under Type (type of operation) select SQL Server Integration Services Package as shown in Figure 16-29.

For Package source you need to select File system (since we will use the dtsx previously saved to the File system).

Click on the ellipsis button for Package and select the package you saved before. At this time, your New Job Step dialog should look something like the one in Figure 16-29. Click OK to continue; when you are asked if the On Success action is intended, click Yes.

Figure 16-29

1. On the Select a page pane at the top right, click on Schedules and then the New… button that appears at the bottom of the dialog. The dialog that comes up now is the New Job Schedule dialog; it is here that you can schedule your package to be run on a recurring basis. For illustrative purposes, we'll select Schedule type: One time. Select today's date with a time of five minutes from now. After you have entered the details your Job Scheduler dialog should resemble Figure 16-30.

   
   Figure 16-30

2. To see the job kicked off as scheduled, you only need to double-click on the Job Activity Monitor icon which is under the SQL Server Agent Folder in SSMS's Object Explorer. Once your job starts, you will see the Status change to Executing as shown in Figure 16-31.

   
   Figure 16-31

We're sure you can see the power of this approach. The ability to create packages chock full of administrative functions to manage your instance of Analysis Services — all scheduled to run on a recurring basis and send out emails based on success or failure. This is great stuff, especially for users who want to automate as much as possible and utilize their remaining time to learn and implement new things! We are confident you have gained sufficient knowledge of SSIS by now that you can create Analysis Services SSIS packages and schedule them. Make a habit of using SSIS when and where applicable for your business.