none
Execute DTSX with Threads in Job SQL Server 2008

    Question

  • Señores
    Muy buen dìa, ocurre que poseo una DTSX centra que ejecuta otros paquetes SSIS de manera Asincrona, de tal manera si lo ejecuto manualmente este funciona sin ningun problema, pero si lo ejecuto en un JOB solo se ejecuta el central.

    Podrian aclararme el porque ocurre esto, ¿Tiene Solucion?

    De antemano
    Muchas Gracias


    Saludos

    JP
    Wednesday, December 16, 2009 2:32 PM

All replies

  • Hi,
    Please translate your post in english to get better responces.
    Hope this helps !!
    Sudeep   |    My Blog
    Wednesday, December 16, 2009 2:37 PM
  • Gentlemen 
    Very good day, I have a case that focuses dtsx running other packages 
    SSIS asynchronously, so if I run it manually this 
    works without any problem, but if I run it in a JOB only 
    run the plant. 

    Please clarify why this happens, is there any solution? 

    In advance 
    Thank you very much 


    Greetings
    Wednesday, December 16, 2009 2:42 PM
  • Are you using Execute Package task?

    Where have you saved the child packages, DB or File System?

    Save the Child packages to the server and check that they have the access to the folder if file system is used.
    Are you getting any errors?
    Hope this helps !!
    Sudeep   |    My Blog
    Wednesday, December 16, 2009 2:48 PM
  • Currently no, I'm doing it in a script task with vb.net, where I am implementing the call of each of the ETL children through vb.net threads.

    Deputy code that is responsible for balancing the call of each:


    Public Delegate Function Delegado(ByVal Duracion As Integer, _

    <Out()> ByRef Id As Integer, _

    ByVal DirectorioETL As String, _

    ByVal Dtsx As String, _

    ByVal Banco As String, _

    ByVal DirectorioLocal As String, _

    ByVal Archivo As String) As String

    Public Sub Main()

    If Dts.Variables("Extension").Value <> "CTR" And Dts.Variables("Procesado").Value <> 1 And Dts.Variables("FechaValida").Value = "SI" Then

    Dim Dtsx As String = "NameFile"

    Dim Banco As String = Dts.Variables("Banco_Nombre").Value

    Dim Archivo As String = Dts.Variables("Archivo").Value

    Dim threadId As Integer

    Dim Add As New ScriptMain()

    Dim Invocar As New Delegado(AddressOf Add.CargaDTSX)

    Dim result As IAsyncResult = Invocar.BeginInvoke(Nothing, threadId, DirectorioETL, Dtsx, Banco, DirectorioLocal, Archivo, Nothing, Nothing)

    Thread.Sleep(0)

    End If

    End Sub


    Public Function CargaDTSX(ByVal Duracion As Integer, <Out()> ByRef Id As Integer, ByVal DirectorioETL As String, ByVal Dtsx As String, ByVal Banco As String, ByVal DirectorioLocal As String, ByVal Archivo As String) As String

    Try

    Dim Ssis As New Application

    Dim Etl As New Package

    Etl = Ssis.LoadPackage(DirectorioETL & Dtsx, Nothing)

    Etl.VariableDispenser.LockForWrite("NombreArchivo")

    Etl.VariableDispenser.LockForWrite("Archivo")

    Etl.Variables("NombreArchivo").Value = DirectorioLocal & "\" & Archivo

    Etl.Variables("Archivo").Value = Archivo

    Try

    Etl.Execute()

    Catch ex As Exception

    ArchivoLog("Problemas al ejecutar archivo: " & Dtsx)

    End Try

    Catch ex As Exception

    ArchivoLog("Error al definir variables a archivo: " & Dtsx)

    End Try

    Return "OK"

    End Function

    Wednesday, December 16, 2009 3:30 PM
  • Hi,
    In your code you are using the variable: DirectorioETL but you are not assigning any value to it.

    Hope this helps !!
    Sudeep   |    My Blog
    Wednesday, December 16, 2009 3:44 PM
  • There should be no need to use a Script Task to start an SSIS package "asynchronously" - why do you think you need to do this?  You can easily use an Execute Package Task, and not place any precedence constraints on it, so that the rest of the parent package executes concurrently.
    Todd McDermid's Blog
    Wednesday, December 16, 2009 4:11 PM
    Moderator
  • May be he is using this because he is checking some conditions before executing the package.
    You can make use of precedence constraint to check these conditions and use Execute Package Task.
    Hope this helps !!
    Sudeep   |    My Blog
    Wednesday, December 16, 2009 4:24 PM
  • As you implement the  precedence constraint , actually I'm doing this because things muchisimas valid before executing the child dtsx

    Please be grateful for your help
    Wednesday, December 16, 2009 6:36 PM
  • Please describe the Control Flow of your package in detail, or show a picture of the screen.  (See the FAQ for how to show a picture in a message.)
    Todd McDermid's Blog
    Wednesday, December 16, 2009 7:24 PM
    Moderator
  • How can I pass variable parameters in a Execute Package task
    Wednesday, December 16, 2009 7:25 PM
  • Please don't post a completely unrelated question in an existing thread.  Start another thread.
    Todd McDermid's Blog
    Wednesday, December 16, 2009 7:28 PM
    Moderator
  • It is part of theme
    Wednesday, December 16, 2009 7:42 PM
  • The "theme" is "Integration Services" - that's the forum you're in.  Asking how to pass parameters using an Execute Package Task is a completely different question than asking about threads and executing packages from a Script Task.  I appreciate that you want to know the answer so that you can use an Execute Package Task instead of a Script Task for executing child packages - but it's still a separate question.  People that search this forum later will not find an answer to your second question if we answer it in a conversation with a title about threading in a Script.

    You can always put a link in each thread to refer to the other.  Such as "I need to know how to pass arguments in an Execute Package Task to help solve this problem: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/76b0bcba-906c-4714-9db0-6b63e0ab0927"

    I am not trying to be difficult to you - I just want to help those that come later to find answers when they search.

    And you should search this forum for your second issue - you will find an answer (several).
    Todd McDermid's Blog
    Wednesday, December 16, 2009 7:52 PM
    Moderator
  • Friends
    I need help to know that I'm doing wrong, I have my task
    vb.net script
    This fails to start threads because concludes successfully, it seems they only start when you finish the main method.


    Attach Code:

     <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
        <System.CLSCompliantAttribute(False)> _
        Partial Public Class ScriptMain
            Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

            Enum ScriptResults
                Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
                Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
            End Enum

        Public Delegate Function Delegado(ByVal Duracion As Integer, _
                                                   <Out()> ByRef Id As Integer, _
                                                    ByVal DirectorioETL As String, _
                                                    ByVal Dtsx As String, _
                                                    ByVal Banco As String, _
                                                    ByVal DirectorioLocal As String, _
                                                    ByVal Archivo As String) As String

        Public Sub Main()
            Dim vars As Variables

            Dts.VariableDispenser.LockOneForRead("DirectorioETL", vars)
            DirectorioETL = vars("DirectorioETL").Value.ToString()
            vars.Unlock()

            Dts.VariableDispenser.LockOneForRead("Local_Directorio", vars)
            DirectorioLocal = vars("Local_Directorio").Value.ToString()
            vars.Unlock()
            If Dts.Variables("Extension").Value <> "CTR" And Dts.Variables("Procesado").Value <> 1 And Dts.Variables("FechaValida").Value = "SI" Then
                Dim Dtsx As String = Dts.Variables("Banco_SSIS").Value
                Dim Banco As String = Dts.Variables("Banco_Nombre").Value
                Dim Archivo As String = Dts.Variables("Archivo").Value

                Dim threadId As Integer
                Dim Add As New ScriptMain()
                Dim Invocar As New Delegado(AddressOf Add.CargaDTSX)
                Dim result As IAsyncResult = Invocar.BeginInvoke(Nothing, threadId, DirectorioETL, Dtsx, Banco, DirectorioLocal, Archivo, Nothing, Nothing)
                Thread.Sleep(5000)
                ArchivoLog(result.IsCompleted)
                ArchivoLog("Banco a Cargar: " & Banco & " bajo archivo " & Dtsx)
                Dts.Events.FireInformation(1, "", "Banco a Cargar: " & Banco, "", 0, False)
            End If
        End Sub

        Public Function CargaDTSX(ByVal Duracion As Integer, <Out()> ByRef Id As Integer, ByVal DirectorioETL As String, ByVal Dtsx As String, ByVal Banco As String, ByVal DirectorioLocal As String, ByVal Archivo As String) As String
            'Ejecuta DTSX de carga para Banco identificado en el proceso.
            Try
                Dim Ssis As New Application
                Dim Etl As New Package
                Etl = Ssis.LoadPackage(DirectorioETL & Dtsx, Nothing)
                Etl.VariableDispenser.LockForWrite("NombreArchivo")
                Etl.VariableDispenser.LockForWrite("Archivo")
                Etl.Variables("NombreArchivo").Value = DirectorioLocal & "\" & Archivo
                Etl.Variables("Archivo").Value = Archivo
                Try
                    Etl.Execute()
                Catch ex As Exception
                    ArchivoLog("Problemas al ejecutar archivo: " & Dtsx)
                End Try
            Catch ex As Exception
                ArchivoLog("Error al definir variables a archivo: " & Dtsx)
                Dts.Events.FireInformation(1, "", "Error al definir variables de archivo: " & Dtsx, "", 0, False)
            End Try
            Return "OK"
        End Function

        Private Sub ArchivoLog(ByVal Valor As String)
            'Generacion de Archivo LOG por proceso.
            Dts.VariableDispenser.LockOneForRead("Log", vars)
            RutaLog = vars("Log").Value.ToString()
            vars.Unlock()

            Dts.VariableDispenser.LockOneForRead("FechaContable", vars)
            FechaArchivo = vars("FechaContable").Value.ToString()
            vars.Unlock()

            Try
                Dim archivo As String = RutaLog & "\LogConsolidacion" & FechaArchivo & ".txt"
                Dim log As String = Valor
                Dim SW As New System.IO.StreamWriter(archivo, True)
                SW.WriteLine(log)
                SW.Close()
            Catch ex As Exception
                Dts.Events.FireInformation(1, "", "Error al abrir o crear documento LOG: " & Err.Description & ":" & ex.Message, "", 0, False)
            End Try
        End Sub
    End Class


    PLEASE HELP ME!

    THANKS!

    Thursday, December 17, 2009 6:48 PM
  • I think that the best help you'll get is to not use threads to spawn SSIS packages from within an SSIS Script Task.  Use parallel precedence constraints to do it instead, or Execute Process Tasks to spawn asynchronous processes.
    Todd McDermid's Blog
    Thursday, December 17, 2009 8:44 PM
    Moderator