SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Run SSIS package from SQL Server 2000 Stored Proc


How to Run SSIS package from SQL Server 2000 Stored Proc

Author
Message
GGKK
GGKK
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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
CozyRoc
CozyRoc
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2502 Visits: 2235
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/


GGKK
GGKK
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search