How to Run SSIS package from SQL Server 2000 Stored Proc
-
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
-
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/
-
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).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply