Exam Prep 70-463: Executing packages in SSIS 2012


This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012.  So far, we’ve covered:

Finally.  After all that development, it’s finally time to actually run our package.  So, how do we do that?  With the project deployment model and SSISDB, executing packages in SSIS 2012 is actually pretty easy, and you’ve got quite a few options.  A lot will depend on when you’d like to run the package: some options are better for ad-hoc executions, some are for automated execution.  Another factor will be where you’ve deployed your package, since not all execution methods can be used to access packages stored in SSISDB.  Let’s take a look.

SQL Server Management Studio

The easiest way to execute a package deployed to SSISDB on-demand is through the SSMS interface.  Right-click on the package and click Execute.  This will open the Execute Package dialog box, where you can specify values for parameters, change connection managers, override properties in your package, and specify a logging level.  Once you’ve configured everything, click OK to execute.

Executing a package using SSMS

Executing a package using SSMS

 

T-SQL

Packages stored inside the SSISDB can also be executed via TSQL.  Executing packages programmatically is a 3-step process.  First, you need to create a new execution operation using the create_execution procedure.  This allows you the ability to reference this particular execution to set parameters, check performance or view messages later.  Once the execution operation has been created, you can configure any runtime parameters or properties using the set_execution_parameter_value procedure.  Finally, start the execution using the start_execution procedure.  To run the same package from the previous example, we would run the following statements:


Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name=N'FillStageTablesParameters.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'70_463 Exam Prep', @project_name=N'TK 463 Chapter 9 Prod', @use32bitruntime=False, @reference_id=Null

Select @execution_id

DECLARE @var0 smallint = 1

EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

EXEC [SSISDB].[catalog].[start_execution] @execution_id

GO

As with most things in SSMS, you can use the Execute Package dialog box to configure everything like you want it, then script the execution from there, making it a whole lot easier.

SQL Server Agent Jobs

If you’re using SSIS to load a data warehouse, it’s very likely that you’ll want to execute packages in a more automated fashion.  And for that you’ll likely use SQL Server Agent Jobs.  Configuring a job step to run a package stored in SSISDB, is simple.   Create a new job and add a job step.  In the new job step, select SQL Server Integration Services Package type, and select the appropriate Package Source (e.g. SSIS Catalog) and Server.  Enter the package path or click the ellipsis to select your package.  Click on the Configuration tab and you’ll see the same options for configuring parameters, properties, and connection managers that you saw in the Execute Package dialog.  Once you’re done, click OK.  Schedule your job and you’re good to go.

Executing a package via a SQL Server Agent job

Executing a package via a SQL Server Agent job

Command-line execution methods

Of course, there’s still the DTExec.exe command line utility for executing packages outside of the SSMS environment.  Note that there’s a difference between DTExec and DTExecUI.  DTExec is a command line utility that can be used to execute packages deployed to SSISDB, as well as packages deployed to a file system or even the msdb database.  DTExecUI, however, is a GUI interface that cannot be used to run packages stored in SSISDB.

For the PowerShell geeks out there, Microsoft’s got you covered too.  Although there aren’t any SSIS PowerShell cmdlets yet, you can still use the SSIS management assembly to access and execute packages stored in SSISDB.  I won’t go into specific examples here, but check out the resources at the end of this post for great examples from Matt Masson at MSDN.

Additional Resources

For more information on executing packages in SSIS 2012, check out the following resources:


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

3 thoughts on “Exam Prep 70-463: Executing packages in SSIS 2012