Saving package results in a table

[SQLHacker]

I'm using SQL Server 2005, SP2, and SSIS as the package designer. I have (thanks to a script I found on the internet) created a script package that looks at the Event Viewer logs on a designated machine. It executes fine, but I need to store the results in a table somewhere. In fact, I want to modify this script to look at several servers at the same time, and report the results back to one table in the database. I'm VERY new to SSIS and am finding it particularly finiky and frustrating! (Where did DTS go...?).

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


Public Class ScriptMain

	' The execution engine calls this method when the task executes.
	' To access the object model, use the Dts object. Connections, variables, events,
	' and logging features are available as static members of the Dts class.
	' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
	' 
	' To open Code and Text Editor Help, press F1.
	' To open Object Browser, press Ctrl+Alt+J.
    Public Shared Function GetEventLogs(ByVal machineName As String) As EventLog()

        Dim returnValue As EventLog()

        returnValue = EventLog.GetEventLogs(machineName)

    End Function

    Public Sub Main()

        Dim remoteEventLogs() As EventLog

        remoteEventLogs = EventLog.GetEventLogs("myserver")

        Console.WriteLine(("Number of logs on computer: " & remoteEventLogs.Length))

        Dim log As EventLog
        For Each log In remoteEventLogs
            Console.WriteLine(("Log: " & log.Log))
        Next log

        Dts.TaskResult = Dts.Results.Success

    End Sub




End Class

 

[MDXer]

you will have to utilize this script as a datasource within a Dataflow. You need to manually add your output columns and modify the script to output these values to the appropriate columns. to save the data to a table you simply need to create the table on your server and utilize and OLE_DB Destination to load into the table. Multiple servers can be done either through looping or by copying the process.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SQLHacker]

MDXer,

Thanks for your response. As I mentioned, I'm very new to SSIS, so most of your response doesn't mean much to me.

Can you explain what a dataflow is? I looked in Books Online, but didn't find anything. I think I get the part about manually adding the output columns, and I assume that the datatype is an important part of that configuration (at least it would be important when it gets to the database).

Thanks again for your help.

 

[MDXer]

SSIS is broken up into three primary units fo work.

1) Control flow - the control flow does is more of the management layer. No data, as in records flow from task to task here. Process execution, execution order and mailing notifications are the primary tasks here. A package has a single control flow.

2) Data flow - The data flow is where you actually move and modify the data between servers, databases and tables. Data flows are typically comprised of a Source and Destination with the various other tasks in between these 2 in order to achieve the desired output. A package can have 0 or more dataflows.

3) Event Handlers - These allow a user to specify what to do when various process events fired with the most popular probably being the OnError. A package has eventhadlers at the package and sub component level.

Example

I have Package A

Control Flow is as flollows

Stored procedure starts an audit record On Success it executes a Dataflow A upon successful completion of Data flow a an email is sent.

Data flow A has Ole DB Source 1 which inputs into a multicast the multicast ouputs to 4 OLE DB destinations.

Event handler has a task to send email On Error at the package level.

Hope this helps some.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck