dts package errors

I am transferring data from a text file to a table in SQL Server. Is
there any way to export the dts package logs or a similar error log to
a text file. I need to know if the errors are duplicate keys or
missing files. I don't know if I need to write some code or what I
need to do. Any help'It largely depends on what type of transformation you're using. If you are
using the Transform Data Task then on the options tab of the task properties
you can set a filename for the "Exception file". If you uncheck the '7.0
format' option and select the other 3 check boxes you'll get an exception
file and 2 other files - each containing the erroring rows. If you are using
the BULK INSERT task then there is no such granular option. You could try
package logging (right-click on the white background, "Package Properties",
"Logging" tab and "Error file:" text box) but AFAIR you don't get much
useful info there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I have tried the exception file but if the file does not exist there is
no error written to that file. I will need to know this.
Paul Ibison wrote:
> It largely depends on what type of transformation you're using. If you are
> using the Transform Data Task then on the options tab of the task properties
> you can set a filename for the "Exception file". If you uncheck the '7.0
> format' option and select the other 3 check boxes you'll get an exception
> file and 2 other files - each containing the erroring rows. If you are using
> the BULK INSERT task then there is no such granular option. You could try
> package logging (right-click on the white background, "Package Properties",
> "Logging" tab and "Error file:" text box) but AFAIR you don't get much
> useful info there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

dts package errors

I am transferring data from a text file to a table in SQL Server. Is
there any way to export the dts package logs or a similar error log to
a text file. I need to know if the errors are duplicate keys or
missing files. I don't know if I need to write some code or what I
need to do. Any help'It largely depends on what type of transformation you're using. If you are
using the Transform Data Task then on the options tab of the task properties
you can set a filename for the "Exception file". If you uncheck the '7.0
format' option and select the other 3 check boxes you'll get an exception
file and 2 other files - each containing the erroring rows. If you are using
the BULK INSERT task then there is no such granular option. You could try
package logging (right-click on the white background, "Package Properties",
"Logging" tab and "Error file:" text box) but AFAIR you don't get much
useful info there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||I have tried the exception file but if the file does not exist there is
no error written to that file. I will need to know this.
Paul Ibison wrote:
> It largely depends on what type of transformation you're using. If you are
> using the Transform Data Task then on the options tab of the task properti
es
> you can set a filename for the "Exception file". If you uncheck the '7.0
> format' option and select the other 3 check boxes you'll get an exception
> file and 2 other files - each containing the erroring rows. If you are usi
ng
> the BULK INSERT task then there is no such granular option. You could try
> package logging (right-click on the white background, "Package Properties"
,
> "Logging" tab and "Error file:" text box) but AFAIR you don't get much
> useful info there.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

DTS logs and "sysdtssteplog" in msdb

HI,
Does anybody know (maybe from MS) is it safe to remove (truncate table) rows
from the "sysdtssteplog" table with contains DTL step logs?
RedardsYou can delete the logs from Enterprise Manager. Go to Data Transformation
Services, right click in the right pane, and choose 'Package logs...' from
the pop up menu. This will give you a dialog from which you can delete
package logs for all package past and present, and you can delete all the
logs for a package at once as well.
--
Jacco Schalkwijk
SQL Server MVP
"stats12" <vhs180@.polbox.com> wrote in message
news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> HI,
> Does anybody know (maybe from MS) is it safe to remove (truncate table)
rows
> from the "sysdtssteplog" table with contains DTL step logs?
> Redards
>|||Yes i know, but i must do it manulay for each package.
Now i have 70-ty packages and each is running even 100 times a day.
That's why i'm thinging about some process with wich i would be able to
automatycly clear the DTS logs.
U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
wiadomo¶ci news:e6y1vsRpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> You can delete the logs from Enterprise Manager. Go to Data Transformation
> Services, right click in the right pane, and choose 'Package logs...' from
> the pop up menu. This will give you a dialog from which you can delete
> package logs for all package past and present, and you can delete all the
> logs for a package at once as well.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "stats12" <vhs180@.polbox.com> wrote in message
> news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > HI,
> >
> > Does anybody know (maybe from MS) is it safe to remove (truncate table)
> rows
> > from the "sysdtssteplog" table with contains DTL step logs?
> >
> > Redards
> >
> >
>|||You can create a DTS package with a VBScript task and use the DTS object
model to remove the logs with the PackageSQLServer.RemovePackageLogRecords
method See the article "Retrieving DTS System, Package, and Log Data" in
Books Online for more details.
Undocumented and thus unsupported is the use of the stored procedures that
are called by the above, sp_dump_dtspackagelog and sp_dump_dtslog_all. You
can use these, or have a look at the code of them (they are in the msdb
database), but if something goes wrong with updating system tables directly
like this, you won't have anything to fall back on
--
Jacco Schalkwijk
SQL Server MVP
"stats12" <vhs180@.polbox.com> wrote in message
news:eyJuolSpDHA.3024@.tk2msftngp13.phx.gbl...
> Yes i know, but i must do it manulay for each package.
> Now i have 70-ty packages and each is running even 100 times a day.
> That's why i'm thinging about some process with wich i would be able to
> automatycly clear the DTS logs.
>
> U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
> wiadomo¶ci news:e6y1vsRpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > You can delete the logs from Enterprise Manager. Go to Data
Transformation
> > Services, right click in the right pane, and choose 'Package logs...'
from
> > the pop up menu. This will give you a dialog from which you can delete
> > package logs for all package past and present, and you can delete all
the
> > logs for a package at once as well.
> >
> > --
> > Jacco Schalkwijk
> > SQL Server MVP
> >
> >
> > "stats12" <vhs180@.polbox.com> wrote in message
> > news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > HI,
> > >
> > > Does anybody know (maybe from MS) is it safe to remove (truncate
table)
> > rows
> > > from the "sysdtssteplog" table with contains DTL step logs?
> > >
> > > Redards
> > >
> > >
> >
> >
>|||Thank's for the tip with VB and the "PackageSQLServer" object - that's all i
need...
Regards...
U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
wiadomo¶ci news:eqoiYDTpDHA.392@.TK2MSFTNGP11.phx.gbl...
> You can create a DTS package with a VBScript task and use the DTS object
> model to remove the logs with the PackageSQLServer.RemovePackageLogRecords
> method See the article "Retrieving DTS System, Package, and Log Data" in
> Books Online for more details.
> Undocumented and thus unsupported is the use of the stored procedures that
> are called by the above, sp_dump_dtspackagelog and sp_dump_dtslog_all. You
> can use these, or have a look at the code of them (they are in the msdb
> database), but if something goes wrong with updating system tables
directly
> like this, you won't have anything to fall back on
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "stats12" <vhs180@.polbox.com> wrote in message
> news:eyJuolSpDHA.3024@.tk2msftngp13.phx.gbl...
> > Yes i know, but i must do it manulay for each package.
> > Now i have 70-ty packages and each is running even 100 times a day.
> > That's why i'm thinging about some process with wich i would be able to
> > automatycly clear the DTS logs.
> >
> >
> > U¿ytkownik "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> napisa³ w
> > wiadomo¶ci news:e6y1vsRpDHA.2512@.TK2MSFTNGP09.phx.gbl...
> > > You can delete the logs from Enterprise Manager. Go to Data
> Transformation
> > > Services, right click in the right pane, and choose 'Package logs...'
> from
> > > the pop up menu. This will give you a dialog from which you can delete
> > > package logs for all package past and present, and you can delete all
> the
> > > logs for a package at once as well.
> > >
> > > --
> > > Jacco Schalkwijk
> > > SQL Server MVP
> > >
> > >
> > > "stats12" <vhs180@.polbox.com> wrote in message
> > > news:%231txanRpDHA.2808@.TK2MSFTNGP10.phx.gbl...
> > > > HI,
> > > >
> > > > Does anybody know (maybe from MS) is it safe to remove (truncate
> table)
> > > rows
> > > > from the "sysdtssteplog" table with contains DTL step logs?
> > > >
> > > > Redards
> > > >
> > > >
> > >
> > >
> >
> >
>