TSQL: excute a SSIS/dts package in a stored procedure bypassing parameters

DECLARE @returncode int
DECLARE @cmd varchar(4000)
DECLARE @ImgURL varchar(250)
DECLARE @ImageToolPath varchar(250)
DECLARE @Msg varchar(500)
SET @ImgURL = ‘http://www.webdatenblatt.de/cds/de/?pid=f822e0c1E_’
SET @ImageToolPath = ‘C:\Daten\imgtest’

 

–execute ssis package with parameters

SELECT @cmd = ‘dtexec /f  “C:\SSIS\GetImages.dtsx”‘
+ ‘ /SET \Package.Variables[ImgURL].Properties[Value];’ + @ImgURL
+ ‘ /SET \Package.Variables[ImageToolPath].Properties[Value];’ + @ImageToolPath
EXEC @returncode = xp_cmdshell @cmd
IF @ReturnCode <> 0
BEGIN
SELECT @Msg = ‘SSIS package execution failed GetImages.dtsx on SQL Server\Instance: ‘ + QUOTENAME(@@servername,'”‘)
EXEC msdb.dbo.sp_send_dbmail @profile_name=’SQLServerMail’, @recipients = ‘xy@xy.xy’ , @body = @Msg, @subject = ‘SSIS Execution Failure’
END
ELSE
BEGIN
print ‘image download for import items finished’
END

Advertisements

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