asked
DTS ActiveX to SSIS script task or XML config
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 (the SSIS executes corretly if I disable the script but I need it to be dynamic) and decided to re-write it in a SSIS script task. I have 4 global varibles and they all have values already set in the Global variables menu. 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. I have asked this question on several forums and I have been suggested to delete the script altogether and use package configurations and an XML file, is it possible to do what I want with this method and how would I do this? I managed to create the XML file in the configurations but don't know what to write in the XML itself. This is the original ActiveX script on the DTS package: link to the Global variables:
http://imgur.com/9EG7zB5 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 in the VB script task: 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: 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() Thanks in advance! - Michael
Package configurations are definitely one way to go. Have a read of
https://msdn.microsoft.com/en-us/library/cc895212.aspx and/or search google for "SSIS Package Configuration". It is your choice about where to store the Server/database names. Alternatively, you can override the connection string for a connection manager at runtime. SQL Server Agent allows you to set properties of connection managers (and variables) as part of the definition of the job step - assuming that you are using SQL Server Agent. If you are running the package from the command line, you can also do this - have a look in Books Online for the DTExec utility.
Thank you for your reaction. I would rather avoid messing with the SQL server agent since it's run daily on a automatic basis, we don't actually execute it manually. I have done some more reading and I managed to get my XML working on my variables wich is very handy. The question remains (and I can't find an answer anywhere) how do I specify the server and database for the connection manager IN my XML file. I need a line of code to write into my XML wich I can adjust if I want the SSIS package to be executed on a different server and/or database. I will be making a seperate XML file for each table that needs to be updated from my .txt file (this I already set up with a control flow) This is because each txt and it's linked table in the DB have different names so I just make a XML for each package. When this is done I will just copy ALL the packages and XML files and all I need to do is change the server and database connection in EACH XML file but I just need to know HOW I make my XML file able to change the connection for the package it is linked to.
15 People are following this question.
