DTS导入的问题

Posted on 2007-04-17 20:39 过江阅读(260) 评论(0) 编辑收藏所属分类: EXCEL、WORD与SQL

不好意思先放下，解决了就撤下去
我在利用DTS导入SQL时，程序不出现错误，但是没有将需要的数据导入到sql中，大家帮我看看：
代码如下：
Button代码：

string strdespath="bsee";

string strsourcepath ="c:\\File\\abc.XLS";

string tablename="OTHER_MainPTJob";

string strsql="select id from [bsee].[dbo].[OTHER_MainPTJob]";

AppExportData tryclass=new AppExportData(strsourcepath,strdespath,tablename,strsql);

tryclass.ExportData();

导入类：

public class AppExportData

{

//DTS包对象

public DTS.Package2Class ObjDTS;

//源文件路径

private string strSourceFilePath;

//目标文件路径

private string strDestinationFilePath;

//导出数据的SQL语句

private string strSQL;

//目标表名

private string strTableName;

//得到目标列名

private ArrayList arrDestinationColumns;

//得到源数据列名

private ArrayList arrSourceColumns;

私有属性

公共属性

/// <summary>

/// 构造函数

/// </summary>

public AppExportData(string strsourcepath,string strdespath,string tablename,string strsql)

{

strTableName = "结果";

arrDestinationColumns = new ArrayList();

arrSourceColumns = new ArrayList();

strSourceFilePath=strsourcepath;

strDestinationFilePath=strdespath;

strTableName=tablename;

strSQL=strsql;

}

导出的全过程#region 导出的全过程

public bool ExportData()

{

try

{

ObjDTS = new DTS.Package2Class();

if ( this.arrDestinationColumns.Count == 0 || this.arrSourceColumns.Count == 0)

{

if ( !this.GetColumns() )

{

//showMessage( "没有获得数据,导出文件失败！");

return false;

}

//新建一个新的DTS包,设置它的属性

ObjDTS.Name = "新建包";

ObjDTS.Description = "DTS 包描述";

ObjDTS.WriteCompletionStatusToNTEventLog = false;

ObjDTS.FailOnError = false;

ObjDTS.PackagePriorityClass = ( DTS.DTSPackagePriorityClass )2;

ObjDTS.MaxConcurrentSteps = 4;

ObjDTS.LineageOptions = 0;

ObjDTS.UseTransaction = true;

ObjDTS.TransactionIsolationLevel = ( DTS.DTSIsolationLevel )4096;

ObjDTS.AutoCommitTransaction = true;

ObjDTS.RepositoryMetadataOptions = 0;

ObjDTS.UseOLEDBServiceComponents = true;

ObjDTS.LogToSQLServer = false;

ObjDTS.LogServerFlags = 0;

ObjDTS.FailPackageOnLogFailure = false;

ObjDTS.ExplicitGlobalVariables = false;

ObjDTS.PackageType = 0;

//建立SQL的连接,设置其属性

DTS.Connection2 oConnection;

//建立导出数据库的连接,设置其属性

oConnection = ( DTS.Connection2 )ObjDTS.Connections.New("Microsoft.Jet.OLEDB.4.0");

oConnection.ConnectionProperties.Item("Data Source").Value = this.strSourceFilePath;

oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;";

oConnection.Name = "连接2";

oConnection.ID = 1;

oConnection.Reusable = true;

oConnection.ConnectImmediate = false;

oConnection.DataSource = this.strSourceFilePath;

oConnection.ConnectionTimeout = 60;

oConnection.UseTrustedConnection = false;

oConnection.UseDSL = false;

//将其加入DTS包中

ObjDTS.Connections.Add( ( DTS.Connection )oConnection );

oConnection = null;

oConnection =(DTS.Connection2) ObjDTS.Connections.New("SQLOLEDB");

oConnection.ConnectionProperties.Item("Integrated Security").Value = "SSPI";

oConnection.ConnectionProperties.Item("Persist Security Info").Value = true;

oConnection.ConnectionProperties.Item("Initial Catalog").Value = this.strDestinationFilePath;

oConnection.ConnectionProperties.Item("Data Source").Value = "(local)";

oConnection.ConnectionProperties.Item("Application Name").Value = "DTS 导入/导出向导";

oConnection.Name = "连接1";

oConnection.ID = 2;

oConnection.Reusable = true;

oConnection.ConnectImmediate = false;

oConnection.DataSource = "(local)";

oConnection.ConnectionTimeout = 60;

oConnection.Catalog =this.strDestinationFilePath;

ObjDTS.Connections.Add( ( DTS.Connection)oConnection );

oConnection = null;

//设置DTS执行的步骤

DTS.Step2 oStep;

DTS.PrecedenceConstraint oPrecConstraint;

oStep = ( DTS.Step2 ) ObjDTS.Steps.New();

//创建表,设置属性

oStep.Name = "创建表 " + this.strTableName + " 步骤";

oStep.Description = "创建表 "+ this.strTableName + " 步骤";

oStep.ExecutionStatus = ( DTS.DTSStepExecStatus )1;

oStep.TaskName = "创建表 " + this.strTableName + " 任务";

oStep.CommitSuccess = false;

oStep.RollbackFailure = false;

oStep.ScriptLanguage = "VBScript";

oStep.AddGlobalVariables = true;

oStep.RelativePriority = ( DTS.DTSStepRelativePriority )3;

oStep.CloseConnection = false;

oStep.ExecuteInMainThread = false;

oStep.IsPackageDSORowset = false;

oStep.JoinTransactionIfPresent = false;

oStep.DisableStep = false;

oStep.FailPackageOnError = false;

//将其加入DTS包中

ObjDTS.Steps.Add( oStep );

oStep = null;

//设置数据导出的步骤 , 设置性属

oStep = ( DTS.Step2 )ObjDTS.Steps.New();

oStep.Name = "Copy Data from 结果 to " + this.strTableName + " 步骤";

oStep.Description = "Copy Data from 结果 to " + this.strTableName + " 步骤";

oStep.ExecutionStatus = ( DTS.DTSStepExecStatus )1;

oStep.TaskName = "Copy Data from 结果 to " + this.strTableName + " 任务";

oStep.CommitSuccess = false;

oStep.RollbackFailure = false;

oStep.ScriptLanguage = "VBScript";

oStep.AddGlobalVariables = true;

oStep.RelativePriority = ( DTS.DTSStepRelativePriority )3;

oStep.CloseConnection = false;

oStep.ExecuteInMainThread = true;

oStep.IsPackageDSORowset = false;

oStep.JoinTransactionIfPresent = false;

oStep.DisableStep = false;

oStep.FailPackageOnError = false;

//将其添加到DTS包中

ObjDTS.Steps.Add( oStep );

oStep = null;

//设置生成表的步骤

oStep = ( DTS.Step2 )ObjDTS.Steps.Item("Copy Data from 结果 to " + this.strTableName + " 步骤");

oPrecConstraint = oStep.PrecedenceConstraints.New("创建表 " + this.strTableName + " 步骤");

oPrecConstraint.StepName = "创建表 " + this.strTableName + " 步骤";

oPrecConstraint.PrecedenceBasis = 0;

oPrecConstraint.Value = 4;

oStep.PrecedenceConstraints.Add( oPrecConstraint );

oPrecConstraint = null;

//this.Task_Sub1(ObjDTS);

this.Task_Sub2(ObjDTS);

//执行导出数据

ObjDTS.Execute();

tracePackageError( ObjDTS );

return true;

}

catch (Exception ex)

{

return false;

}

finally

{

ObjDTS.UnInitialize();

System.Runtime.InteropServices.Marshal.ReleaseComObject( ObjDTS );

ObjDTS = null;

GC.Collect();

this.arrDestinationColumns.Clear();

this.arrSourceColumns.Clear();

}

private void tracePackageError( DTS.Package2Class oPackage)

{

int ErrorCode;

string ErrorSource;

string ErrorDescription;

string ErrorHelpFile;

int ErrorHelpContext;

string ErrorIDofInterfaceWithError;

for( int i = 1 ; i < oPackage.Steps.Count ; i ++ )