INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

DTS ActiveX script convertion to SSIS script task.

DTS ActiveX script convertion to SSIS script task.

(OP)
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:

CODE --> VB

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:

CODE --> VB

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 = "\\" & 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 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:

CODE -->

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

RE: DTS ActiveX script convertion to SSIS script task.

(OP)
I sloved it myself.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close