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
go. Any help or code example esspecially would be a big help. Thanks!

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

"Dave Anderberg via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote
in message news:forum (AT) nospam (DOT) SQLMonster.com:

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.

--
Message posted via http://www.sqlmonster.com