SSIS: download picture via HTTP

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

<System.AddIn.AddIn(“ScriptMain”, Version:=”1.0″, Publisher:=””, Description:=””)> _ <System.CLSCompliantAttribute(False)> _

Partial Public Class ScriptMain

Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

Enum ScriptResults

Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

End Enum

Public Sub Main()

Dim myWebClient As WebClient
Dim fireagain As Boolean
Dim cmd As String
Dim con As SqlClient.SqlConnection

con = DirectCast(Dts.Connections(“(local).OLReweAbf”).AcquireConnection(Dts.Transaction), SqlClient.SqlConnection)

‘MsgBox(con.ConnectionString, MsgBoxStyle.Information, “ADO.NET Connection”)

cmd = “Select * from CustomItem”

Dim oDS As New DataSet
Dim oDA As New SqlDataAdapter(cmd, con)
Dim path As String = CStr(Dts.Variables(“Bildpfad”).Value)
Dim filename As String

Try

Dts.Log(“Custom: Start picturedownload per HTTP”, 999, Nothing)

oDA.Fill(oDS)

For Each Row As DataRow In oDS.Tables(0).Rows

If Not IsDBNull(Row(“pictureURL”)) And Row(“pictureURL”).ToString.Trim <> String.Empty Then

filename = path & “\” & IIf(IsDBNull(Row(“EAN”)), Row(“ARTNR”).ToString, Row(“EAN”).ToString) & “.jpg”

If Not System.IO.File.Exists(filename) Then

myWebClient = New WebClient()

‘ Ereignismeldung senden (nur zur Information des Anwenders)

Dts.Events.FireInformation(0, String.Empty, String.Format _

(“Downloading ‘{0}’ to ‘{1}'”, Row(“pictureURL”), filename), _

String.Empty, 0, fireagain)

‘ Download durchführen

myWebClient.DownloadFile(Row(“pictureURL”), filename)

myWebClient = Nothing

End If

End If

Next

Dts.TaskResult = ScriptResults.Success

Catch ex As Exception

Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)

Dts.TaskResult = ScriptResults.Failure

End Try

End Sub

End Class

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s