Capture Error In DTS Package

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Creative - 26 Nov 2007 21:54 GMT

I have a SQL Server Package that is dumping a text file into an Access
Database. However, due to update timing every now and then it errors
out due to a primary key violation. How do I capture that error
number in my next Active Script Task and determine whether or not I
want to continue (since if it's a duplicate I just want to skip it and
finish the DTS package). If it's a different error I want to log
it.

Thanks!

Reply to this Message

John Bell - 27 Nov 2007 09:00 GMT

Hi

If these are identity values you may want to check out
http://www.sqldts.com/293.aspx
if not try
http://www.sqldts.com/282.aspx

John

> I have a SQL Server Package that is dumping a text file into an Access
> Database. However, due to update timing every now and then it errors
[quoted text clipped - 5 lines]
>
> Thanks!

Reply to this Message

Creative - 27 Nov 2007 14:26 GMT

> Hi
>
[quoted text clipped - 14 lines]
>
> - Show quoted text -

Thanks for the reply but it doesn't look like that answers my
question. I'm actually using a File Transformation from a text file
to an access database. I have a connection to the text file and a
connection to an access database with a transformation connecting the
two.

Reply to this Message

John Bell - 27 Nov 2007 14:50 GMT

Hi

There was no mention of the destination database being an access database,
in your original post. I suggest that you therefore load into a staging table
and either do an update if the PK exists or an insert if it doesn't.

John

> > Hi
> >
[quoted text clipped - 20 lines]
> connection to an access database with a transformation connecting the
> two.

Reply to this Message

JXStern - 30 Nov 2007 05:02 GMT

I don't know the answer, but it's a good question, and I'm in a
similar situation in SSIS - except I want to test for a warning code!

Googling found this crufty answer:
http://www.quest-pipelines.com/newsletter-v4/0603_E.htm

There may be a better answer if you open up the multiphase data pump
business, but I have small experience with that.

Note there is also a DTS group, I'll copy this to.

Josh

>I have a SQL Server Package that is dumping a text file into an Access
>Database. However, due to update timing every now and then it errors
[quoted text clipped - 5 lines]
>
>Thanks!

Reply to this Message

John Bell - 30 Nov 2007 08:26 GMT

Hi Josh

I pointed the OP to the tutorial on Multiphase datapump on SQL DTS. In SSIS
you have a standarderrorvariable which may be of some use to you, and there
is the configure error output dialog. you may want to check out Professional
SQL Server 2005 Integration Services by Brian Knight et al ISBN 0764584359 or
the short videos on http://www.jumpstarttv.com such as
http://www.jumpstarttv.com/Media.aspx?vid=34 where Brian shows how an
application similar to the examples in the book work.

John

> I don't know the answer, but it's a good question, and I'm in a
> similar situation in SSIS - except I want to test for a warning code!
[quoted text clipped - 18 lines]
> >
> >Thanks!

Reply to this Message

JRStern - 30 Nov 2007 19:19 GMT

John,

Thanks for several good hints, I'll be following them all. Configure error
output dialog?!?! Oh, in a data flow. Righto.

I keep waiting for some other, newer SSIS books to come out.

I've got the basics grinding along now, anyway.

Thanks again.

Josh

> Hi Josh
>
[quoted text clipped - 30 lines]
> > >
> > >Thanks!

Reply to this Message