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
