Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Run SSIS package from SQL Server 2000 Stored Proc Expand / Collapse
Author
Message
Posted Wednesday, March 25, 2009 11:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 12, 2010 7:43 AM
Points: 15, Visits: 73
Hi,

Currently we have a stored procedure (SQL Server 2000 (32 bit)) that calls a DTS package which processes an MSAS 2000 Cube Dimensions. This Cube is moved to SSAS2005 on a new server.

An SSIS package is created on the new server(64bit). I want to call this package from the stored procedure in the old server (SQL SErver 2000 - 32 bit).

Is this doable?? Can anyone help on this??

I tried executing the DTSX package using xp_cmdshell and dtsexec utility. But DTSExec is not recognized. I guess the utility is not available. Following is the command that I tried...

master..xp_cmdshell 'dtsexec /f "\\servername\C$\Documents and Settings\....\My Documents\Visual Studio 2005\Projects\Integration Services Project1\Integration Services Project1\ProcessCPCubeDims.dtsx"'

Any suggestion/solution would be of great help..

Thanks,
Ganesvari S
Post #683849
Posted Monday, March 30, 2009 7:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:52 AM
Points: 1,114, Visits: 2,191
GGKK (3/25/2009)
Hi,

Currently we have a stored procedure (SQL Server 2000 (32 bit)) that calls a DTS package which processes an MSAS 2000 Cube Dimensions. This Cube is moved to SSAS2005 on a new server.

An SSIS package is created on the new server(64bit). I want to call this package from the stored procedure in the old server (SQL SErver 2000 - 32 bit).

Is this doable?? Can anyone help on this??

I tried executing the DTSX package using xp_cmdshell and dtsexec utility. But DTSExec is not recognized. I guess the utility is not available. Following is the command that I tried...

master..xp_cmdshell 'dtsexec /f "\\servername\C$\Documents and Settings\....\My Documents\Visual Studio 2005\Projects\Integration Services Project1\Integration Services Project1\ProcessCPCubeDims.dtsx"'

Any suggestion/solution would be of great help..

Thanks,
Ganesvari S


You have to use DTEXEC (not DTSEXEC which is for old DTS packages) to execute SSIS packages. You have to find a way to execute and RUN the package on the remote server. You will not be able to execute the SSIS package and use the resources of the old SQL 2000 server without first installing SQL 2005 + Integration Services.


---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #686107
Posted Thursday, April 02, 2009 8:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 12, 2010 7:43 AM
Points: 15, Visits: 73
Thanks for the reply. Yeah, I am now using DTExec. Following is the progress on this:

I have the dtsx file and the batch file that calls this dtsx in server1 (that has ssas 2005 and is on 64-bit). Calling this batch using WMIC command from server 2 (SQLServer 2000).

Still have to explore on how to get the DTExec's execution result. As what WMIC does is just spawns the process on server1 and returns with success code and giving the process id.

Any other suggestions/way of doing this.

All I want is, Server 2 does not have the DTExec utility and would want to run the package in Server 1 from the Server 2(with an sql procedure).
Post #689012
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse