THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

Schedule an SSIS Application with SSIS Framework Community Edition

ALyellowThe 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:

SchedFWa

Log in an instance of SQL Server, expand SQL Server Agent, right-click Jobs, and click “New Job…”:

SchedFW0

Supply a Name when the New Job window displays:

SchedFW1

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

SchedFW2

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:

SchedFW3

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

SchedFW4

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

SchedFW5

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

SchedFW6

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

SchedFW7

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…”:

SchedFW8

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

SchedFW9

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

SchedFW10

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

SchedFW11

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):

 

SchedFW12

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:

Designing an SSIS Framework (recording)
Biml in the Enterprise Data Integration Lifecycle (recording)
From Zero to Biml - 19-22 Jun 2017, London 
IESSIS1: Immersion Event on Learning SQL Server Integration Services – Oct 2017, Chicago

Published Tuesday, May 9, 2017 12:19 AM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement