Thursday, March 27, 2008

Migrating the Dynamic Property Task

The DTS Dynamic Property Task does not have a one-to-one migration path to SSIS. To emulate the same functionality, you can use package configurations, variables, expressions, or decide to get rid of the dynamic property piece entirely. Which of these options you choose is completely dependent on your scenario, but I’ve put together a quick and dirty mapping from the type of dynamic property to the new SSIS functionality.

DTS Dynamic Property Task TypeSSIS Functionality
INI FilePackage Configuration
QueryExecute SQL Task, Variable, Expression
Global VariableExpression
Environment VariablePackage Configuration
Data FilePackage Configuration

Good luck in your migration!

Version: SQL Server 2000 SP4, SQL Server 2005 SP2

Tuesday, March 18, 2008

InteractiveMode Variable

In DTS, message boxes were a great way to debug packages and especially ActiveX scripts. In SSIS, other methods of tracking down issues, such as breakpoints and logging, are the way to go. In fact, message boxes aren't allowed in SSIS unless you are running the packages on a client machine. If you run a package on a server with the following code:

MsgBox("This is a message box!")

You'll see an error message similar to this:

Error: 2008-03-18 23:00:03.30 Code: 0x00000002 Source: Script Task Description: The script threw an exception: Showing a modal dialog box or form when the application is not running in UserInteractive mode is not a valid operation. Specify the ServiceNotification or DefaultDesktopOnly style to display a notification from a service application. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:00:02 PM Finished: 11:00:03 PM Elapsed: 0.546 seconds. The package execution failed. The step failed.

If you come across that one user that just can't give up their precious message boxes, you can use this code:

If CBool(Dts.Variables("InteractiveMode").Value) = True Then
MsgBox("This is a message box!")
End If

This will allow the user to see their message box on the client, but repress the error when running on the server!

Version: SQL Server 2005 SP2

Wednesday, March 12, 2008

Anything technical that piques my interest

Every blog needs to have a welcome post, so welcome! My name is Jessica Moss (could you tell from the title of the blog? ;) ), and I'm a Microsoft SQL Server Business Intelligence Mentor with Solid Quality Mentors. I specialize in Integration Services but work with the entire BI suite of products. I'll mostly be posting about cool things I find in my BI adventures, tips and tricks, and anything technical that piques my interest. I would love to hear from you in the form of comments and/or questions. Enjoy!