DTS ActiveX script convertion to SSIS script task.

[Michael843]

Hello everyone,

I'm new to this forum and this is my first thread so I hope I am posting it in the right location.

I don't know any C# but I know some VB, I have migrated my DTS package to SSIS but I'm unable to get the ActiveX script working and decided to re-write it in a script task. I have 4 global varibles and they all have values already set in the Global variables menu. link to a picture: Link. The ActiveX code is there so the user can just alter 2 vars when they switch server/database and it will still do the same on the tables there (all tables have to same name across all the servers and databases so that doesn't need to be adjusted) and it is automaticly run daily. This is the original ActiveX code:

Function Main()

Dim sEnvironm       
Dim sServer            
Dim sSourceFile     
Dim sSourcePath    
Dim sBackupPath  
Dim sErrorPath      
Dim sFileName        
Dim sUDLPath    

'*********************************************************
' Set vars 
' 
' First 2 are depending on the server and db
' FILL IN THE RIGHT VALUES
' 
'*********************************************************
sEnvironm = "MON_Datamart"
sServer =  "W0254"



' --- DO NOT CHANGE ANYTHING BELOW THIS LINE ---
' ---
sSourceFile  = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\SourceFiles\tbl_L47T1.txt"
sSourcePath = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\SourceFiles\"
sBackupPath  = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\BackupFiles\"
sErrorPath   = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\ErrorFiles\"
sFileName = "tbl_L47T1.txt"
sUDLPath = "\\" & sServer & "\Data_sql\" & sEnvironm & "\UDL\" & sEnvironm & ".udl"


FoundError = False

Set oPackage = DTSGlobalVariables.Parent

Set oConnection = oPackage.Connections("tbl_L47T1.txt")
oConnection.DataSource = sSourceFile

Set oConnection = oPackage.Connections("Datamart")
oConnection.UDLPath = sUDLPath 

Set oConnection = oPackage.Connections("Truncate")
oConnection.UDLPath = sUDLPath 

Set oTask = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oTask.SourceObjectName = sSourceFile
oTask.DestinationObjectName =  sEnvironm & ".dbo.stg_tbl_L47T1" 

DTSGlobalVariables("SourcePath").Value = sSourceFile
DTSGlobalVariables("BackupPath").Value = sBackupPath
DTSGlobalVariables("ErrorPath").Value = sErrorPath
DTSGlobalVariables("FileName").Value = sFileName   

Set oTask = Nothing
Set oConnection = Nothing
Set oPackage = Nothing

Main = DTSTaskExecResult_Success

End Function

And this is what I have so far:

Public Sub Main()

        Dim sEnvironm As  String
        Dim sServer As String
        Dim sSourceFile As String
        Dim sSourcePath As String
        Dim sBackupPath As String
        Dim sErrorPath As String
        Dim sFileName As String
        Dim sUDLPath As String
        Dim FoundError As Boolean
        Dim oPackage As Object
        Dim oConnection As Object
        Dim oTask As Object
        Dim DTSGlobalVariables As Object



        '*********************************************************
        ' Set vars 
        ' 
        ' First 2 are depending on the server and db
        ' FILL IN THE RIGHT VALUES
        ' 
        '*********************************************************
        sEnvironm = "MON_Datamart"
[indent]sServer =  "W0254"



' --- DO NOT CHANGE ANYTHING BELOW THIS LINE ---
' ---
sSourceFile  = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\SourceFiles\tbl_L47T1.txt"
sSourcePath = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\SourceFiles\"
sBackupPath  = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\BackupFiles\"
sErrorPath   = "\\" & sServer & "\Data_sql\" & sEnvironm & "\DTS\ErrorFiles\"
sFileName = "tbl_L47T1.txt"
sUDLPath = "\\" & sServer & "\Data_sql\" & sEnvironm & "\UDL\" & sEnvironm & ".udl"
[/indent]

FoundError = False

oPackage = Dts.Variables("User::VariableName").Value

oConnection = oPackage.Connections("tbl_L47T1.txt")
oConnection.DataSource = sSourceFile

oConnection = oPackage.Connections("Datamart")
oConnection.UDLPath = sUDLPath 

oConnection = oPackage.Connections("Truncate")
oConnection.UDLPath = sUDLPath 

oTask = oPackage.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask
oTask.SourceObjectName = sSourceFile
oTask.DestinationObjectName =  sEnvironm & ".dbo.stg_tbl_L47T1" 

DTSGlobalVariables("SourcePath").Value = sSourceFile
DTSGlobalVariables("BackupPath").Value = sBackupPath
DTSGlobalVariables("ErrorPath").Value = sErrorPath
DTSGlobalVariables("FileName").Value = sFileName   

oTask = Nothing
oConnection = Nothing
oPackage = Nothing

Main = DTSTaskExecResult_Success
	End Sub

If I debug this I get a lot of errors and I have no clue on how to fix them...

error:

Error: System.Reflection.TargetInvocationException: Het doel van een aanroep heeft een uitzondering veroorzaakt. ---> Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
 ---> System.Runtime.InteropServices.COMException (0xC0010009): The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

   bij Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100.get_Item(Object Index)
   bij Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
   --- Einde van intern uitzonderingsstackpad ---
   bij Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index)
   bij ST_f32fc12b60f34bebbbdfc0c5e5b40a96.vbproj.ScriptMain.Main()
   --- Einde van intern uitzonderingsstackpad ---
   bij System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   bij System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
   bij System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
   bij System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
   bij System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
   bij System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, CultureInfo culture)
   bij Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

Could someone please help me convert this further? I'm stuck and don't know how to fix my errors...

Thanks in advance!

- Michael

 

[Michael843]

I sloved it myself.