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..
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/