Hi!
Here is my situation.
The source and destination are both SQL Server.
================================================== =================
Goal :transform customer's telephone number.
================================================== =================
Source Column:
Cust_Seril_No Tel_Type Tel_No
12345 Home 123-4567
12345 Office 234-4567
67890 Home 321-4321
================================================== =================
Destination Column
Cust_Seril_No Tel_Seq_No Tel_Type Tel_No
12345 1 Home 123-4567
12345 2 Office 234-4567
67890 1 Home 321-4321
================================================== =================
The different Column between souce and destination is "Tel_Seq_No"
Because we use Cust_Seril_No AND Tel_Seq_No to be the PK of the
destination.
When doing transformation,the same cust's different tel_no must has
different Tel_Seq_No ,which can be automatically increased.
In our AP,we have a component to do serial number genaration.
So I take that code in the ActiveX Script of transformation.
Our component generates serial number by checking the exist and max
one.
After that,it will add one to the max number and return the result.

It now seems the component always generates serial number "1" and
won't
increase the number.

So in the above example,12345's Home Tel can be inserted successfully,
but 12345's Office Tel will be failed.

I don't know what's wrong with my setting in transformation task of
the properties's option tab.
I uncheck the "Use Fast Load"
(I use MultiStep Pump to handle insert error.
Friends told me if I use fast load,I can't catch insert failure event
in transformation,so I uncheck it)
The other setting is by default
Max Error Count is 9999
Fetch Buffer Size is 1


And the line in transformation tab is---
Cust_Seril_No,Tel_Type,Tel_No use copy column
Tel_Seq_No has no Source and use ActiveX Script to generate it.

I doubt 10 records dont' insert 10 times,
so when the first record's Tel_Seq_No is generated,
it hasn't inserted in DB.
Then the second record get the same Tel_Seq_No.
After that,the insertion starts and only first record can successfully
be inserted.

Can anyone tell me how to solve this problem?

Thank a lot!!!!

You have failed to give us the Active Script transform to help you out so i
doubt someone will be able to troubleshoot it. But you do not even need that

CREATE TABLE CustTelNumbers
(
CustSerialNo int,
TelType varchar(10),
TelNo varchar(20)
)

INSERT CustTelNumbers VALUES('12345','Home','123-4567')
INSERT CustTelNumbers VALUES('12345','Office','234-4567')
INSERT CustTelNumbers VALUES('23234','Home','123-4567')

SELECT
A.CustSerialNo,
(SELECT Count(*) FROM CustTelNumbers B WHERE B.CustSerialNo =
A.CustSerialNo And B.TelNo > a.TelNo) + 1 as Counter,
A.TelType,
A.TelNo
FROM
CustTelNumbers A
Order By 1

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"woofwoof" <barbq (AT) mail2000 (DOT) com.tw> wrote

Hi! Allan
Thank you very much!
Your suggestion is really a good idea, and I missed some information
about my
situation.

1.One customer can only have one Home Number or one Office Number.
And his Home Number can equal his Office Number.
So I revise your sql "And B.TelNo > a.TelNo" to "And B.TelType >
a.TelType"


2.My ActiveX Script is below
================================================== =====================
Function Main()
Dim objCM
Dim TelSeqNo
Dim vntFilterArray

ReDim vntFilterArray(0, 1)
vntFilterArray(0, 0) = "PER_SERIL_NO"
vntFilterArray(0, 1) = DTSSource("PER_SERIL_NO")

Set objCM = CreateObject("GSSHEARTCM_Num20.NumBase")
TelSeqNo = objCM.GetTheSeqNo("CMTELM", "TEL_SEQ_NO", 1,
vntFilterArray)
Set objCM = Nothing

DTSDestination("TEL_SEQ_NO") = TelSeqNo

Main = DTSTransformStat_OK
End Function



Function InsertFailureMain()
Dim strErrMsg
strErrMsg = "UPDATE T12_TWO_SOURCE_OK" & vbCrLf & _
" SET ERR_LOG = ERR_LOG + 'Insert Fail||'" & vbCrLf & _
" WHERE PETPNO = '" & DTSSource("PETPNO") & "'"

Set objRs=CreateObject("ADODB.Recordset")
cns= "driver={SQL Server};server=" &
DTSglobalVariables("gstrHEART_DataSource").Value &_
";database=" &
DTSGlobalVariables("gstrHEART_Catalog").Value &_
";uid=" & DTSGlobalVariables("gstrHEART_UserID").Value &_
";PWD=" & DTSGlobalVariables("gstrHEART_Password").Value
& ";"

================================================== =====================
Because our Destination table also accepts data from UI.
So when I transform custA's telephone data into it,custA may aleady
have
2 phone data in it.
So I think the easiest way for me is still use component to
automatically
generate serial number for that customer.
Allan's method also could work,but I have to know the max number in
the DB
and add max number with counter....

any good suggestion?

thanks a lot!!