October 23, 2012 at 6:04 am
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 :
USE [RMReports]
GO
/****** Object: StoredProcedure [NonFreight].[procRptLstTeam] Script Date: 10/23/2012 06:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [NonFreight].[procRptLstTeam]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR(MAX)
--Build #Team Sql table
CREATE TABLE [NonFreight].[#Team]
(
[TeamCode] VARCHAR(10),
[TeamName] VARCHAR(30)
)
ON [PRIMARY]
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, '''', '''''') + ''')'
EXEC (@Sql)
-- Dropdown query
SELECT TeamCode,
TeamName,
Count(*) OVER(PARTITION BY NULL) AS TeamCount
FROM NonFreight.#Team
WHERE TeamName != ''
ORDER BY TeamName
END
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.
October 23, 2012 at 6:20 am
See this: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/cf3a9b8c-90f2-410c-978b-c1d995f7e0f9
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 25, 2012 at 5:48 am
Thanks.
For right now, I will continue with the way I am doing it now.
October 25, 2012 at 10:05 pm
terry hayduck (10/25/2012)
Thanks.For right now, I will continue with the way I am doing it now.
Not that it matters but I agree. I'm not so sure that an SSIS package would be any faster than the OPENQUERY method you used. You also don't have to mess around with an SSIS package this way.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply