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:
- Tables and Schemas in the data warehouse
- Dimensions and Slowly Changing Dimensions
- Fact tables and measures
- Intro to columnstore indexes
- Columnstore indexes and partitioning
- Introduction to SQL Server Data Tools (SSDT)
- Connection Managers
- Control Flow Tasks and Containers
- Data Flow Source Adapters
- Data Flow Destination Adapters
- Data Flow Transformations
- Variables and Parameters
- SSIS Expressions
- Transactions and Checkpoints
- Logging in SSIS 2012
- Deploying projects in SSIS 2012
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
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
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:
