Programmatically Add A Script Task To An SSIS Package in VB

The following example uses Visual Basic .NET 2005 (.NET 2) to add a Script Task, in code, to an Integration Services package.

References

c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.AnalysisServices.dll
c:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\Microsoft.SqlServer.ADONETSrc.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.DTSPipelineWrap.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.DTSRuntimeWrap.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.PipelineHost.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ScriptTask.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll
C:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.SQLTask.dll
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.SQLTaskConnectionsWrap.dll
c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.TxScript.dll
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\Microsoft.SqlServer.VSAHosting.dll

Imports

Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Code

''' <summary>
''' Add a script task to the package
''' </summary>
''' <param name="objPackage">The package</param>
''' <param name="strTaskName">The task name</param>
''' <param name="strScript">The script - this needs to be a full class definition, with a Public Class ScriptMain and a Public Sub Main()</param>
''' <returns></returns>
''' <remarks></remarks>
Protected Function AddScriptTask(ByVal objPackage As Package, ByVal strTaskName As String, ByVal strScript As String) As TaskHost
Dim objExe As Executable = objPackage.Executables.Add("STOCK:ScriptTask")
Dim objTask As TaskHost = CType(objExe, TaskHost)
objTask.Name = strTaskName
objTask.Description = strTaskName
Dim objScriptTask As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask = objTask.InnerObject
objScriptTask.EntryPoint = "ScriptMain"
objScriptTask.PreCompile = False
'Build the moniker for the project
Dim strMoniker As String = "dts://Scripts/" & objScriptTask.VsaProjectName & "/" & objScriptTask.VsaProjectName & ".vsaproj"
Dim objProject As New System.Text.StringBuilder
'Build the project "file"
objProject.AppendLine("<VisualStudioProject>")
objProject.AppendLine("<VisualBasic Version = ""8.0.50727.791"" MVID = ""{00000000-0000-0000-0000-000000000000}"" ProjectType = ""Local"" ProductVersion = ""8.0.50727"" SchemaVersion = ""2.0"">")
objProject.AppendLine("<Build>")
objProject.AppendLine("<Settings ")
objProject.AppendLine("DefaultNamespace = """ & objScriptTask.VsaProjectName & """ ")
objProject.AppendLine("OptionCompare = ""0"" OptionExplicit = ""1"" OptionStrict = ""1"" ")
objProject.AppendLine("ProjectName = """ & objScriptTask.VsaProjectName & """ ")
objProject.AppendLine("ReferencePath = ""C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ScriptTask\9.0.242.0__89845dcd8080cc91\;C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SqlServer.ManagedDTS\9.0.242.0__89845dcd8080cc91\"" ")
objProject.AppendLine("TreatWarningsAsErrors = ""false"" WarningLevel = ""1"" ")
objProject.AppendLine("RootNamespace = """ & objScriptTask.VsaProjectName & """ >")
objProject.AppendLine("<Config Name = ""Debug"" DefineConstants = """" DefineDebug = ""true"" DefineTrace = ""true"" DebugSymbols = ""true"" RemoveIntegerChecks = ""false"" />")
objProject.AppendLine("</Settings>")
objProject.AppendLine("<References>")
objProject.AppendLine("<Reference Name = ""System"" AssemblyName = ""System"" />")
objProject.AppendLine("<Reference Name = ""System.Data"" AssemblyName = ""System.Data"" />")
objProject.AppendLine("<Reference Name = ""Microsoft.AnalysisServices"" AssemblyName = ""Microsoft.AnalysisServices"" />")
objProject.AppendLine("<Reference Name = ""Microsoft.SqlServer.ScriptTask"" AssemblyName = ""Microsoft.SqlServer.ScriptTask"" />")
objProject.AppendLine("<Reference Name = ""Microsoft.SqlServer.ManagedDTS"" AssemblyName = ""Microsoft.SqlServer.ManagedDTS"" />")
'TODO: Add any additional references here...
objProject.AppendLine("</References>")
objProject.AppendLine("<Imports>")
objProject.AppendLine("<Import Namespace = ""Microsoft.VisualBasic"" />")
objProject.AppendLine("</Imports>")
objProject.AppendLine("</Build>")
objProject.AppendLine("<Files>")
objProject.AppendLine("<Include>")
objProject.AppendLine("<File RelPath = ""ScriptMain"" BuildAction = ""Compile"" ItemType = ""2"" />")
objProject.AppendLine("<VSAAppGlobal VSAAppGlobalName = ""Dts"" ItemType = ""1"" VSAAppGlobalType = ""Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel"" />")
objProject.AppendLine("</Include>")
objProject.AppendLine("</Files>")
objProject.AppendLine("<Folders>")
objProject.AppendLine("<Include/>")
objProject.AppendLine("</Folders>")
objProject.AppendLine("</VisualBasic>")
objProject.AppendLine("</VisualStudioProject>")
objScriptTask.CodeProvider.PutSourceCode(strMoniker, objProject.ToString())
 
'Build the moniker for the script
strMoniker = "dts://Scripts/" & objScriptTask.VsaProjectName & "/ScriptMain.vsaitem"
objScriptTask.CodeProvider.PutSourceCode(strMoniker, strScript)
 
Return objTask
End Function