[原创建立DTS包示例代码]ASP将大量数据导入SQL SERVER表

madpolice 2005-02-01 07:41:56

<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<%Option Explicit%>
<%
Server.ScriptTimeOut = 1800
Const DTSStepExecResult_Failure = 1

Dim DestinationTableName,DataFile,FormatFile
DestinationTableName = "target" '目的表名
DataFile = Server.Mappath("source.txt") '源文本文件
FormatFile = Server.Mappath("source.fmt") '源导入格式

Dim goPackage
Set goPackage = Server.CreateObject("DTS.Package")

Call Main()

Sub Main()
'建立连接
Dim oConnection
Set oConnection = goPackage.Connections.New("SQLOLEDB.1")
oConnection.ID = 1
oConnection.DataSource = "(local)"
oConnection.UserID = "sa"
oConnection.Password = "sa"
oConnection.Catalog = "test"
goPackage.Connections.Add oConnection
Set oConnection = Nothing

'建立工作流
Dim oStep
Set oStep = goPackage.Steps.New
oStep.Name = "Task1"
oStep.TaskName = "Task1"
goPackage.Steps.Add oStep
Set oStep = Nothing

Call Task_Sub1( goPackage )
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
Set goPackage = Nothing
End Sub

'错误处理
Sub tracePackageError(oPackage)
Dim oDTSStep,sResult,bSucceeded
bSucceeded = True
For Each oDTSStep In oPackage.Steps
If oDTSStep.ExecutionResult = DTSStepExecResult_Failure Then
sResult = sResult & "Package " & oDTSStep.Name & " failed.<br>"
bSucceeded = False
Else
sResult = sResult & "Package " & oDTSStep.Name & " succeeded.<br>"
End If
Next
If bSucceeded Then
Response.Write "<h1>Package Succeeded</h1>"
Else
Response.Write "<h1>Package Failed</h1>"
End If
Response.Write sResult
End Sub

'建立任务(大容量插入)
Sub Task_Sub1(ByVal goPackage)
Dim oTask
Dim oLookup
Dim oCustomTask1
Set oTask = goPackage.Tasks.New("DTSBulkInsertTask")
oTask.Name = "Task1"
Set oCustomTask1 = oTask.CustomTask
oCustomTask1.Name = "Task1"
oCustomTask1.DestinationTableName = DestinationTableName
oCustomTask1.ConnectionID = 1
oCustomTask1.BatchSize = 1000
oCustomTask1.DataFile = DataFile
oCustomTask1.FormatFile = FormatFile
oCustomTask1.TableLock = True
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing
End Sub
%>

...全文