Is it impossible in Main function?
I mean

if dataexeist then
do nothing
else
insert
end if

Thanks.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

The data source file(CSV) is a 3-line text file.
when the file has a row which violates PK_constraints, none of 3 rows is
inserted.
I want other two rows to be inserted.
How can I do that?

TIA.

Error:
Step Copy Data export to [Weather].[dbo].[tblWeather]
Failure reported while processing.
Statement terminated.
violate PRIMARY KEY constraint 'PK_tblWeahter'. can insert duplicate key to
'tblWeather'

Script:
Function Main()
DTSDestination("date") = DTSSource("Col001")
DTSDestination("time") =
left(DTSSource("Col002"),2)+right(DTSSource("Col00 2"),2)
DTSDestination("position") = DTSSource("Col003")
IF TRIM(DTSSource("Col004")) <> "" THEN
DTSDestination("temp") = DTSSource("Col004")
END IF
IF TRIM(DTSSource("Col005")) <> "" THEN
DTSDestination("dewPoint") = DTSSource("Col005")
END IF
IF TRIM(DTSSource("Col006")) <> "" THEN
DTSDestination("humidity") = DTSSource("Col006")
END IF
IF TRIM(DTSSource("Col007")) <> "" THEN
DTSDestination("fainfallStrength") = DTSSource("Col007")
END IF
IF TRIM(DTSSource("Col008")) <> "" THEN
DTSDestination("rainfall") = DTSSource("Col008")
END IF
IF TRIM(DTSSource("Col009")) <> "" THEN
DTSDestination("snowfall") = DTSSource("Col009")
END IF
IF TRIM(DTSSource("Col010")) <> "" THEN
DTSDestination("visibilityRange") = DTSSource("Col010")
END IF
IF TRIM(DTSSource("Col016")) <> "" THEN
DTSDestination("surfaceTemp") = DTSSource("Col016")
END IF
IF TRIM(DTSSource("Col017")) <> "" THEN
DTSDestination("undergroundTemp") = DTSSource("Col017")
END IF
IF TRIM(DTSSource("Col018")) <> "" THEN
DTSDestination("freezingPoint") = DTSSource("Col018")
END IF
Main = DTSTransformStat_OK
End Function

CSV file(data source)
20031208,15:50,ROSA1, 0.2, 0.2,100,1,0,0,2000, 2.1, 2.5, 0.0,MO, ,
1.7, 1.9, 0.0,MO, ,
20031209,15:50,ROSA2, 2.6, 0.8,88,1,0,0,2000, 5.4, 5.4, 0.0,MO, , 5.1,
5.6, -2.7,TR, ,
20031208,15:50,ROSA3, 5.5, -1.3,62,0,0 ,0 ,2000, 9.1, 8.8, 0.0,DR, ,
9.0, 8.6, 0.0,DR, ,

Vio;ation of a Primary key error is considered really bad and will abort the
batch

CREATE TABLE SourceForPKViol(col1 int)
CREATE TABLE DestForPKViol(col1 int PRIMARY KEY)

INSERT SourceForPKViol VALUES(1)
INSERT SourceForPKViol VALUES(2)
INSERT SourceForPKViol VALUES(2)
INSERT SourceForPKViol VALUES(3)

INSERT DestForPKViol SELECT * FROM SourceForPKViol

Note "The batch"

If you do this in your Datapump it will work

Set "max error count" >= 3 and set the "insert batch size" = 1

Personally I wouldn't do it like this. I would

1. Import the text file into a working table
2. use TSQL to compare PK column values and only insert those that do not
lready exist.



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"haode" <iamjhkang (AT) abc (DOT) com> wrote

Yes via a lookup but remember this will execute once for every row. If you
have a lot of rows it will be slow.

How to Use Lookups in DTS
(http://www.sqldts.com/default.aspx?277)

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"haode" <iamjhkang (AT) abc (DOT) com> wrote