Output Results Using Execute Process Task With SSIS

Recently in my mysql blog http://kensmysqlblog.blogspot.com/2008/10/pumping-out-warnings-when-using-load.html I talked about how to display warnings when working with mysql from the command line. This was all good from a prompt, however when working in SSIS these warnings are invisible to the user, and in effect go without notice. Whether it’s mysql or any other command executable running under the execute process task in SSIS you often need to capture output. Here’s the easiest method I’ve found.


Step 1
Create a variable global variable as a string type leaving the value empty. In this instance, it’s called mysqlwarnings.



Step 2
Put your command and arguments appropriately in the execute process task properties Once done point to the variable created in Step 1 (mysqlwarnings). For those concerned about the mysql portion I’m generating a warning by inserting a datetime value into a date field. I run the “Show warnings” to be captured to the output variable

insert into datetest values('2008-01-01 11:00:00');show warnings;"



Once the variable has been given the output the last thing to do is write it to a text file using a script task. The code should look like this. Remember to set imports system.io to get this to work.

Public Sub Main()
Dim sw As New StreamWriter("c:\warningsout.txt")
sw.Write(Dts.Variables("mysqlwarnings").Value.ToString())
sw.Close
Dts.TaskResult = Dts.Results.Success
End Sub

See my previous blog where I show how to fail a package based on data in a file.