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

CLR: arithmetic overflow while insert into a table containing a datetime column

problem: you get an arithmetic overflow error while you want in your CLR stored procedure to insert into a table containing a datetime column
reason: datetime-format on sql serveris set up differently than you use it
workaround:  you can use SET DATEFORMAT before you call you CLR stored procedures.

example:

USE Database
GO

DECLARE    @iReturnvalue int
–set datetime format you want
SET DATEFORMAT ymd;
GO

— call CLR stored procedure
EXEC   [dbo].[sp] @iMandant = 1, @iReturnvalue = @iReturnvalue OUTPUT