Using the Script Task


After you understand the previously described settings, you still need to learn how to write Visual Basic code and how to build Script Tasks. Of course, this chapter isn't a Visual Basic tutorial, so that isn't covered here. This chapter assumes you have a working knowledge of Visual Basic and focuses mainly on working with the Dts object and on practices you should follow when building Script Task code.

The Dts Object

The Dts object or ScriptObjectModel object is the way you access the package object model in the Script Task. The package, application, and other interfaces have been removed to only expose those objects, methods, and interfaces that are legal to use inside of tasks. The Dts object is the base object for accessing all the services available in the SSIS runtime and is how the Script Task interacts with the package.

To see the available collections and methods for this object, find the ScriptObjectModel node in the Object Browser, as shown in Figure 15.6.

Figure 15.6. Viewing the Script Task object model


The Visual Studio design environment also provides IntelliSense support for the ScriptObjectModel object. Simply type in "Dts." and IntelliSense appears. Because the ScriptObjectModel is how you interact with the package, the following discussion covers each of its methods or objects and how to use them.

The Log Method

This is a shortcut to logging and only takes three parameters:

  • Message text The string message

  • DataCode The integer identifier for the contents of the DataBytes

  • DataBytes The array of bytes storing additional log eventspecific information

The New Method

This is the public constructor that has no practical value in the Script Task.

The Connections Collection

This is how the Script Task accesses connection managers. You can use either the connection manager name or ID for the index.

The Events Interface

This is the IDTSComponentEvents interface that supports the following seven events:

  • FireBreakpointHit This event has no practical function. You can use it to fire a breakpoint, but there is no way to enable the breakpoint in the designer because the Script Task does not support registering breakpoints. So, if you fire this event from the Script Task, nothing will be listening on the other side.

  • FireCustomEvent Use this event if you need to fire a custom event.

    Tip

    This event can also be used as a pseudo custom breakpoint. The Script Task registers custom events, so it is possible to enable a breakpoint on this event and fire it whenever you want to have an event without side effects such as a breakpoint event.


  • FireError Use this event to alert the user to critical errors.

  • FireInformation Use this event to provide information to which the user would not otherwise have access.

  • FireWarning Use this event to provide information about potentially problematic settings or failures that are not critical.

  • FireProgress For long-running tasks, use this event to provide progress information.

  • FireQueryCancel For long-running Script Tasks, use this event to determine if the Script Task should terminate prematurely.

The ExecutionValue

The ExecutionValue is a way to provide more information about the result of the task's execution than just the result provided by the ExecutionResult property. Because it is an object, you have more flexibility in what you return. The SSIS runtime can be set up to place this value in a variable.

The Logging Object

This object provides more information about the logging infrastructure and a more flexible Logging method. The following are the properties and their purpose:

  • Enabled A read-only value that tells you if logging has been enabled for the Script Task.

  • GetFilterStatus A value that returns the filtered event name strings.

  • Log The longer version of the Log method mentioned earlier; it should be avoided because the SSIS runtime overwrites any values you provide for the additional parameters not visible in the abbreviated version.

Caution

Although it would appear that, using this method, you can create a new log entry type, the designer does not currently support it. There is no way to register the log event so it cannot be enabled in the designer.


The TaskResult

This is the value you set to establish if the Script Task succeeded or failed. This value must be set intentionally and be accurate because the SSIS runtime keys off this value in determining the evaluation of precedence constraints.

The Transaction Object

This is the transaction created on the Taskhost container and made available to the Script Task to pass to connection managers when calling AcquireConnection so that the connection can enlist in the DTC transaction.

VariableDispenser

This is the mechanism you can use to lock and retrieve variables.

Variables

This is the simplest way to access the Variables collection and contains only those variables that have been reserved by entering them in the Script Task dialog box, as shown previously.

Suggested Practices

After covering the technical details of how to build a Script Task, the following sections cover some suggested practices that will make the Script Tasks in your packages easier to manage and understand. It's easy to get sloppy with the Script Task. After all, it's easy to discover the methods you need to call and the environment goes a long way toward ensuring the viability of the code before you ever run it the first time. But, there are still a lot of "gotchas" and you'll be creating headaches for yourself if you don't follow some basic guidelines, as discussed in the following sections.

Use Exception Handling

This one should go without saying, but is often overlooked or ignored. Using exception handling makes it possible to correctly handle unexpected behavior and, for any substantial code, pays off in the long run, especially if you do some diagnostic output in the Catch block. For example, at a minimum, you should raise an error event in the exception, as follows:

' Initialize the task result to success. Dts.TaskResult = Dts.Results.Success Try        ' Try to add a new variable        Dts.Variables.Add("TestVariable", False, "", "Test value") Catch ex As Exception        ' Write out an error, with the exception generating target site        ' and the exception message        Dts.Events.FireError(1, ex.TargetSite.ToString(), ex.Message, "", 0)        ' Always execute this code and return gracefully.        ' Never return with an exception from script tasks.        Dts.TaskResult = Dts.Results.Failure End Try 


This code attempts to create a new variable during execution. The Integration Services runtime locks all collections at execution time, so this call fails and causes an exception. The Catch block catches the exception and raises an error event. The Integration Services runtime writes a message to the log and DTExec writes the error to the standard output.

Catching exceptions this way is correct. Raising an error this way is good practice. With one simple call, you're able to create an event that can be handled inside the package, write out to the log ensuring that you have some hint why the task failed, and write out the error to the console, making it possible to see the same information, but on the command line. Further, it allows you, the package writer, to handle the failed task in a graceful way because the script actually captures the exception and sets the ExecutionResult to Failure. If you don't correctly handle the exception in this way, the returned ExecutionResult will likely be wrong and the package will proceed as though the task succeeded. The sample package called InteractiveModeTest.dtsx contains this code. When run on the command line with DTExec, you receive the following message:

Error: 2006-01-02 19:27:13.01    Code: 0x00000001    Source: Test Script for Illustrating InteractiveMode and Handling Exceptions    Microsoft.SqlServer.Dts.Runtime.Variable Add(System.String, Boolean, System.Str...    Description: Exception from HRESULT: 0xC0016026 End Error 


Although not terribly informative, this still provides a hint and a place to start when trying to determine what went wrong with the package, which is much better than the alternativenothing. The HRESULT is a good place to start, so go to MSDN and find the error. As of this writing, the errors are documented on http://msdn2.microsoft.com/enus/library/ms345164.aspx. A simple web search for "SSIS Error" or "DTS_E_" takes you to this list.

Scroll down to the error ID for the error and this is what you find.

0xC0016026    DTS_E_COLLECTIONLOCKED    This collection cannot be modified during package validation or execution. 


Now, because the code adds the TargetSite information, you know that the call causing the error is the Add call. You know the name of the task that caused the error, which is found in the Source. The TargetSite information and the package name combined with the error information makes it clear that the reason the Script Task is failing is because of the Add call.

To summarize, use exception handling to gracefully handle exceptions, log the resulting errors, and correctly set the execution result of the Script Task. If this sounds like too much work, try figuring out what caused your 15-task, 25-transform package to fail with no errors at 2:00 a.m.

Use Meaningful Comments

This is another "yeah, duh" practice, but often falls by the wayside in the rush of hurried projects and tight deadlines. This practice is universally advocated across all coding environments, so there's no need to say much more about it here. Just make it so.

Use FireProgress

If the Script Task is long running, sending progress messages helps the user get a sense that the package is making some progress. At a minimum, progress of 0% and 100% should be fired. The Progress window in the designer shows these events along with the other tasks, making it easy to see the order of package execution. The logs also show these events if they have been enabled in the logging filters.

Avoid Modal Dialog Boxes

Do not use message boxes (or other modal dialog boxes) in production. This probably sounds funny in a book with samples full of message boxes. But, we do a lot of things for illustration purposes that we wouldn't do in production packages. Just understand that placing a message box in a package might cause your package to block until someone notices after two weeks "Hey, the nightly run isn't finishing" and finds out that the packages are all waiting for someone to click the OK button.

Share Libraries in Assemblies

If you have code that you regularly use in multiple locations, you should place it in a compiled assembly and then reference the code in the Script Task. This is one step closer to a custom task, but allows you to be more flexible in what you place in the library and how you call it. This is ideal for the consultant who wants to combine all the code she's writing at various client sites into one reusable Script Task library assembly.

Use Spacing to Eliminate Code Density

Code is always easier to read if it is phrased. Just like paragraphs of an article or this book, if the script is not separated semantically, it is very hard to understand. The Visual Studio Editor goes a long way toward ensuring that the code subscribes to some modicum of formatting sanity, but it doesn't enforce spacing and good commenting. Pieces of code that are related should be bunched together into groupings with some space between.

Eliminate Junk Code

When asked to review code other developers have written, especially when it's giving them trouble, usually, the first thing to do is delete all the commented code. It's confusing and gets in the way. Often, you think you have some errant code commented out, but don't. Or, you think some code is compiling, but it's commented out. Do yourself a favor and just delete temporary code. At worst, move the code to a temporary file somewhere out of site and mind. It makes the code a lot easier to understand and diagnose.

Use Connection Managers for Accessing Resources

The same rules apply here as for other components. If you hard-code filenames and so on, you'll have to edit the Script Task later for any changes that inevitably happen. Using connection managers for accessing files and servers means the package can be managed and configured more easily.

Use Variables for Parameters

Don't hard-code values that you pass as method parameters. Create variables to contain the parameter values, even if they're just at the scope of the Script Task. Again, variables can be configured or easily modified in the package designer without ever needing to open the Script Task designer.

Don't Write Spaghetti Code

It seems like many Script Tasks have Main functions as long as your arm. Arguably, if the work you're doing is getting that complex, you should break it up into smaller functions, put most of the code into a class library assembly, or write a custom task. A good rule of thumb is if the Main function is much longer than the editor window, you should break it down into subfunctions. This is common coding wisdom, but it is often overlooked when it comes to the Script Task.

Log Well

For very simple Script Tasks, the most you'll likely end up with is an error event in the Catch block, as described previously. However, for complex Script Tasks, you should log significant information.

TaskResult

If you don't correctly set the TaskResult, the parts of the package following the Script Task might be executing under false pretenses. Make sure you check all return paths from the Script Task. If the main section of logic (or, in other words, the thing that you are trying to accomplish with the Script Task) successfully completed, the task is successful. If not, the task failed.

Don't Use the ActiveX Script Task

The ActiveX Script Task is deprecated. Use the Script Task. It's better!



Microsoft SQL Server 2005 Integration Services
Microsoft SQL Server 2005 Integration Services
ISBN: 0672327813
EAN: 2147483647
Year: 2006
Pages: 200
Authors: Kirk Haselden

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net