sql server dts: checking for duplicant entries with DTS Import -- sql server dts

checking for duplicant entries with DTS Import

Dave Anderberg via SQLMonster.com
5/20/2005 12:00:00 AM

sql server dts:

I'm trying to make a DTS Import Transformation with the DTS Editor in
Enterprise. My main problem right now is that I'm trying to import just a
few columns from a table with several tables worth of information. So
there's a tendancy for redundant data in the set that I'm working with. I
keep getting either the classic "Violation of PRIMARY KEY constraint
<table>. Cannot insert duplicate key in object <table>." or the table
doesn't get populated depending on what kind of code I'm experimenting
with. I'm trying to steer away from a temp table if possible due to
resources.

here is the transformation script (this one doesn't copy anything):

Function Main()
dim lookupZone
lookupZone = DTSLookups("zoneNumber").Execute(DTSSource("Zone#"))
if IsNull( lookupZone ) then
DTSDestination("ZoneNumber") = DTSSource("Zone #")
DTSDestination("ZoneName") = DTSSource("Zone Name")
'plus a few other cloumn copies...
Main = DTSTransformStat_OK
else
Main = DTSTransformStat_SkipRow
end if
End Function

and here's the lookup script:

SELECT ZoneNumber FROM tblSTORE_Zones WHERE (ZoneNumber = ?)

The idea is to check to see if the ZoneNumber is already present in the
Destination table, and then to insert that row if it is not.

ZoneNumber is the indentity key, and the table I'm trying to insert into is
empty. I'm getting the info from a text doc that came from an excel table.

I've been looking all over for some good basic examples, but I haven't
really been able to find any, and I'm more or less figuring this out as I

Re: checking for duplicant entries with DTS Import

Allan Mitchell
5/21/2005 12:00:00 AM

Personally I would import the Source to the destination into a working
table. I would then do the comparisons in TSQL using a ExecuteSQL task
or a DataPump task with your real destination. If the dataset is even
slightly large this will more often than not out perform the lookup *
row solution.

Allan

[quoted text, click to view]
in message news:forum@nospam.SQLMonster.com:

[quoted text, click to view]

Re: checking for duplicant entries with DTS Import

Dave Anderberg via SQLMonster.com
5/24/2005 12:00:00 AM

I figured it out. It works pretty well with lookups once you figure it
out. My problem was with the data types, which are sort of tricky if you
haven't worked with scripting languages very much like me.

Here's what I ended up using:

FUNCTION Main()

DIM lookupZones
DIM zoneNum

zoneNum = Cint( DTSSource("Zone #") )
lookupZones = DTSLookups("zoneNumber").Execute(zoneNum)

IF zoneNum = Cint( lookupZones ) THEN
main = DTSTransformStat_SkipInsert
ELSE
DTSDestination("ZoneNumber") = DTSSource("Zone #")
' plus various other columns
main = DTSTransformStat_OK
END IF

END FUNCTION

So for anybody who needs help in the future, this is a pretty streamlined
easy way to do it. You just need to make sure you know what your variables
are being set to and what data type they use.

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server dts group.