SQL server agent running dtsx package

MInendra
MInendra used Ask the Experts™
on
I have a SQL server 2005 in server and 2000 locally. I want to transfer the records from the many table of many databases over night from server to local.
I want to create dtsx package saved in SQL server and run on the desired time. Advantage is i can stop any particular one when ever i want. The packages are executing by SQL Server Agent.
 
I am worried if it would slow down or cause any kind of problem/load on SQL server? Can i run many dtsx package at the same time? Also how can i delete the dtsx package that i already have created. I know it lives in msdb DB. how do i delete ?  I am shaky to touch MSDB
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I am worried if it would slow down or cause any kind of problem/load on SQL server?

Depends upon the volume of data you are going to transfer. And based on the query you are going to use to identify the records that needs to be moved.

>> Can i run many dtsx package at the same time?

Yes.. As mentioned earlier it depends.

>> Also how can i delete the dtsx package that i already have created. I know it lives in msdb DB. how do i delete ?

Use sp_drop_dtspackage to drop dts packages like

sp_drop_dtspackage ur_dts_package_name, ur_dts_package_id, version_id

Author

Commented:
sp_drop_dtspackage ur_dts_package_name, ur_dts_package_id, version_id

Running this where do i get dts_package_id and also version_id ?

Also another problem is when i create dtsx and saved as a file format, then open it and execute then it workls fine. But when i call this dtsx package from Agent then it generates the error. Also doing same thing and saving in SQL sever and calling from agent generates the error as well.

Aslo where can i vew the error ?

Thanks
 
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
There are a whole lot of built in procedures used to control Agent jobs and request you to go through all procedures listed here:

http://msdn.microsoft.com/en-us/library/ms187763.aspx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial