title: Convert DTS ActiveX to Script Task in SSIS
body: <p>I'm new to this forum and this is my first thread so I hope I am posting it in the right location. </p>
  
  <p>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: <a href="http://imgur.com/9EG7zB5" rel="nofollow">Link</a>. 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: </p>
  
  <pre><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  = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\SourceFiles\tbl_L47T1.txt"
  sSourcePath = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\SourceFiles\"
  sBackupPath  = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\BackupFiles\"
  sErrorPath   = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\ErrorFiles\"
  sFileName = "tbl_L47T1.txt"
  sUDLPath = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\UDL\" &amp; sEnvironm &amp; ".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 &amp; ".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 
  </code></pre>
  
  <p>And this is what I have so far:</p>
  
  <pre><code>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"
  sServer =  "W0254"
  
  
  
  ' --- DO NOT CHANGE ANYTHING BELOW THIS LINE ---
  ' ---
  sSourceFile  = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\SourceFiles\tbl_L47T1.txt"
  sSourcePath = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\SourceFiles\"
  sBackupPath  = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\BackupFiles\"
  sErrorPath   = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\DTS\ErrorFiles\"
  sFileName = "tbl_L47T1.txt"
  sUDLPath = "\\" &amp; sServer &amp; "\Data_sql\" &amp; sEnvironm &amp; "\UDL\" &amp; sEnvironm &amp; ".udl"
  
  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 &amp; ".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 
  </code></pre>
  
  <p>If I debug this I get a lot of errors and I have no clue on how to fix them...</p>
  
  <p>error:</p>
  
  <pre><code>Error: System.Reflection.TargetInvocationException: Het doel van een aanroep heeft een uitzondering veroorzaakt. ---&gt; 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.
   ---&gt; 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&amp; 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()
  </code></pre>
  
  <p>Could someone please help me convert this further? I'm stuck and don't know how to fix my errors...</p>
  
  <p>Thanks in advance!</p>
  
  <ul>
  <li>Michael</li>
  </ul>