Schedule an SSIS Application with SSIS Framework Community Edition

Note: This post was originally posted at SQLBlog.

The coolest thing about SSIS Framework Community Edition is it’s free. You can click that link to head over to the DILM Suite site where you can learn more and find a link to the source code, documentation, and sample SSIS projects on GitHub.

I’m overwhelmed by the number of folks using this free utility. Believe it or not, there are a handful of large enterprises (still) running early versions of my Framework – versions that are not integrated with the SSIS Catalog (because the SSIS Catalog didn’t exist back then) – and with less functionality.

Scheduling Execution

This post is about scheduling execution of an SSIS Application using SQL Agent. In this post we will schedule the sample SSIS Application that ships with SSIS Framework Community Edition: the aptly-named “Framework Test” application that ships with the (open) source. You can view the SSIS Application metadata by querying the custom.Applications table:

Supply a Name when the New Job window displays:

Click on the Steps page. Click the New button to create a new job step:

Supply a Job Step Name. Select “SQL Server Integration Services Package as the Job Step Type. Enter a Server that hosts an SSIS Catalog and SSIS Framework Community Edition. Click the ellipsis beside the Package textbox (hidden in the image below) to display the “Select an SSIS Package” dialog. Expand the SSISDB node, Framework node, and select the Parent.dtsx SSIS package:

Click the OK button. Your Job Step should appear similar to that shown below:

Next click on the Configuration tab. Click the ellipsis beside the ApplicationName parameter Value:

Set the Value property of the ApplicationName parameter to Framework Test:

Click the OK button. The Value “Framework Test” will appear in bold as shown below to indicate the value has been configured:

Click OK a couple times to close and save the SQL Agent Job.

Let’s Test It!

Right-click the job and click “Start Job at Step…”:

If all is properly configured, you should see a successful job execution:

Expand the SSMS Object Explorer “Integration Services Catalogs” node. Right-click SSISDB, hover over Reports, hover over Standard Reports, and click “All Executions”:

You should see a successful execution of the Parent.dtsx package at the top of the All Executions report, similar to that shown here:

Click the Overview link to view the Overview report. You should see the name of the SSIS Application – Framework Test – in the Parameters Used tablix, similar to that shown below (click to enlarge):

Metadata

SSIS Framework Community Edition is metadata-driven. A document titled “SSIS Framework Community Edition Documentation and User Guide” is included with the download. You can add metadata to create your own applications by following the example shown in the section named “Adding an Application to SSIS Framework Community Edition.” For a shortcut, check out the T-SQL script in the post titled Adding an SSIS Application to SSIS Framework Community Edition.

Using metadata to drive execution of a collection of SSIS packages adds complexity to the setup process.

The complexity is worth it – especially if you have lots of SSIS package to execute. But if you want, you can continue creating a job step for each and every SSIS package your enterprise executes. It’s completely and totally up to you.

:{>

—

You might like working with Enterprise Data & Analytics because we grok DevOps and SSIS, and we are here to help.

—

Learn More:

Expert SSIS Training Online with Brent Ozar Unlimited!
IESSIS1: Immersion Event on Learning SQL Server Integration Services – 2-6 Oct 2017, Chicago
What is the DILM Suite? 2. SSIS Catalog Compare
What is the DILM Suite? 1. SSIS Framework Community Edition
Adding an SSIS Application to SSIS Framework Community Edition
Schedule an SSIS Application with SSIS Framework Community Edition
The Heart of SSIS Framework Community Edition-Parent.dtsx
SSIS Framework Community Edition Updates
My Latest Book – Building Custom Tasks for SQL Server Integration Services – Is Now Available!