DTS Question

I am using SQL Server 2000 and I have a created a DTS package that
copies Data from a Excel Spreasheet to a SQL Server table then executes
a user Stored Proc
What I am wanting to do is if for some reason the Stored Proc errors
out because of bad data , I would like to see the errors reported in a
file so that it can be reviewed and I was not sure if there is a way to
output the errors in DTS. When I run the Stored Proc theough Query
Analyzer I see the errors in Query Analyzer, I basically want to see
the same information after running the DTS. Can this be done, if so
how.
Any help in this regard is greatly appreciated.
ThanksI would use the DTSRUN command to execute the DTS package. On the DTSRUN
command you can use the /L option to create a log froma DTS package.
"shub" wrote:
> I am using SQL Server 2000 and I have a created a DTS package that
> copies Data from a Excel Spreasheet to a SQL Server table then executes
> a user Stored Proc
> What I am wanting to do is if for some reason the Stored Proc errors
> out because of bad data , I would like to see the errors reported in a
> file so that it can be reviewed and I was not sure if there is a way to
> output the errors in DTS. When I run the Stored Proc theough Query
> Analyzer I see the errors in Query Analyzer, I basically want to see
> the same information after running the DTS. Can this be done, if so
> how.
> Any help in this regard is greatly appreciated.
> Thanks
>|||Thanks Greg for your response. I will definitely look at that option.
Is there any way this option could be incorporated when executed
through enterprise manager.
Greg Larsen wrote:
> I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> command you can use the /L option to create a log froma DTS package.
> "shub" wrote:
> > I am using SQL Server 2000 and I have a created a DTS package that
> > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > a user Stored Proc
> > What I am wanting to do is if for some reason the Stored Proc errors
> > out because of bad data , I would like to see the errors reported in a
> > file so that it can be reviewed and I was not sure if there is a way to
> > output the errors in DTS. When I run the Stored Proc theough Query
> > Analyzer I see the errors in Query Analyzer, I basically want to see
> > the same information after running the DTS. Can this be done, if so
> > how.
> >
> > Any help in this regard is greatly appreciated.
> > Thanks
> >
> >|||I don't know of any way, sorry.
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Hi Greg,
Yes, if you open up your DTS package and go to Package => Properties you
will see a tab for 'Logging', in the 'Error Handling' section you can specify
a file to log to. Just remember that the file is always appended to and not
overwritten.
Ray
"shub" wrote:
> Thanks Greg for your response. I will definitely look at that option.
> Is there any way this option could be incorporated when executed
> through enterprise manager.
> Greg Larsen wrote:
> > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > command you can use the /L option to create a log froma DTS package.
> >
> > "shub" wrote:
> >
> > > I am using SQL Server 2000 and I have a created a DTS package that
> > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > a user Stored Proc
> > > What I am wanting to do is if for some reason the Stored Proc errors
> > > out because of bad data , I would like to see the errors reported in a
> > > file so that it can be reviewed and I was not sure if there is a way to
> > > output the errors in DTS. When I run the Stored Proc theough Query
> > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > the same information after running the DTS. Can this be done, if so
> > > how.
> > >
> > > Any help in this regard is greatly appreciated.
> > > Thanks
> > >
> > >
>|||Thanks Ray. I tried using that however when there are multiple errorrs
it is displaying only the first error. For example in my case the DTS
package executes a stored proc to add logins from the table but in some
cases because of typos the proc cannot grant access because it cannot
find the user account in the domain, but if there are multiple errors
it is displaying the very firts one but when I run the same stored proc
through Query analyzer I see all the errors and I need to see all the
errors so that it can be informed that there are wrong entries in the
table.
Any ideas? Here is the only error I am getting
Step 'DTSStep_DTSExecuteSQLTask_2' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:The task reported failure on execution.
(Microsoft OLE DB Provider for SQL Server (80040e14): Windows NT user
or group 'YYY\XXX' not found. Check the name again.)
Step Error code: 8004043B
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
****************************************************************************************************
rb wrote:
> Hi Greg,
> Yes, if you open up your DTS package and go to Package => Properties you
> will see a tab for 'Logging', in the 'Error Handling' section you can specify
> a file to log to. Just remember that the file is always appended to and not
> overwritten.
> Ray
> "shub" wrote:
> > Thanks Greg for your response. I will definitely look at that option.
> > Is there any way this option could be incorporated when executed
> > through enterprise manager.
> > Greg Larsen wrote:
> > > I would use the DTSRUN command to execute the DTS package. On the DTSRUN
> > > command you can use the /L option to create a log froma DTS package.
> > >
> > > "shub" wrote:
> > >
> > > > I am using SQL Server 2000 and I have a created a DTS package that
> > > > copies Data from a Excel Spreasheet to a SQL Server table then executes
> > > > a user Stored Proc
> > > > What I am wanting to do is if for some reason the Stored Proc errors
> > > > out because of bad data , I would like to see the errors reported in a
> > > > file so that it can be reviewed and I was not sure if there is a way to
> > > > output the errors in DTS. When I run the Stored Proc theough Query
> > > > Analyzer I see the errors in Query Analyzer, I basically want to see
> > > > the same information after running the DTS. Can this be done, if so
> > > > how.
> > > >
> > > > Any help in this regard is greatly appreciated.
> > > > Thanks
> > > >
> > > >
> >
> >