Execution Patterns - SQL Server Integration Services Design Patterns - page 80

Note “Are message boxes bad?” Absolutely not! In fact, they're the only way to
troubleshoot a certain class of errors in SSIS. I use them all the time, but I qualify the
message box calls in an If/Then statement. If you don't do this, the message box calls
will execute and cause SQL Server Agent jobs to either fail or lie to you about execu-
tion success.
All is not lost. The problem here is that a service account is providing the security
context for the execution. The account used to start the SQL Server Agent service is
the account used to execute the packages from SQL Server Agent jobs. That account
typically does not have the InteractWithDesktop role assigned, and you have to
admit—a desktop is handy for displaying message boxes. The caveat is this: you can-
not include unqualified calls to message box displays in SSIS packages. Use a paramet-
er or variable (I use one called Debug ) and make sure its value is external to the pack-
age so you can turn it on and off when you want to display message boxes.
You can also execute the Parent.dtsx package from the SSIS Catalog. In
SSMS Object Explorer, continue drilling into the Chapter2 folder. Open Projects, then
Chapter2, then Packages, and right-click the Parent.dtsx package. Click Execute
and supply TestSSISApp for the ApplicationName parameter. When you click the
OK button, the package executes and the two message boxes appear. Why? Because
you are no longer running the security context of the service account that starts the
SQL Server Agent service; you are running in the security context with which you con-
nected to SSMS Object Explorer. This is most likely a domain or machine account that
uses Windows Authentication and your personal credentials. If you've been watching a
desktop all this time, you (and all the other users in your domain or machine) have the
InteractWithDesktop role assigned. But almost all service accounts do not parti-
cipate in the InteractWithDesktop role.
Running the Custom Execution Framework with SQL
Server Agent
You can run SQL Server Agent jobs with the custom execution framework. You just
cannot pop up message boxes. For example, you can create an SSIS application for
each “step” in your process. The SSIS application can contain SSIS packages that can
execute in parallel. You then build a SQL Server Agent job with several job steps—one
for each SSIS application. A SQL Server Agent job executes its steps serially, waiting
for one to succeed (by default) before starting the next.