SSIS - Scripting
From SQLServerPedia
|
See Also: Main_Page - Code Management - Loaders - Integration Services (SSIS) SSIS offers much built-in functionality which should be sufficient for the majority of your data integration tasks. However, at times you need to enhance your packages with the functionality that isn't readily available in SSIS. To do so you can use the Script task. Important: Do not confuse the Script task with ActiveX Script task. The latter task is used strictly for executing ActiveX scripts developed with DTS 2000 packages. For all new development use the Script task. To write a script, drag the Script Task icon from the toolbox to your control flow. Next, double-click on the script task. This opens a script task editor dialog. The general page of this dialog allows you to change the name and description for your script. You should change these properties to give your script a descriptive name and meaningful description, particularly if you anticipate having multiple scripts. The "Script" page of this dialog is shown below: Even though you see the Script Language drop-down box you can only use Visual Basic .NET language to write SSIS scripts. Other languages are not supported with SSIS 2005. The option "Pre-compile Script Into Binary Code" should be set to True for best performance. Within your script you could have multiple subroutines. The EntryPoint property allows you to specify the method that will be executed first. Normally you should leave this option at its default value - ScriptMain. The other two options on this page allow you to define a comma-separated list of read-only and read-write variables. When you click on Design Script button SSIS opens a Visual Studio for Applications environment where you can write your code. Here is a very simple script you could use to check the hour when the script task is executed. If the task is executed on the 10th hour, it will fail, otherwise it will succeed: ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic ' The ScriptMain class is the entry point of the Script Task. Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Public Class ScriptMain ' The execution engine calls this method when the task executes. ' To access the object model, use the Dts object. Connections, variables, events, ' and logging features are available as static members of the Dts class. ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. ' Public Sub Main() Dim hour As String hour = Now.Hour.ToString If hour = "10" Then Dts.TaskResult = Dts.Results.Failure Console.WriteLine("task failed the hour is: {0}", hour) Else Dts.TaskResult = Dts.Results.Success Console.WriteLine("task succeeded") End If End Sub End Class Once you are done editing your script, you can choose File -> Close and Return to get back to the Script task editor dialog. Close this dialog and choose Debug -> Start Without Debugging option to execute the package, you will see the result similar to the following: Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:34:20 AM Log: Name: PackageStart Computer: MyComputerName Operator: MyDomainName\MyUserName Source Name: SampleScriptTaskPackage Source GUID: {1203CA35-AB5A-4096-A8E3-585DB62E3BB5} Execution GUID: {91C6988E-FFFD-4E47-8338-A4310978DCA0} Message: Beginning of package execution. Start Time: 2007-09-06 10:34:20 End Time: 2007-09-06 10:34:20 End Log task failed the hour is: 10 Log: Name: OnError Computer: MyComputerName Operator: MyDomainName\MyUserName Source Name: Script Task Source GUID: {DAB5AA82-3982-4096-B817-AB5C1467A238} Execution GUID: {91C6988E-FFFD-4E47-8338-A4310978DCA0} Message: The Script returned a failure result. Start Time: 2007-09-06 10:34:21 End Time: 2007-09-06 10:34:21 End Log Error: 2007-09-06 10:34:21.02 Code: 0x00000004 Source: Script Task Description: The Script returned a failure result. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:34:20 AM Finished: 10:34:21 AM Elapsed: 0.701 seconds Note that even though the task failed the package execution completed successfully. Similarly if you execute the same Script task at a different hour, you'll get a success message: "task succeeded". A slightly more useful script below establishes a connection to the local Analysis Services instance and prints the number of databases on this instance to the console window. Note that in order to run this script you must add a reference to the Microsoft.AnalysisServices library (to do so, right-click on References' folder within Project Explorer, choose "Add Reference", click on Analysis Management Objects within the resulting dialog, click "Add", then click "OK"): Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.AnalysisServices Public Class ScriptMain Public Sub Main() Dim myServer As New Microsoft.AnalysisServices.Server Dim dbCount As Integer Try myServer.Connect("D10ZF411\SQL2005Instance") dbCount = myServer.Databases.Count Console.WriteLine("database count equals: {0}", dbCount) Dts.TaskResult = Dts.Results.Success Catch ex As Exception Dts.Events.FireError(0, ex.Message, "connection couldn't be established", "", 0) Dts.TaskResult = Dts.Results.Failure Finally myServer.Disconnect() End Try End Sub End Class Results in the console window: Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 10:56:07 AM Log: Name: PackageStart Computer: MyComputerName Operator: MyDomainName\MyUserName Source Name: SampleConnectionToAnalysisServices Source GUID: {1203CA35-AB5A-4096-A8E3-585DB62E3BB5} Execution GUID: {D683A5CE-9261-4AF5-892F-236E66558489} Message: Beginning of package execution. Start Time: 2007-09-06 10:56:07 End Time: 2007-09-06 10:56:07 End Log database count equals: 5 Log: Name: PackageEnd Computer: MyComputerName Operator: MyDomainName\MyUserName Source Name: SampleConnectionToAnalysisServices Source GUID: {1203CA35-AB5A-4096-A8E3-585DB62E3BB5} Execution GUID: {D683A5CE-9261-4AF5-892F-236E66558489} Message: End of package execution. Start Time: 2007-09-06 10:57:57 End Time: 2007-09-06 10:57:57 End Log DTExec: The package execution returned DTSER_SUCCESS (0). Started: 10:56:07 AM Finished: 10:57:58 AM Elapsed: 110.939 seconds |