DTS 2000

Hi,

I am having a dts 2000 package which is accessing oracle source and loading data into sql server .The package runs fine when ran individually or from designer.

But when it is scheduled into a job, the job is being shown as ran successfully whereas the package is not run or for sure the data is not loaded.

I looked into microsoft articles regarding this problem and found that this job will be run under the profile who started the SQL Server Agent.There is another job which is running properly whose owner is xxx. So i changed the owner of my job into xxx.

But still the same condition is prevailing, job is being shown as ran successfully while the data is not loaded.

Kindly help me on this.

Thanks and Regards

Arobind

Are you executing this DTS package via SSIS? If not, you're in the wrong forum as this is an SSIS forum. The DTS forum can be found here: http://groups.google.com/group/microsoft.public.sqlserver.dts/topics?lnk=srg

If in SSIS, make sure that you set the FailParentOnFailure to true.|||

The fact is that, job history is showing as job ran successfully.When i check the details also, package step got executed successfully message is coming.

|||

Then your problem is within SQL Server Agent...the Job is being reported as Successfully run but the results indicate it actully does not run....

|||

Arobind Balakrishnan wrote:

The fact is that, job history is showing as job ran successfully.When i check the details also, package step got executed successfully message is coming.

Again, is this an SSIS package, or an older DTS package? This forum can help you only if it's an SSIS package.|||

this is a dts package only.Sorry to put it in SSIS as i didnt find any dts forum in msdn forum site.

|||

Arobind Balakrishnan wrote:

this is a dts package only.Sorry to put it in SSIS as i didnt find any dts forum in msdn forum site.

Nope, I'm not sure that they even had MSDN forums when DTS was released. So they created a USENET newsgroup for DTS, which is the link I provided above. They felt it was important to segregate the two products.

I could be wrong on some of those points though.

DTS - Execute Package Task Problem

I have five DTS packages on a server.

Instead of executing these DTS packages individually, I have decided to create a 'parent' DTS package that will fire off each of the five 'child' DTS packages using 'On Success' precedences.

This seemed like a pretty straight forward task, but when one of the child DTS packages fails, the 'parent' DTS package continues to execute the next 'child' DTS package through the 'On Success' precedence. I was expecting the 'parent' DTS to fire off an 'on failure' precendence and stop executing

Can anyone tell me why the 'parent' DTS package continues with the 'on success' precedence after one of the 'child' DTS packages has failed.

Thanks in advance.Hi Krispy

I can't answer your question directly however I tend to use the below to execute my DTSs from code. I now include about as little as possible in my DTSs (the transformation only in fact) and wrap everything else up in a sproc.
So - the long and the short of my answer would be - perhaps look at replacing your masater DTS with some T-SQL code - makes trouble shooting and execution flow much easier IMHO.
http://www.pengoworks.com/index.cfm?action=articles:spExecuteDTS

HTH|||Thanks for the swift response Pootle Flump - much appreciated.

I did a bit of digging and found this in the SQL Server 2005 books online. I probably should have said that we are running SQL Server 2000.

Alternatively, sometimes you want the parent and child packages to fail together as one unit, or you might not want to incur the additional overhead of another process. For example, if a child process fails and subsequent processing in the parent process of the package depends on success of the child process, the child package should run in the process of the parent package.

By default, the ExecuteOutOfProcess property of the Execute Package task is set to False, and the child package runs in the same process as the parent package. If you set this property to True, you can debug only packages that contain limited functionality. To debug all packages supported by your edition of SQL Server 2005, you must install Integration Services.

Does anyone know if it is possible to set the ExecuteOutOfProcess property on SQL Server 2000, or is this specific to SQL Server 2005?