We are not allowed to use xp_cmdshell for security reasons.
I currently extract data (real-time) from Oracle tables in my sql stored procedures using linked servers.
I was told that I might be able to improve performance by using ssis packages to to do this.
Below is a example on how I do this :
/****** Object: StoredProcedure [NonFreight].[procRptLstTeam] Script Date: 10/23/2012 06:48:30 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [NonFreight].[procRptLstTeam]
SET NOCOUNT ON;
DECLARE @sql VARCHAR(MAX)
--Build #Team Sql table
CREATE TABLE [NonFreight].[#Team]
SET @Sql = 'SELECT DISTINCT C.TEAM as TeamCode, C.TEAM as TeamName
FROM GPCOMP1.GPCOLL C'
SET @Sql = N'INSERT INTO [NonFreight].[#Team]
SELECT * from openquery
([GPNFE], ''' + Replace(@Sql, '''', '''''') + ''')'
-- Dropdown query
Count(*) OVER(PARTITION BY NULL) AS TeamCount
WHERE TeamName != ''
ORDER BY TeamName
Is there another way I could execute a ssis package with parms via my sql stored procedure in place of xp_cmdshell ?
My stored procedures can be called by many users at the same time through out the day.
Also, I am not sure if you can call a ssis package via a sql stored procedure and create temp tables that can be then used in the calling sql stored procedure.