Function Main()
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
set mySourceConn = CreateObject("ADODB.Connection")
set mySourceRecordset = CreateObject("ADODB.Recordset")
mySourceConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=family1;user id = 'sa';password=''"
mySQLCmdText = "Select * from price_test where ChangeCode<>''"
mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeyset
If mySourceRecordset.RecordCount < 1 Then
MsgBox " There are no records found. Return a Failure code"
'Main = DTSTaskExecResult_Failure
Main = DTSTransformStat_SkipRow
Else
dim ChCD,Num, pname,myDestSQL
set myDestConn = CreateObject("ADODB.Connection")
myDestConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=family1;user id ='sa';password=''"
for countr = 1 to mySourceRecordset.RecordCount
Num = mySourceRecordset.Fields("id").value
pname = mySourceRecordset.Fields("pricename").value
ChCD = mySourceRecordset.Fields("changecode").value
pname = "'" & pname & "'"
select case ChCD
case "1"
myDestSQL = "insert into price values ( " & Num & "," & pname & ")"
myDestConn.Execute myDestSQL
Main=DTSTransformStat_InsertQuery
case "2"
myDestSQL = "update price set pricename="& pname &" where id="&Num&""
myDestConn.Execute myDestSQL
Main=DTSTransformStat_UpdateQuery
case "3"
myDestSQL = "delete from price where id="&Num&""
myDestConn.Execute myDestSQL
Main=DTSTransformStat_DeleteQuery
End Select
mySourceRecordset.MoveNext
Next
'Main = DTSTaskExecResult_Success End If
End Function报的错误为:DateDrivenQuery:转换状态返回一个DateDrivenQuery值,但未指定DateDrivenQuerie值。
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
set mySourceConn = CreateObject("ADODB.Connection")
set mySourceRecordset = CreateObject("ADODB.Recordset")
mySourceConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=family1;user id = 'sa';password=''"
mySQLCmdText = "Select * from price_test where ChangeCode<>''"
mySourceRecordset.Open mySQLCmdText, mySourceConn, adOpenKeyset
If mySourceRecordset.RecordCount < 1 Then
MsgBox " There are no records found. Return a Failure code"
'Main = DTSTaskExecResult_Failure
Main = DTSTransformStat_SkipRow
Else
dim ChCD,Num, pname,myDestSQL
set myDestConn = CreateObject("ADODB.Connection")
myDestConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); Initial Catalog=family1;user id ='sa';password=''"
for countr = 1 to mySourceRecordset.RecordCount
Num = mySourceRecordset.Fields("id").value
pname = mySourceRecordset.Fields("pricename").value
ChCD = mySourceRecordset.Fields("changecode").value
pname = "'" & pname & "'"
select case ChCD
case "1"
myDestSQL = "insert into price values ( " & Num & "," & pname & ")"
myDestConn.Execute myDestSQL
Main=DTSTransformStat_InsertQuery
case "2"
myDestSQL = "update price set pricename="& pname &" where id="&Num&""
myDestConn.Execute myDestSQL
Main=DTSTransformStat_UpdateQuery
case "3"
myDestSQL = "delete from price where id="&Num&""
myDestConn.Execute myDestSQL
Main=DTSTransformStat_DeleteQuery
End Select
mySourceRecordset.MoveNext
Next
'Main = DTSTaskExecResult_Success End If
End Function报的错误为:DateDrivenQuery:转换状态返回一个DateDrivenQuery值,但未指定DateDrivenQuerie值。
报错为:脚本函数“Main”返回了无效的DTSTransformStatus值,或者Activex脚本未返回整数类型。DataPump将不进行进一步的操作