SQL Agent debugging

17 Feb 2006 4:08 PM

Ken Abe

I'm connecting to SQL Server 2005 using Management Studio. I copied a table
to another server using the "Export Data" facility and opted to save the SSIS
package on my server. Then, I created a Job that runs the package as its only
step. The job fails, but neither Job History nor the SQLAgent log file give
me any useful information as to why it failed. Where do I have to look?

17 Feb 2006 10:30 PM

Erland Sommarskog

Ken Abe (Ken***@discussions.microsoft.com) writes:

> I'm connecting to SQL Server 2005 using Management Studio. I copied a
> table to another server using the "Export Data" facility and opted to
> save the SSIS package on my server. Then, I created a Job that runs the
> package as its only step. The job fails, but neither Job History nor the
> SQLAgent log file give me any useful information as to why it failed.
> Where do I have to look?

I have not tried Import/Export data, but I played with the Copy
Database Wizard, and by default it logs in the Windows event log. I
always redirect to a text file. Just recently I tried for the first
time to copy from one server to another, and found that the log file
was on the target server, despite I had stared CDW on the source server.

Anyway, check Windows event log on both servers for clues.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

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

9 May 2006 1:52 PM

Ken Abe

I check Windows event log and all I get is "Package 'my package' failed".

Show quote

"Erland Sommarskog" wrote:

> Ken Abe (Ken***@discussions.microsoft.com) writes:
> > I'm connecting to SQL Server 2005 using Management Studio. I copied a
> > table to another server using the "Export Data" facility and opted to
> > save the SSIS package on my server. Then, I created a Job that runs the
> > package as its only step. The job fails, but neither Job History nor the
> > SQLAgent log file give me any useful information as to why it failed.
> > Where do I have to look?
>
> I have not tried Import/Export data, but I played with the Copy
> Database Wizard, and by default it logs in the Windows event log. I
> always redirect to a text file. Just recently I tried for the first
> time to copy from one server to another, and found that the log file
> was on the target server, despite I had stared CDW on the source server.
>
> Anyway, check Windows event log on both servers for clues.
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

9 May 2006 10:10 PM

Erland Sommarskog

Ken Abe (Ken***@discussions.microsoft.com) writes:

> I check Windows event log and all I get is "Package 'my package' failed".

I played around with it, and I found that if I ran the package
immediately, I got an error and when I clicked on it, I got the real
error message.

But when I ran it as a job, you are right there is no message at all
be found, in the realms that I know.

I found that when you have a job step which is an SSIS package, there
is a tab Logging. I tried to use something that looked like text file,
and filled in a file name, but no luck.

I guess that if one wants to take this road, one first needs to learn
the basics about SQL Server Integration Services - and I haven't done
that.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

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