Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2008 > BOOK: Professional Microsoft SQL Server 2008 Integration Services ISBN: 978-0-470-24795-2
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Professional Microsoft SQL Server 2008 Integration Services ISBN: 978-0-470-24795-2
This is the forum to discuss the Wrox book Professional Microsoft SQL Server 2008 Integration Services by Brian Knight, Erik Veerman, Grant Dickinson, Douglas Hinson, Darren Herbold; ISBN: 9780470247952
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Professional Microsoft SQL Server 2008 Integration Services ISBN: 978-0-470-24795-2 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 3rd, 2009, 11:52 PM
jminatel's Avatar
Wrox Staff
Points: 17,437, Level: 57
Points: 17,437, Level: 57 Points: 17,437, Level: 57 Points: 17,437, Level: 57
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2003
Location: Indianapolis, IN, USA.
Posts: 1,933
Thanks: 57
Thanked 128 Times in 97 Posts
Default Article: Script Task (.NET) in SQL Server 2008 Integration Services (SSIS)

This article is exerpted from chapter 3 SSIS Tasks of the Wrox book Professional Microsoft SQL Server 2008 Integration Services by by Brian Knight, Erik Veerman, Grant Dickinson, Douglas Hinson, Darren Herbold and is reused by permission of the publisher. This may not be reused without publisher permission
Script Task (.NET)
The Script Task allows you to access the Microsoft Visual Studio Tools for Applications (VSTA) environment to write and execute scripts using the VB and C# languages. The VSTA environment is new in the latest version of SSIS and replaces the Visual Studio for Applications (VSA) environment from the 2005 version. Scripting now is almost a misnomer because the latest SSIS edition solidifies the connection to the full .NET libraries for both VB and C#. The latest addition to SSIS of the VSTA environment and the Script Task in general also offer these extra functional advantages:
  • A coding environment with the advantage of IntelliSense
  • An integrated Visual Studio design environment within SSIS
  • An easy-to-use methodology for passing parameters into the script
  • The ability to add breakpoints into your code for testing and debugging purposes (for only one Script Task per package)
  • The automatic compiling of your script into binary format for a speed advantage (This was configurable in earlier releases of SSIS.)
The Script Task is configured through the Script tab in the Script Task Editor (shown in Figure 3-6). The ScriptLanguage property is where you select the .NET language you’d like to use in the task. Notice that the default language is set to C#, so if you are coding in VB.NET, don’t whiz through these settings when setting up your Script Tasks. If you have done any SSIS development with the previous versions, you’ll also notice that the PreCompileScriptIntoBinaryCode property has been permanently removed. The result is that all scripting code will automatically compile and persist into the package. This speeds up and reduces runtime errors in the task significantly.
The EntryPoint property allows you to provide an alternative function to call initially when the ScriptMain class is instantiated. Typically, you’ll leave this set to the default Main() function. The ReadOnlyVariables and ReadWriteVariables properties allow you to pass SSIS variables into the script as a listing of variable names separated by commas. The typing out of the variable names was a little unconventional in the earlier versions of SSIS, but the latest version provides the capability of browsing to the variable collection and selecting the variables. Having these variables provides a significant advantage when coding. You only need to refer to them by ordinal position or by name in the Variable collection to be able to access their values without worrying about locking, unlocking, or blocking variables during read and write actions. Just make sure you have the variables you wish to write back to in the ReadWriteVariables property, or you’ll get an error in the script. There are also alternative methods for altering variables that aren’t provided in these collections during set up that are demonstrated in Chapter 9 on scripting.
When you click the actionable Edit Script button, the Visual Studio Tools for Applications environment opens to allow coding directly in the class ScriptMain. In this IDE, you have access to all the advanced debugging tactics, breakpoints, and IntelliSense found in the Visual Studio environment. If you create a package with a variable named myValue containing the string "Hello World", and set up the Script Task like Figure 3-7, the following example shows you how to write code that uses the passed-in myValue variable:
csharp Code:
public void Main()
{
    if(Dts.Variables.Contains(“User::MyValue))
    {
        System.Windows.Forms.MessageBox.Show("MyValue=" + Dts.Variables
[“User::MyValue].Value.ToString());
    }
 
    Dts.TaskResult = (int)ScriptResults.Success;
}


vbnet Code:
Public Sub Main()
    If Dts.Variables.Contains(“User::MyValue”) = True Then
        System.Windows.Forms.MessageBox.Show("myValue=" & Dts.Variables
(“User::MyValue).Value.ToString())
    End If
 
    Dts.TaskResult = ScriptResults.Success
End Sub

First, the script checks for the existence of the variable, and then pops up a message box with the famous message “Hello World” as you see in Figure 3-7.
This is just a very simple example of the Script Task in action. We’ve created an entire chapter to dive into the details and specific use cases for both the Script Task and the Data Flow version called the Script Component, so go read Chapter 9 for more information.
Attached Images
File Type: jpg Figure 3-6.jpg (53.6 KB, 19 views)
File Type: jpg Figure 3-7.jpg (11.3 KB, 16 views)
__________________
Jim Minatel
Associate Publisher, WROX - A Wiley Brand
Blog: http://p2p.wrox.com/content/blogs/jminatel
Wrox on Twitter: http://twitter.com/wrox
Did someone here help you? Click on their post!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Integration Services WMI Event Watcher Task Harald SQL Server 2005 3 July 3rd, 2013 01:37 AM
SSIS integration gunjan.sh SQL Server 2005 0 January 10th, 2008 06:22 AM
SQL SSIS Send Mail Task mstoler SQL Server 2005 0 August 8th, 2006 07:35 AM



All times are GMT -4. The time now is 09:53 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.