dtsrun /Aglobal_variable_name:typeid=value
执行dts可以带一个全局变量
你可以试试
执行dts可以带一个全局变量
你可以试试
解决方案 »
- 数据库ID
- 创建主键自定义名称的用处是什么?
- 请问是否在SQL SERVER中某列建立索引后,当查询时,SQL SERVER会自动运用索引
- varchar(5)能提取超过5个字符的数据吗?
- 相领记录比较的语句怎么写,谢谢各位高手
- exec string 的结果怎么传递出来
- 关于时间格式的转换?
- 如何得到SQLServer中当前插入记录的ID?
- 使用ms sql server ,我能在程序中创建一个job,在job中写sql ,对表中的某个字段进行修改吗?
- 用存储过程实现: 怎样找到远程主机上有哪些数据库,库中有哪些用户表,存储过程,触发器?
- 关于游标的问题
- 谁有SQL-DMO的帮助文档,急急急急急急急急!
Function ExecDTS(strDTS_Name: string): boolean;
var
n: Variant;
oConnection: Variant;
oStep,oTask,oCustomTask: variant;
begin
moPackage := CreateOleObject('dts.package2');
oConnection := moPackage.Connections.New('SQLOLEDB');
oStep := moPackage.Steps.New;
oTask := moPackage.Tasks.New('DTSExecutePackageTask');
oCustomTask := oTask.CustomTask;
moPackage.FailOnError := True; oCustomTask.PackagePassword := 'user';
oCustomTask.FileName := strDTS_Name;
oCustomTask.Name := 'ExecPkgTask'; oStep.TaskName := oCustomTask.Name;
oStep.Name := 'ExecPkgStep';
oStep.ExecuteInMainThread := True; moPackage.Steps.Add(oStep);
moPackage.Tasks.Add(oTask);
try
moPackage.Execute;
except
oCustomTask := n;
oTask := n;
oStep := n;
moPackage.Uninitialize;
ExecDTS := False;
Exit;
end; ExecDTS := True;
oCustomTask := n;
oTask := n;
oStep := n;
moPackage.Uninitialize;
end;
怎么改变dts包里的连接属性,另外那里有这方面的资料?
非常感谢!
注意参数SourceDSN和SdestDsn分别为源和目标的数据库连接串
Public Function createDTS(sPackageName As String, sSourceDsn As String, sDestDsn As String, sTableNames As String) As Boolean
Dim oConnection As DTS.Connection
Dim oTask As DTS.Task, oStep As DTS.Step, oDataPumpTask As DTS.DataPumpTask
Dim iSourceCnId As Integer, iDesCnId As Integer
Dim oConstraint As DTS.PrecedenceConstraint
Dim oDTSForm As DTS.Transformation
iSourceCnId = 1
iDesCnId = 2
Set oPackage = New DTS.Package
oPackage.Name = sPackageName
'oPackage.FailOnError = True
Set oConnection = oPackage.Connections.New()
oConnection.ConnectionProperties("Extended Properties") = sSourceDsn
oConnection.ID = iSourceCnId
oConnection.Reusable = True
oPackage.Connections.Add oConnection
Set oConnection = Nothing
Set oConnection = oPackage.Connections.New()
oConnection.DataSource = ""
oConnection.ConnectionProperties("Extended Properties") = sDestDsn
oConnection.UseTrustedConnection = True
oConnection.ID = iDesCnId
oConnection.Reusable = True
oPackage.Connections.Add oConnection
Set oConnection = Nothing
Dim i, sTables
sTables = Split(sTableNames, ":")
Dim oTempStep As DTS.Step
For i = 0 To UBound(sTables)
If Trim(sTables(i)) <> "" Then
Set oTask = oPackage.Tasks.New("DTSDataPumpTask")
Set oDataPumpTask = oTask.CustomTask
oDataPumpTask.SourceConnectionID = iSourceCnId
oDataPumpTask.SourceObjectName = sTables(i)
oDataPumpTask.DestinationConnectionID = iDesCnId
oDataPumpTask.DestinationObjectName = sTables(i)
oDataPumpTask.Name = "task" & i
oPackage.Tasks.Add oTask
Set oStep = oPackage.Steps.New()
oStep.Name = "step" & i
oStep.TaskName = "task" & i
oStep.ExecuteInMainThread = True
oPackage.Steps.Add oStep
Set oDTSForm = oDataPumpTask.Transformations.New("DTSPump.DataPumpTransformCopy")
oDTSForm.TransformFlags = 63
oDTSForm.Name = "step" & i
oDataPumpTask.Transformations.Add oDTSForm
Set oDTSForm = Nothing
Set oTask = Nothing
Set oDataPumpTask = Nothing
If i > 0 Then
Set oTempStep = oPackage.Steps("step" & (i - 1))
Set oConstraint = oStep.PrecedenceConstraints.New("step" & (i - 1))
oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
oConstraint.StepName = "step" & (i - 1)
oConstraint.Value = DTSTaskExecResult_Success
oStep.PrecedenceConstraints.Add oConstraint
Set oConstraint = Nothing
End If
Set oStep = Nothing
End If
Next i
oPackage.FailOnError = True
On Error GoTo errDTSExecute
oPackage.Execute
createDTS = True
oPackage.UnInitialize
Exit Function
errDTSExecute:
sErrMsg = Err.Description
createDTS = False
Exit Function
End Function