Tag: VB.NET

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL 2005: SSIS – Pushing Data to MySQL using Script Component Destination

Sometimes, I just wonder why things that are useful in previous versions of things get removed. In SQL 2000, in DTS, there was an ODBC destination, you could write (insert, update, etc) to a foreign (non MSFT SQL) system easily. DB2 – no prob, MySQL – heck yea. For whatever reason, in SQL 2005 Integration Services (SSIS), that ability was removed (in SQL 2008 SSIS there is an ADO.NET Destination that can update ODBC sources, so they brought some functionality back).

I need to write to a MySQL database pulling data from a SQL Database, using SSIS 2005. What are the options? Well, the best I could come up with was a Script Component Destination in my DataFlow, this is how I did it:

1) Create a new SSIS Package, throw a DataFlow on the Control Flow, Add your connections (let’s say SourceDB_SQL – your source data, and then DestDB_MySQL – your MySQL destination, it needs to be a ADO.NET Connection, you need to install the MySQL connection – I installed this http://dev.mysql.com/downloads/connector/odbc/5.1.html)

2) In your DataFlow, create your OLEDB Source and get your query returning data, throw a Script Component on the Data Flow and make it a Destination.

ms_01

3) Open the Script Component, set your input columns up, and then your Connection Manager

ms_02

4) Open the actual script, and you just have to add a few lines of code:

' Microsoft SQL Server Integration Services user script component
' This is your new script component in Microsoft Visual Basic .NET
' ScriptMain is the entrypoint class for script components

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc

Public Class ScriptMain
    Inherits UserComponent

    Dim mySQLConn As OdbcConnection
    Dim sqlCmd As OdbcCommand

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
        mySQLConn = CType(Me.Connections.MySQLDatabase.AcquireConnection(Nothing), OdbcConnection)
    End Sub

    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        sqlCmd = New OdbcCommand("INSERT INTO steve_test(ShipTo, YearValue) VALUES(" & Row.ShipTo & ", '" & Row.YearValue & "')", mySQLConn)
        sqlCmd.ExecuteNonQuery()

    End Sub

    Public Overrides Sub ReleaseConnections()
        Me.Connections.MySQLDatabase.ReleaseConnection(mySQLConn)
    End Sub
End Class

Run it and you are done! Easy. Now you can write data into MySQL using SQL 2005 SSIS (or any ODBC destination if you can get it to work :))

Categories
Geeky/Programming

VBA vs VB.NET – turn bytes into bitmaps

So, recently working on some things, I have noticed the HUGE difference between VBA and VB.NET, specifically with turning bytes into bitmaps.. (assume GiveMeBytes() returns a byte array that is a bitmap)

VB.NET:

Dim image As Byte() = GiveMeBytes()
Dim memStream As MemoryStream = New MemoryStream(image)
Dim bitImage As Bitmap = New Bitmap(System.Drawing.Image.FromStream(memStream))

bitImage.Save(“C:test.bmp”)

 

VBA:

Dim image() As Byte
image = GiveMeBytes()

Dim bitImage

bitImage = FreeFile
Open “c:test.bmp” For Binary Access Write As bitImage
Put #bitImage, , image()
Close #bitImage

Now, maybe there is an easier way in VBA/VB6 to do it, but this is the way I learned way back in the day. I am sure you can do something with the FileSystemObject (I am guessing)..

You notice that the VB.NET snippet mostly deals with converting bytes to MemoryStream to Bitmap, and the saving is 1 line, whereas the VBA is really nothing with converting but mostly deals with saving the file. Can we get the best of both worlds? I am not sure, but I still like the .NET implementation better, it just seems “cleaner” to me, and the VBA just seems “dirty”, but they both do the same thing.. (notice in the VBA I didn’t strongly type the bitImage Dim)

In conclusion…”viva .NET!” 🙂

Technorati tags: , , , , , , , , ,
Categories
Geeky/Programming

How To Make Your Own Syslog Sever in VB.NET

In networks all over, many devices can send Syslogs to a syslog server. You can download Syslog Servers (like Kiwi) to capture and process the syslogs, or you can create your own server to catch all the syslogs on your network. Then you can parse them to a database and write your own reports of them, having full control of everything.

First, in VB.Net, you need to import some namespaces.

Imports System.IO
Imports System.Net.Sockets
Imports System.Net
Imports System.Text

Then, from you Main procedure, call a procedure called ListenForSyslogs

Private Sub ListenForSyslogs()

Dim ipeRemoteIpEndPoint As New IPEndPoint(IPAddress.Any, 0)
Dim udpcUDPClient As New UdpClient(514)
Dim sDataRecieve As String
Dim bBytesRecieved() As Byte
Dim sFromIP As String

Try
While True
bBytesRecieved = udpcUDPClient.Receive(ipeRemoteIpEndPoint)
sDataRecieve = Encoding.ASCII.GetString(bBytesRecieved)
sFromIP = ipeRemoteIpEndPoint.Address.ToString

FillLog(sDataRecieve, sFromIP)

Console.WriteLine(sDataRecieve)
sDataRecieve = ""
End While
Catch e As Exception
' just ignore for now
End Try
End Sub

If you analyze this code, it just sets up a endpoint on the IP you are running the program, and listens on port 514, the default syslog port. It will just run and run, and keep listening. Whenever you recieve data, then call FillLog procedure

Private Sub FillLog(ByVal sSyslog As String, ByVal sFromIp As String)

Dim sPriority As String
Dim sPath As String = System.Environment.CurrentDirectory & "Unprocessed"

sSyslog = sSyslog.Replace(vbCrLf, "")
sSyslog = Mid(sSyslog, InStr(sSyslog, ">") + 1, Len(sSyslog))
sSyslog = Trim(sSyslog)

sPriority = GetSyslogPriority(sSyslog)

Dim swWriter As New StreamWriter(sPath & "syslog" & Now.Month & Now.Day & Now.Year & Now.Minute & ".txt", True)
swWriter.WriteLine(sFromIp & "," & Now & "," & sPriority & "," & sSyslog)
swWriter.Flush()
swWriter.Close()

End Sub

What FillLog does it look at data recieved, and parses it out, removing line feeds, etc.

Then it gets the priority from a function GetSyslogPriority(). Then it writes out the info to a comma seperated txt file (for easy parsing later), that is named pathsyslogmonthdayyearminute.txt so for example, c:unprocessedsyslog0408200529.txt

It will append to that txt for for the minute it gets syslogs for. So you should probably have another process that will consume that txt file before the next hour rolls around.

Finally, the function that gets the priority:

Private Function GetSyslogPriority(ByVal sSyslog As String) As String
Dim sResult As String

If InStr(sSyslog, "-0-") Then
sResult = "Emergency (0)"
End If

If InStr(sSyslog, "-1-") Then
sResult = "Alert (1)"
End If

If InStr(sSyslog, "-2-") Then
sResult = "Critical (2)"
End If

If InStr(sSyslog, "-3-") Then
sResult = "Error (3)"
End If

If InStr(sSyslog, "-4-") Then
sResult = "Warning (4)"
End If

If InStr(sSyslog, "-5-") Then
sResult = "Notice (5)"
End If

If InStr(sSyslog, "-6-") Then
sResult = "Info (6)"
End If

If InStr(sSyslog, "-7-") Then
sResult = "Debug (7)"
End If

If sResult = "" Then
sResult = "UNKNOWN"
End If
Return sResult

End Function

To summarize, you can capture syslogs from your network to text files, and then create another program to read in the text files to a database and write reports. Creating the UDP listener on port 514, you can setup your network devices to dump syslogs to your box where you are running the syslog server you created. To troubleshoot network issues, syslogs will give you a good idea of what is getting denied, etc, and you can create your own homegrown Syslog Server using VB.NET in a few simple steps.

Enjoy!