insertdepart.bas

delphi使用sqlserver2000中dts导入导出数据

'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: C:\Documents and Settings\Administrator\桌面\InsertDepart.bas
'Package Name: InsertDepart
'Package Description: 
'Generated Date: 2004-11-12
'Generated Time: 17:25:32
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
	set goPackage = goPackageOld

	goPackage.Name = "InsertDepart"
	goPackage.WriteCompletionStatusToNTEventLog = False
	goPackage.FailOnError = False
	goPackage.PackagePriorityClass = 2
	goPackage.MaxConcurrentSteps = 4
	goPackage.LineageOptions = 0
	goPackage.UseTransaction = True
	goPackage.TransactionIsolationLevel = 4096
	goPackage.AutoCommitTransaction = True
	goPackage.RepositoryMetadataOptions = 0
	goPackage.UseOLEDBServiceComponents = True
	goPackage.LogToSQLServer = False
	goPackage.LogServerFlags = 0
	goPackage.FailPackageOnLogFailure = False
	goPackage.ExplicitGlobalVariables = False
	goPackage.PackageType = 0
	


'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("DTSFlatFile")

	oConnection.ConnectionProperties("Data Source") = "D:\depart.csv"
	oConnection.ConnectionProperties("Mode") = 1
	oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
	oConnection.ConnectionProperties("File Format") = 1
	oConnection.ConnectionProperties("Column Delimiter") = ","
	oConnection.ConnectionProperties("File Type") = 1
	oConnection.ConnectionProperties("Skip Rows") = 0
	oConnection.ConnectionProperties("Text Qualifier") = """"
	oConnection.ConnectionProperties("First Row Column Name") = True
	oConnection.ConnectionProperties("Number of Column") = 0
	oConnection.ConnectionProperties("Max characters per delimited column") = 255
	
	oConnection.Name = "Text File (Source)"
	oConnection.ID = 1
	oConnection.Reusable = True
	oConnection.ConnectImmediate = False
	oConnection.DataSource = "D:\depart.csv"
	oConnection.ConnectionTimeout = 60
	oConnection.UseTrustedConnection = False
	oConnection.UseDSL = False
	
	'If you have a password for this connection, please uncomment and add your password below.
	'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

	oConnection.ConnectionProperties("Persist Security Info") = True
	oConnection.ConnectionProperties("User ID") = "sa"
	oConnection.ConnectionProperties("Initial Catalog") = "JYT"
	oConnection.ConnectionProperties("Data Source") = "192.168.1.114"
	oConnection.ConnectionProperties("Application Name") = "DTS 设计器"
	
	oConnection.Name = "Microsoft OLE DB Provider for SQL Server"
	oConnection.ID = 2
	oConnection.Reusable = True
	oConnection.ConnectImmediate = False
	oConnection.DataSource = "192.168.1.114"
	oConnection.UserID = "sa"
	oConnection.ConnectionTimeout = 60
	oConnection.Catalog = "JYT"
	oConnection.UseTrustedConnection = False
	oConnection.UseDSL = False
	
	'If you have a password for this connection, please uncomment and add your password below.
	'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

	oStep.Name = "DTSStep_DTSDataPumpTask_1"
	oStep.Description = "转换数据任务: 未定义"
	oStep.ExecutionStatus = 1
	oStep.TaskName = "DTSTask_DTSDataPumpTask_1"
	oStep.CommitSuccess = False
	oStep.RollbackFailure = False
	oStep.ScriptLanguage = "VBScript"
	oStep.AddGlobalVariables = True
	oStep.RelativePriority = 3
	oStep.CloseConnection = True
	oStep.ExecuteInMainThread = False
	oStep.IsPackageDSORowset = False
	oStep.JoinTransactionIfPresent = False
	oStep.DisableStep = False
	oStep.FailPackageOnError = False
	
goPackage.Steps.Add oStep
Set oStep = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (转换数据任务: 未定义)
Call Task_Sub1( goPackage	)

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
tracePackageError goPackage
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package lines above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'-----------------------------------------------------------------------------
' error reporting using step.GetExecutionErrorInfo after execution
'-----------------------------------------------------------------------------
Public Sub tracePackageError(oPackage As DTS.Package)
Dim ErrorCode As Long
Dim ErrorSource As String
Dim ErrorDescription As String
Dim ErrorHelpFile As String
Dim ErrorHelpContext As Long
Dim ErrorIDofInterfaceWithError As String
Dim i As Integer

	For i = 1 To oPackage.Steps.Count
		If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
			oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
					ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
			MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
		End If
	Next i

End Sub

'------------- define Task_Sub1 for task DTSTask_DTSDataPumpTask_1 (转换数据任务: 未定义)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
oTask.Name = "DTSTask_DTSDataPumpTask_1"
Set oCustomTask1 = oTask.CustomTask

	oCustomTask1.Name = "DTSTask_DTSDataPumpTask_1"
	oCustomTask1.Description = "转换数据任务: 未定义"
	oCustomTask1.SourceConnectionID = 1
	oCustomTask1.SourceObjectName = "D:\depart.csv"
	oCustomTask1.DestinationConnectionID = 2
	oCustomTask1.DestinationObjectName = "[JYT].[dbo].[AccountTbdepart]"
	oCustomTask1.ProgressRowCount = 1000
	oCustomTask1.MaximumErrorCount = 0
	oCustomTask1.FetchBufferSize = 1
	oCustomTask1.UseFastLoad = True
	oCustomTask1.InsertCommitSize = 0
	oCustomTask1.ExceptionFileColumnDelimiter = "|"
	oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
	oCustomTask1.AllowIdentityInserts = False
	oCustomTask1.FirstRow = 0
	oCustomTask1.LastRow = 0
	oCustomTask1.FastLoadOptions = 2
	oCustomTask1.ExceptionFileOptions = 1
	oCustomTask1.DataPumpOptions = 0
	
Call oCustomTask1_Trans_Sub1( oCustomTask1	)
Call oCustomTask1_Trans_Sub2( oCustomTask1	)
Call oCustomTask1_Trans_Sub3( oCustomTask1	)
Call oCustomTask1_Trans_Sub4( oCustomTask1	)
Call oCustomTask1_Trans_Sub5( oCustomTask1	)
Call oCustomTask1_Trans_Sub6( oCustomTask1	)
Call oCustomTask1_Trans_Sub7( oCustomTask1	)
Call oCustomTask1_Trans_Sub8( oCustomTask1	)
Call oCustomTask1_Trans_Sub9( oCustomTask1	)
Call oCustomTask1_Trans_Sub10( oCustomTask1	)
Call oCustomTask1_Trans_Sub11( oCustomTask1	)
Call oCustomTask1_Trans_Sub12( oCustomTask1	)
Call oCustomTask1_Trans_Sub13( oCustomTask1	)
		
		
goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

	Dim oTransformation As DTS.Transformation2
	Dim oTransProps as DTS.Properties
	Dim oColumn As DTS.Column
	Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
		oTransformation.Name = "DTSTransformation__1"
		oTransformation.TransformFlags = 63
		oTransformation.ForceSourceBlobsBuffered = 0
		oTransformation.ForceBlobsInMemory = False
		oTransformation.InMemoryBlobSize = 1048576
		oTransformation.TransformPhases = 4
		
		Set oColumn = oTransformation.SourceColumns.New("单位" , 1)
			oColumn.Name = "单位"
			oColumn.Ordinal = 1
			oColumn.Flags = 32
			oColumn.Size = 255
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.SourceColumns.Add oColumn
		Set oColumn = Nothing

		Set oColumn = oTransformation.DestinationColumns.New("Addr" , 1)
			oColumn.Name = "Addr"
			oColumn.Ordinal = 1
			oColumn.Flags = 104
			oColumn.Size = 50
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.DestinationColumns.Add oColumn
		Set oColumn = Nothing

	Set oTransProps = oTransformation.TransformServerProperties

		
	Set oTransProps = Nothing

	oCustomTask1.Transformations.Add oTransformation
	Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub2(ByVal oCustomTask1 As Object)

	Dim oTransformation As DTS.Transformation2
	Dim oTransProps as DTS.Properties
	Dim oColumn As DTS.Column
	Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
		oTransformation.Name = "DTSTransformation__2"
		oTransformation.TransformFlags = 63
		oTransformation.ForceSourceBlobsBuffered = 0
		oTransformation.ForceBlobsInMemory = False
		oTransformation.InMemoryBlobSize = 1048576
		oTransformation.TransformPhases = 4
		
		Set oColumn = oTransformation.SourceColumns.New("姓名" , 1)
			oColumn.Name = "姓名"
			oColumn.Ordinal = 1
			oColumn.Flags = 32
			oColumn.Size = 255
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.SourceColumns.Add oColumn
		Set oColumn = Nothing

		Set oColumn = oTransformation.DestinationColumns.New("AcntName" , 1)
			oColumn.Name = "AcntName"
			oColumn.Ordinal = 1
			oColumn.Flags = 104
			oColumn.Size = 20
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.DestinationColumns.Add oColumn
		Set oColumn = Nothing

	Set oTransProps = oTransformation.TransformServerProperties

		
	Set oTransProps = Nothing

	oCustomTask1.Transformations.Add oTransformation
	Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub3(ByVal oCustomTask1 As Object)

	Dim oTransformation As DTS.Transformation2
	Dim oTransProps as DTS.Properties
	Dim oColumn As DTS.Column
	Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
		oTransformation.Name = "DTSTransformation__3"
		oTransformation.TransformFlags = 63
		oTransformation.ForceSourceBlobsBuffered = 0
		oTransformation.ForceBlobsInMemory = False
		oTransformation.InMemoryBlobSize = 1048576
		oTransformation.TransformPhases = 4
		
		Set oColumn = oTransformation.SourceColumns.New("手机" , 1)
			oColumn.Name = "手机"
			oColumn.Ordinal = 1
			oColumn.Flags = 32
			oColumn.Size = 255
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.SourceColumns.Add oColumn
		Set oColumn = Nothing

		Set oColumn = oTransformation.DestinationColumns.New("ContactMobile" , 1)
			oColumn.Name = "ContactMobile"
			oColumn.Ordinal = 1
			oColumn.Flags = 104
			oColumn.Size = 15
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.DestinationColumns.Add oColumn
		Set oColumn = Nothing

	Set oTransProps = oTransformation.TransformServerProperties

		
	Set oTransProps = Nothing

	oCustomTask1.Transformations.Add oTransformation
	Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub4(ByVal oCustomTask1 As Object)

	Dim oTransformation As DTS.Transformation2
	Dim oTransProps as DTS.Properties
	Dim oColumn As DTS.Column
	Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
		oTransformation.Name = "DTSTransformation__4"
		oTransformation.TransformFlags = 63
		oTransformation.ForceSourceBlobsBuffered = 0
		oTransformation.ForceBlobsInMemory = False
		oTransformation.InMemoryBlobSize = 1048576
		oTransformation.TransformPhases = 4
		
		Set oColumn = oTransformation.SourceColumns.New("电话" , 1)
			oColumn.Name = "电话"
			oColumn.Ordinal = 1
			oColumn.Flags = 32
			oColumn.Size = 255
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.SourceColumns.Add oColumn
		Set oColumn = Nothing

		Set oColumn = oTransformation.DestinationColumns.New("ContactPhone" , 1)
			oColumn.Name = "ContactPhone"
			oColumn.Ordinal = 1
			oColumn.Flags = 104
			oColumn.Size = 20
			oColumn.DataType = 129
			oColumn.Precision = 0
			oColumn.NumericScale = 0
			oColumn.Nullable = True
			
		oTransformation.DestinationColumns.Add oColumn
		Set oColumn = Nothing

	Set oTransProps = oTransformation.TransformServerProperties

		
	Set oTransProps = Nothing

	oCustomTask1.Transformations.Add oTransformation
	Set oTransformation = Nothing

End Sub

Public Sub oCustomTask1_Trans_Sub5(ByVal oCustomTask1 As Object)

	Dim oTransformation As DTS.Transformation2
	Dim oTransProps as DTS.Properties
	Dim oColumn As DTS.Column
	Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
		oTransformation.Name = "DTSTransformation__5"
		oTransformation.TransformFlags = 63
		oTransformation.ForceSourceBlobsBuffered = 0