I can provide you some tried & tested snippet to call a SSIS Package from SP. You may have to enable the command shell scripting as shown in the snippet since in SQl2008 it is disabled by default.

filePath & filename are user variables in the SSIS package, on which the package is dependendant on.

You could try calling a SSRS on similar lines if you haven't yet got what you were looking for.

Cheers !

declare @cmd varchar(1250)

declare @ssispath varchar(1250)

declare @filePath varchar(1250)

declare @fileName varchar(1250)

set @ssispath = 'C:\SAPImport\AutomatedZ23Processing.dtsx'

set @filePath = 'C:\ExportFileFolder\\'

set @fileName = 'test.Xlsx'

select @cmd = 'dtexec /F "' + @ssispath + '"'

select @cmd = @cmd + ' /SET \Package.Variables[User::filePath].Properties[Value];"' + @filePath + '"'

select @cmd = @cmd + ' /SET \Package.Variables[User::fileName].Properties[Value];"' + @fileName + '"'

EXEC sp_configure 'xp_cmdshell', '1' --- Enable Command Shell

RECONFIGURE

exec master..xp_cmdshell @cmd

EXEC sp_configure 'xp_cmdshell', '0' --- Disable Command Shell

RECONFIGURE