• How do I execute Integration Services ( SSIS ) package using a Script task? How do I call one SSIS package from another one?

• How do I write to Event Logs (Application Log, System Log etc.) in Integration Services ( SSIS )? How do I log events to the Application/System Log in SSIS?

How do I execute Integration Services ( SSIS ) package using a Script task? How do I call one SSIS package from another one?

You could execute one ssis package from another one using either Execute Package OR Execute Process (with dtexec.exe) tasks. But I prefer using a Script Task to achieve the same for two reasons. First one is, easy readability, you can see clearly what values you are actually passing to the package, unlike the other two tasks where you have to specify them in a long, tortuous string, specially when you have many variables to pass to the child package. The other reason is that Execute Package and Execute Process tasks fail intermittently!! yep, that's true. This observation was initially made by Sutha Thiru, an SSIS/ETL expert, when we were working together on a project at Edenbrook and has been brought to the attension of SSIS team at Microsoft. This is true as of SQL Server 2005 sp2.

Here's how you would use a script task to call another package

Add a script task to your package, specify the required variables in the script tab, select Design Script button to open Microsoft Visual Studio for Applications Editor, replace Public Sub Main()....End Sub with the following code

    Public Sub Main()

        Dim pkg As String = "C:\temp\InvokedFromScript.dtsx"   'Dts.Variables("ChildPackagePath").Value.ToString
        Dim app As Application = New Application()
        Dim p As Package = app.LoadPackage(pkg, Nothing)  'Load child Package

        Try
             'Pass the required variables and execute the child package
            p.Variables("ServerName").Value = Dts.Variables("ServerName").Value.ToString
            p.Variables("EnviromentName").Value = Dts.Variables("EnviromentName").Value.ToString
            p.Execute()

        Catch ex As Exception
            p = Nothing
            app = Nothing
            Dts.TaskResult = Dts.Results.Failure
            Exit Sub
        End Try

        p = Nothing
        app = Nothing
        Dts.TaskResult = Dts.Results.Success
    End Sub

Top

How do I write to Event Logs (Application Log, System Log etc.) in Integration Services ( SSIS )? How do I log/raise events to the Application/System Log in SSIS?

Here's how you would use a script task to raise events to Event logs in SSIS

Add a script task to your package, specify the required variables in the script tab, select Design Script button to open Microsoft Visual Studio for Applications Editor, replace existing code with the following code

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Diagnostics

    Public Class ScriptMain

        Public Sub Main()
		'RETURNS True if successful, false if not
        Dim blnSuccess As Boolean = WriteToEventLog("hello log", "my SSIS App", EventLogEntryType.Error, "Application")  
        Dts.TaskResult = Dts.Results.Success
        End Sub

        Public Function WriteToEventLog(ByVal Entry As String, _ 
       Optional ByVal AppName As String = "SSIS Application", _
       Optional ByVal EntryType As EventLogEntryType = EventLogEntryType.Error, _
       Optional ByVal LogName As String = "Application1") As Boolean 
	   
        Dim objEventLog As New EventLog()

        Try
            'Register the Application as an Event Source
            If Not objEventLog.SourceExists(AppName) Then
                objEventLog.CreateEventSource(AppName, LogName)
            End If
            objEventLog.Source = AppName
            'WriteEntry is overloaded; this is one of 10 ways to call it
            objEventLog.WriteEntry(Entry, EntryType)
            Return True
        Catch Ex As Exception
            Return False
        End Try
        End Function

    End Class

'WriteToEventLog function Parameters: 
'			 Entry - Value to Write
'            AppName - Name of Client Application. 
'            EntryType - EventLogEntryType.Warning,EventLogEntryType.Error,Warning,EventLogEntryType.Information etc.
'            LogName: Name of Log (System, Application;Security is read-only)

Top