Capture Error In DTS Package

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!
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
"Creative" wrote:

> 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!
>
|||On Nov 27, 3:00 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> If these are identity values you may want to check outhttp://www.sqldts.com/293.aspx
> if not tryhttp://www.sqldts.com/282.aspx
> John
>
> "Creative" wrote:
>
> - 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.
|||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
"Creative" wrote:

> On Nov 27, 3:00 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> 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.
>
|||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
On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
wrote:

>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!
|||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
"JXStern" wrote:

> 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
>
> On Mon, 26 Nov 2007 13:54:33 -0800 (PST), Creative <GraberJ@.gmail.com>
> wrote:
>
>
|||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
"John Bell" wrote:
[vbcol=seagreen]
> 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
> "JXStern" wrote: