Post reply

SSIS Execute Process Task Standard Output/Error variables

  • September 2, 2008 at 9:09 am

    #154684

    I keep waiting for my SSIS light bulb to go on, but the more I try to work with it the worse it seems to me. My latest questions:

    1.I have an Execute Process Task to run a command line FTP session (because MS refuses to fix the horrid bug that keeps their FTP from working with IBM mainframes). It works (finally), and it populates the StandardOutputVariable and StandardErrorVariable with the expected contents. But my (dumb) question is now, how do I take those variables and append them to some text log files?

    2.My second dumb question is: why do I have to execute the entire package to use the debugger to expose the variables? When I simply execute the task (right click) the debugger will stop on the OnPostExecute event, but not show any locals.

    3. How do I clear the Execution Results page? It seems to start fresh after a successful run, but while I am getting failures it gets junked up with all my failed attempts?

  • September 3, 2008 at 5:16 am

    #866416

    Anybody?

  • September 4, 2008 at 3:06 am

    #867003

    Jim Russell (9/2/2008)

    1.I have an Execute Process Task to run a command line FTP session (because MS refuses to fix the horrid bug that keeps their FTP from working with IBM mainframes). It works (finally), and it populates the StandardOutputVariable and StandardErrorVariable with the expected contents. But my (dumb) question is now, how do I take those variables and append them to some text log files?

    Use ScriptTask:

    Imports System

    Imports System.IO

    Imports Microsoft.SqlServer.Dts.Runtime

    Public Class ScriptMain

    Public Sub Main()

    Dts.TaskResult = Dts.Results.Success

    Try

    If Not File.Exists(Dts.Variables("LogFile").Value.ToString()) Then

    File.Create(Dts.Variables("LogFile").Value.ToString()).Close()

    End If

    Dim writer As New StreamWriter(Dts.Variables("LogFile").Value.ToString(), True)

    writer.WriteLine(Date.Now.ToLongTimeString() + " " + Dts.Variables("var").Value.ToString())

    writer.Close()

    Catch ex As Exception

    Dts.TaskResult = Dts.Results.Failure

    System.Windows.Forms.MessageBox.Show(ex.Message)

    End Try

    End Sub

    End Class

    Don't forget to add variables used in the Script to ReadOnlyVariables property (comma separated, on the same page where you open script designer).

    Jim Russell (9/2/2008)

    2.My second dumb question is: why do I have to execute the entire package to use the debugger to expose the variables? When I simply execute the task (right click) the debugger will stop on the OnPostExecute event, but not show any locals.

    Disable all tasks except those you want to debug (right click -> disable) and then start debugging.

  • September 4, 2008 at 4:18 am

    #867027

    trueboar: Thanks very much! That helps.

  • October 19, 2008 at 12:10 am

    #886841

    Thank you very much for posting this great solution. Exactly what I was looking for! Another alternative is posted here: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

  • April 2, 2009 at 10:00 am

    #970871

    Hi Jim,

    Can you explain how did you write the ftp task using execute process task?

    Thanks,

    Pankaj

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply