|
Home > Archive > MS SQL Server DTS > January 2006 > Check for primary key violation
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Check for primary key violation
|
|
| tjonsek@phenom-biz.com 2006-01-23, 11:24 am |
| I have a package that exports records from a linked server into a table
on the current server. I would like to add a piece of code to skip a
row if that key is already in the table. I attempted this but got a
type mismatch error on 'DTSSource'.
Here's the code snippet I attempted:
if (DTSSource("Emp_PK") = DTSDestination("EmpEvt_EmpFK") and
DTSSource("Evt_PK") = DTSDestination("EmpEvt_EvtFK")) then
Main = DTSTransformStat_Ski
pRow
end if
If I can't accomplish this, I thought perhaps I could add code that
would skip any rows that cause an error, but I'm not sure of the wisdom
in this. Nor, am I sure about the syntax for activex script.
I've double checked and my field names are typed correctly. I've not
attempted this before. Any tips or suggestions would be greatly
appreciated.
Thanks
TJ
| |
| Allan Mitchell 2006-01-23, 8:24 pm |
| Hello tjonsek@phenom-biz.com,
I would not be doing it like this. The way you suggest processes rows 1*1.
This will hurt. If you are already using Linked servers then in your Source
Query for the datapump task you simply add a join between the source table
and the destination table and make sure that the source row is not already
in the destination. This way you do not need to process each row individually
Allan
> I have a package that exports records from a linked server into a
> table
> on the current server. I would like to add a piece of code to skip a
> row if that key is already in the table. I attempted this but got a
> type mismatch error on 'DTSSource'.
> Here's the code snippet I attempted:
> if (DTSSource("Emp_PK") = DTSDestination("EmpEvt_EmpFK") and
> DTSSource("Evt_PK") = DTSDestination("EmpEvt_EvtFK")) then
> Main = DTSTransformStat_Ski
pRow
> end if
> If I can't accomplish this, I thought perhaps I could add code that
> would skip any rows that cause an error, but I'm not sure of the
> wisdom in this. Nor, am I sure about the syntax for activex script.
>
> I've double checked and my field names are typed correctly. I've not
> attempted this before. Any tips or suggestions would be greatly
> appreciated.
> Thanks
> TJ
| |
| tjonsek@phenom-biz.com 2006-01-23, 8:24 pm |
| Ok. I understand what you are saying and I understand your explanation,
just not how to execute it. I apologize. I've not attempted this
before, so I'll do a bit of reading online if I can find anything for
datapump tasks.
If you know any good sources, please let me know.
Thanks
| |
| Allan Mitchell 2006-01-23, 8:24 pm |
| Hello tjonsek@phenom-biz.com,
www.SQLDTS.com is generally considered useful.
in the datapump task instead of specifying a table as the source you are
going to use a query that will retrieve the source rows that are not already
in the destination (LEFT OUTER JOIN, NOT IN, NOT EXISTS)
Allan
> Ok. I understand what you are saying and I understand your
> explanation,
> just not how to execute it. I apologize. I've not attempted this
> before, so I'll do a bit of reading online if I can find anything for
> datapump tasks.
> If you know any good sources, please let me know.
> Thanks
| |
| tjonsek@phenom-biz.com 2006-01-23, 8:24 pm |
| Ok. I get you. Thanks so much. I'll check out the website! Earlier I
had tried an if exists statement, but obviously didn't have everything
correct.
I appreciate it.
|
|
|
|
|