Getting a reference to a Script task in VB.net

I am trying to get a reference to a script task so I can manipulate it's properties. However I can't seem to figure it out? I have a reference to Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.Design and based on BOL they show

Imports System

Imports System.Collections.Generic

Imports System.Text

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

Imports Microsoft.SqlServer.Dts.Runtime

But I get errors saying

Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask

Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask

can't be found? What do I need to reference or Import to be able to see the Tasks and/or Task types so I can convet InnerObject to a ScriptTask type and manipulate?

What am doing wrong?

Dim pkgPath As String = "C:\Program Files\\ETL\ODS\Policy\"Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ? = ctype(t.InnerObject,?)

End If

Next

You cannot manipulate properties of objects in the package directly from script within the package. Period.

What you CAN do is put expressions on properties which affect them at execution-time.

-Jamie

|||

I am not trying to do it from within a package. this is outside of SSIS in the vb.net IDE.

I have figured it out.

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

Module SSISPackage

Public Sub main()

Dim pkgPath As String = "C:\Program Files\Insurity\Reporting Decisions\ETL\ODS\Policy\"

Dim pkgName As String = "LoadOdsCountryCodes"

Dim pkg1 As String = pkgPath + pkgName + ".dtsx"

Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"

Dim app As Application = New Application()

Dim pkg As Package = app.LoadPackage(pkg1, Nothing)

Dim x As Executable

For Each x In pkg.Executables

Dim t As TaskHost = CType(x, TaskHost)

If t.Name = "SCT Set Global Variables" Then

Dim sct As ScriptTask = CType(t.InnerObject, ScriptTask)

Dim cp As ScriptTaskCodeProvider = sct.CodeProvider

Dim Moniker As String = "dts://Scripts/" & sct.VsaProjectName & "/" & sct.VsaProjectName & ".vsaproj"

Dim code As String = cp.GetSourceCode(Moniker)

End If

Next

But still does not seem to show the user text added by the developer? Strange.

|||

I had to add a reference to:

Microsoft.SqlServer.ScriptTask

Microsoft.SqlServer.VSAHosting

to my project and then this would work:

Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask

|||

Ah OK. Sorry, my bad!

Glad you got it working.

-Jamie

sql