SSIS Tip #1: Set SSIS package variables via SQL Agent jobs
I've recently been digging into SQL Server 2005 Integration Services (SSIS), and I'm really impressed with how powerful a tool this package can be, and the wide range of tasks that can be accomplished in a short amount of time. Lately I've been researching how SSIS packages can help with our ETL (Extract, Transform & Load) data warehousing projects, and the results have been strong enough to convince me that SSIS is an often overlooked, valuable tool for much more than basic DTS-ish data flows. I'll be posting some of my favorite tips as I continue to explore...
Today's topic involves setting package variables via a SQL Agent job. A package is a set of SSIS tasks, including data management, file manipulation, custom scripting, and much more. For this example, I needed to run one package via a SQL Agent job to produce a nightly export. This is all fairly straightforward, until I realized that we needed three separate exports created, which could be run separately at scheduled times, or on demand. In order to avoid having three very similar SSIS packages, it was time to explore setting package-level variables via SQL Agent... this allowed me to create three separate SQL jobs, all using the same package... the only difference is the variable value I assign to each job (FYI: my packages use this variable to retrieve other custom configuration values from a database; for brevity I'll keep moving forward).
After you've compiled your package and deployed it to SQL server, create a SQL Agent (SQL Server Agent --> Jobs --> New Job). Fill out the basics on the General tab, then move to the Steps tab. Here's where the fun begins: Name your step and select "SQL Server Integration Services package" as the type. Locate your package source and physical location (SQL stored or File based). Once your package is selected you can configure as needed, this is the spot I used to differentiate my three packages by a single variable.
The "Set Values" tab allows you to set various property values of the package contents; the syntax is a little funky, but here's how I set the value of my RequestTypeId package variable:
Property Path: \Package.Variables[RequestTypeId].Value
Value: 14
This proved to be handy little trick, and provided a nice means of package reuse. I'll try to post a few more tiplets in the coming days...