DTS Job Reporting Failures

I have a DTS Job that is reporting failures but it looks to me as if
the job is actually completing successfully. The job only has a
couple steps. Step 1 (DTSStep_DTSExecuteSQLTask_1) Execute SQL Task,
runs a stored procedure to export blobs (pdf files) out of sql server
and onto the local machine.
Here is the code in the storedprocedure called sp_PDFExport
CREATE PROCEDURE [dbo].[sp_PDFExport] AS
begin
set quoted_identifier off

declare @pk int

declare @where_clause varchar(100)

declare @file_name varchar (50)

declare @debug varchar (50)

Declare @cmd varchar (50)

--debug

/*if @Debug = 1
print @cmd
exec Master..xp_cmdShell @cmd */


-- begin cursor

DECLARE LOOKUP CURSOR FOR select pr.[id]
from plan_report pr, plan_version pv
where pv.plan_id = pr.plan_id and pv.status = '30' and pr.create_time
>= pv.update_time and pr.create_time > (Getdate()-1)

OPEN LOOKUP

FETCH NEXT FROM LOOKUP INTO @pk


-- Loop through the list

WHILE @@FETCH_STATUS = 0

BEGIN


SET @where_clause = 'Where' + '[ID]' + '=' + cast(@pk as
varchar(10))


SET @file_name = 'F:\NPPDFs\'+cast(@pk as varchar(10))+'.pdf'

exec sp_textcopy @srvname = '',

@login = 'sa',

@password = '',

@dbname = '',

@tbname = 'Plan_Report',

@colname = 'document',

@filename = @file_name,

@whereclause = @where_clause,

@direction = 'o' -- 'o' for output, 'i' for input

-- loop cursor

SET @pk = NULL

SET @where_clause = NULL

SET @file_name = NULL

FETCH NEXT FROM LOOKUP INTO @pk


END


-- cleanup

CLOSE LOOKUP

DEALLOCATE LOOKUP

end
GO

Then on success of this step I run the following Execute Process Task:
F:\NPMove.bat(DTSStep_DTSCreateProcessTask_1) which runs a batch file
command to move the PDF's from the local machine to our optical
storage. Here is the batch file command:

CD F:
Move /Y F:\NPPDFs\*.* \\Mil-Pegasus-01\Optical\NaviplanOptical001\

I am getting the following info on failure:
Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
Pegasus-01\Optical\NaviplanOptical001\F:\NPPDFs\8562.pdfF:\N PPDFs
\8830.pdfF:\NPPDFs\8869.pdfF:\NPPDFs\8955.pdfF:\NPPDFs\8961. pdfF:
\NPPDFs\8968.pdfF:\NPPDFs\9019.pdfF:\NPPDFs\9023.pdfF:\NPPDF s
\9024.pdfF:\NPPDFs\9025.pdfF:\NPPDFs\9027.pdfF:\NPPDFs\9028. pdfF:
\NPPDFs\9031.pdfF:\NPPDFs\9034.pdfF:\NPPDFs\9036.pdfF:\NPPDF s
\9041.pdfF:\NPPDFs\9042.pdfF:\NPPDFs\9043.pdfF:\NPPDFs\9044. pdfF:
\NPPDFs\9047.pdfF:\NPPDFs\9055.pdfF:\NPPDFs\9056.pdfF:\NPPDF s
\9057.pdfF:\NPPDFs\9058.pdfThe specified network name is no longer
available.The specified network name is no longer available.The
specified network name is no longer available.F:\NPPDFs\9070.pdfF:
\NPPDFs\9073.pdfF:\NPPDFs\9077.pdfF:\NPPDFs\9079.pdfF:\NPPDF s
\9081.pdf 29 file(s) moved. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSCreate... Process Exit Code 1. The step failed.

When I check our optical storage I see the pdf's above have been moved
there, so my issues is what is actually failing? If anyone has any
ideas I would greatly appreciate it :) If step 1 is failing then the
above PDF files would not be there to move so it must be succeeding,
I am just not sure what else to look at.

Re: DTS Job Reporting Failures

Are you logging the dts

Re: DTS Job Reporting Failures

On May 4, 9:18 am, rshivara...@gmail.com wrote:
> Are you logging the dts

yes in the package properties I have logging checked and I am logging
to the sql server that this process is running on milnpprodsql, using
the sa login and password.

Re: DTS Job Reporting Failures

On May 4, 9:23 am, Connie wrote:
> On May 4, 9:18 am, rshivara...@gmail.com wrote:
>
> > Are you logging the dts
>
> yes in the package properties I have logging checked and I am logging
> to the sql server that this process is running on milnpprodsql, using
> the sa login and password.

Where does this log to?? I cannot find a log on the server??

Re: DTS Job Reporting Failures

On May 4, 10:33 am, Connie wrote:
> On May 4, 9:23 am, Connie wrote:
>
> > On May 4, 9:18 am, rshivara...@gmail.com wrote:
>
> > > Are you logging the dts
>
> > yes in the package properties I have logging checked and I am logging
> > to the sql server that this process is running on milnpprodsql, using
> > the sa login and password.
>
> Where does this log to?? I cannot find a log on the server??

go to the dtspackage in enterprise manager\Data transformation Services
\Local Packages and right click the dts and you can find the Package
logs in it.

Re: DTS Job Reporting Failures

On May 4, 10:42 am, rshivara...@gmail.com wrote:
> On May 4, 10:33 am, Connie wrote:
>
> > On May 4, 9:23 am, Connie wrote:
>
> > > On May 4, 9:18 am, rshivara...@gmail.com wrote:
>
> > > > Are you logging the dts
>
> > > yes in the package properties I have logging checked and I am logging
> > > to the sql server that this process is running on milnpprodsql, using
> > > the sa login and password.
>
> > Where does this log to?? I cannot find a log on the server??
>
> go to the dtspackage in enterprise manager\Data transformation Services
> \Local Packages and right click the dts and you can find the Package
> logs in it.

Duh I'm sorry I know those were there but I just didn't make the
connection, bad day! The error doesn't really hellp me much



Step Error Source: Microsoft Data Transformation Services (DTS)
Package
Step Error Description:CreateProcessTask
'DTSTask_DTSCreateProcessTask_1': Process returned code 1, which does
not match the specified SuccessReturnCode of 0.
Step Error code: 80040496
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:4900

This is the step that is moving the PDF files and I can confirm that
it is completing. I selected workflow properties for this task and on
the options tab Use ActiveX Script is selected, since I am not using
any activex script in this DTS job could that be causing the error??

Re: DTS Job Reporting Failures

Connie (csawyer@rwbaird.com) writes:
> I am getting the following info on failure:
> Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
> Pegasus-01\Optical\NaviplanOptical001\F:\NPPDFs\8562.pdfF:\N PPDFs
> \8830.pdfF:\NPPDFs\8869.pdfF:\NPPDFs\8955.pdfF:\NPPDFs\8961. pdfF:
> \NPPDFs\8968.pdfF:\NPPDFs\9019.pdfF:\NPPDFs\9023.pdfF:\NPPDF s
> \9024.pdfF:\NPPDFs\9025.pdfF:\NPPDFs\9027.pdfF:\NPPDFs\9028. pdfF:
> \NPPDFs\9031.pdfF:\NPPDFs\9034.pdfF:\NPPDFs\9036.pdfF:\NPPDF s
> \9041.pdfF:\NPPDFs\9042.pdfF:\NPPDFs\9043.pdfF:\NPPDFs\9044. pdfF:
> \NPPDFs\9047.pdfF:\NPPDFs\9055.pdfF:\NPPDFs\9056.pdfF:\NPPDF s
> \9057.pdfF:\NPPDFs\9058.pdfThe specified network name is no longer
> available.The specified network name is no longer available.The
> specified network name is no longer available.F:\NPPDFs\9070.pdfF:
> \NPPDFs\9073.pdfF:\NPPDFs\9077.pdfF:\NPPDFs\9079.pdfF:\NPPDF s
> \9081.pdf 29 file(s) moved. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSCreate... Process Exit Code 1. The step failed.
>
> When I check our optical storage I see the pdf's above have been moved
> there, so my issues is what is actually failing? If anyone has any
> ideas I would greatly appreciate it :) If step 1 is failing then the
> above PDF files would not be there to move so it must be succeeding,
> I am just not sure what else to look at.
>

I guess it's the "The specified network name is no longer available."
Seems like there was a glitch when the share disappeared for a short
while. If you can verify that all files are where they should be, I guess
you can sleep well.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: DTS Job Reporting Failures

On May 4, 4:40 pm, Erland Sommarskog wrote:
> Connie (csaw...@rwbaird.com) writes:
> > I am getting the following info on failure:
> > Executed as user: US\svcsqlserver. ...Move /Y F:\NPPDFs\*.* \\Mil-
> > Pegasus-01\Optical\NaviplanOptical001\F:\NPPDFs\8562.pdfF:\N PPDFs
> > \8830.pdfF:\NPPDFs\8869.pdfF:\NPPDFs\8955.pdfF:\NPPDFs\8961. pdfF:
> > \NPPDFs\8968.pdfF:\NPPDFs\9019.pdfF:\NPPDFs\9023.pdfF:\NPPDF s
> > \9024.pdfF:\NPPDFs\9025.pdfF:\NPPDFs\9027.pdfF:\NPPDFs\9028. pdfF:
> > \NPPDFs\9031.pdfF:\NPPDFs\9034.pdfF:\NPPDFs\9036.pdfF:\NPPDF s
> > \9041.pdfF:\NPPDFs\9042.pdfF:\NPPDFs\9043.pdfF:\NPPDFs\9044. pdfF:
> > \NPPDFs\9047.pdfF:\NPPDFs\9055.pdfF:\NPPDFs\9056.pdfF:\NPPDF s
> > \9057.pdfF:\NPPDFs\9058.pdfThe specified network name is no longer
> > available.The specified network name is no longer available.The
> > specified network name is no longer available.F:\NPPDFs\9070.pdfF:
> > \NPPDFs\9073.pdfF:\NPPDFs\9077.pdfF:\NPPDFs\9079.pdfF:\NPPDF s
> > \9081.pdf 29 file(s) moved. DTSRun: Loading... DTSRun:
> > Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> > OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> > DTSStep_DTSCreate... Process Exit Code 1. The step failed.
>
> > When I check our optical storage I see the pdf's above have been moved
> > there, so my issues is what is actually failing? If anyone has any
> > ideas I would greatly appreciate it :) If step 1 is failing then the
> > above PDF files would not be there to move so it must be succeeding,
> > I am just not sure what else to look at.
>
> I guess it's the "The specified network name is no longer available."
> Seems like there was a glitch when the share disappeared for a short
> while. If you can verify that all files are where they should be, I guess
> you can sleep well.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks Erland I was leaning towards a network issue of some sort I
appreciate the verification.