Tuesday, June 26, 2007

SSIS Packages and setting up Jobs

I promise not to turn todmeansfox into an SSIS blog! But I thought that as I continued development on my SQL Server back to FoxPro project, I would share any acquired wisdom along the way.

Last post, I discussed some of the problems I faced with the drag-and-drop nature of SSIS. In a nutshell, I had to do a lot (and I mean A LOT) of extra non-drag-and-drop work to get the packages running. As of now, I have thoroughly tested the entire solution locally on my machine and have now uploaded the packages to the server. A couple of notes:

Upload Packages

The first thing I had to do was upload my packages to the server. Log into Integration Services, click 'Stored Packages' from the treeview and add the package to the appropriate folder (in my case, MSDB). Do this by right-clicking the folder and click 'Import Package'. I had several packages to upload (a single general package that calls multiple specific packages). The Import Package dialog is easy to use, but takes a minute to get used to. There are some non-standard interface elements (the single '.' next to the disabled text box 'Protection level' is actually very important to push).

Once uploaded, I went ahead and created a job using SQL Server Agent.

SQL Server Agent

Connect to the server through SSMS where you want to execute your package. Click SQL Server Agent, and then right-click Jobs and click 'New Job...'. The New Job dialog will open.

There are quite a few important elements on this screen, all of which I'm sure I would do a poor job explaining. So instead, here are the basic things I did to get my job going:

  • On the General page, entered a name and description

  • On the Steps page, added a series of new steps designed to execute my main package in several different ways (passing in a variety of parameters for testing purposes):

    • Enter a Step name, and select the SQL Server Integration Services type

    • Select the Package source (SQL Server) and the Server name. Pick an uploaded package from the Package list

    • At this point, you'll want to look into how you want to log and fail the job as needed

    • To get things up and running, I clicked on the 'Set values' tab to enter all relevant parameters (see Some Snags below)

  • On the Notifications page, I set myself up to receive an email when the job finishes

Some Snags

While setting up the job, I ran into a few snags that I thought I would pass along.

First on the list: Be sure to define and assign variables properly:
Property Path: \Package.Variables[Somevariable]
Value: The literal parameter (do not use quotes for character strings)

When you are satisfied with your job, and you have entered all required information, save it (press 'Ok') and create a script. Simply right on the job in the SQL Server Agent / Jobs treeview and click Script Job As - Create To - New Query Editor Window. You can now use this script to re-create the job. I find it infinitely easier to make minor modifications to the generated script (which is just some T-SQL) and re-create the package than use the bulky and slow interface (which I had to do). One thing, though, caught me by surprise:

If you added a variable that ends in a backslash, you need to escape it. The T-SQL script generated will treat the single backslash as an escape for the quote mark that is used for the command line parameters. For example, assume your value is (without the quotes): "\\machine\folder\temp\". This will result in an error when executed because the last backslash is not escaped and on the command line will cause the a parenthesis mismatch. Instead, use something like "\\machine\folder\temp\\"

Hopefully someone, somewhere finds this useful!

No comments: