DTS导入的问题

不好意思先放下，解决了就撤下去
我在利用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;

            私有属性#region 私有属性
            private ArrayList SourceColumns
            {
                get
                {
                    return arrSourceColumns;
                }
                set
                {
                    arrSourceColumns = value;
                }
            }

            /**//// <summary>
            /// 得到目标列名
            /// </summary>
            private ArrayList DestinationColumns
            {
                get 
                {
                    return arrDestinationColumns;
                }
                set 
                {
                    arrDestinationColumns = value;
                }
            }
            #endregion

            公共属性#region 公共属性
            /**//// <summary>
            /// 目标表名
            /// </summary>
            public string TableName
            {
                get
                {
                    return strTableName;
                }
                set 
                {
                    strTableName = value;
                }
            }
            /**//// <summary>
            /// 导出数据的SQL语句
            /// </summary>
            public string SQL
            {
                get
                {
                    return strSQL;
                }
                set 
                {
                    strSQL = value;
                }
            }

            /**//// <summary>
            /// 源文件所在的路径
            /// </summary>
            public string SourceFilePath
            {
                get
                {
                    return strSourceFilePath;
                }

                set
                {
                    strSourceFilePath = value;
                }
            }
            /**//// <summary>
            /// 目标文件所在的路径
            /// </summary>
            public string DestinationFilePath
            {
                get
                {
                    return strDestinationFilePath;
                }

                set
                {
                    strDestinationFilePath = value;
                }
            }
            #endregion

            /**//// <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 ++ )                
                {
                    if ( oPackage.Steps.Item(i).ExecutionResult == DTS.DTSStepExecResult.DTSStepExecResult_Failure )
                    {
                        oPackage.Steps.Item(i).GetExecutionErrorInfo(out ErrorCode,out ErrorSource,out ErrorDescription,out ErrorHelpFile,out ErrorHelpContext,out ErrorIDofInterfaceWithError);
                        //添加抛出异常语句
                    }
                }
            } 

            /**//// <summary>
            /// 创建表
            /// </summary>
            /// <param name="goPackage">DTS包对象</param>
            private void Task_Sub1( DTS.Package2Class ObjDTS )
            {
                DTS.Task oTask;
   
                DTS.ExecuteSQLTask2 oCustomTask1;
                oTask = ObjDTS.Tasks.New("DTSExecuteSQLTask");
                oTask.Name = "创建表 " + this.strTableName + " 任务";
                oCustomTask1 = ( DTS.ExecuteSQLTask2) oTask.CustomTask;

                oCustomTask1.Name = "创建表 " + this.strTableName + " 任务";
                oCustomTask1.Description = "创建表 " + this.strTableName + " 任务";
                oCustomTask1.SQLStatement = "CREATE TABLE `" + this.strTableName + "` (" + "\n";

                //arrDestinationColumns[0]="管理号";
                for( int i = 0 ; i < this.arrDestinationColumns.Count ; i ++ )
                {
                    if ( i == this.arrDestinationColumns.Count - 1 )
                        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + "[" + this.arrDestinationColumns[i].ToString() +"]  VarChar(50) " + "\n";
                    else
                        oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + "[" + this.arrDestinationColumns[i].ToString() +"]  VarChar(50), " + "\n";
                }
   
                oCustomTask1.SQLStatement = oCustomTask1.SQLStatement + ")";
                oCustomTask1.ConnectionID = 2;
                oCustomTask1.CommandTimeout = 0;
                oCustomTask1.OutputAsRecordset = false;
 
                ObjDTS.Tasks.Add( oTask );
                oCustomTask1 = null;
                oTask = null;
            }

            /**//// <summary>
            /// 执行导出的SQL语句
            /// </summary>
            /// <param name="ObjDTS">DTS包对象</param>
            private void Task_Sub2( DTS.Package2Class ObjDTS )
            {
                DTS.Task oTask;
   
                DTS.DataPumpTask2 oCustomTask2;
                oTask = ObjDTS.Tasks.New("DTSDataPumpTask");
                oTask.Name = "Copy Data from 结果 to " + this.strTableName + " 任务";
                oCustomTask2 = (DTS.DataPumpTask2 )oTask.CustomTask;

                oCustomTask2.Name = "Copy Data from 结果 to " + this.strTableName + " 任务";
                oCustomTask2.Description = "Copy Data from 结果 to " + this.strTableName + " 任务";
                oCustomTask2.SourceConnectionID = 1;
                oCustomTask2.SourceSQLStatement = this.strSQL;   
                oCustomTask2.DestinationConnectionID = 2;                
                oCustomTask2.DestinationObjectName =  "[bsee].[dbo].["+this.strTableName+"]";                
                oCustomTask2.ProgressRowCount = 1000;
                oCustomTask2.MaximumErrorCount = 0;
                oCustomTask2.FetchBufferSize = 1;
                oCustomTask2.UseFastLoad = true;
                oCustomTask2.InsertCommitSize = 0;
                oCustomTask2.ExceptionFileColumnDelimiter = "|";
                oCustomTask2.ExceptionFileRowDelimiter = @"\n";
                oCustomTask2.AllowIdentityInserts = false;
                oCustomTask2.FirstRow = 0;
                oCustomTask2.LastRow = 0;
                oCustomTask2.FastLoadOptions = ( DTS.DTSFastLoadOptions )2;
                oCustomTask2.ExceptionFileOptions = ( DTS.DTSExceptionFileOptions )1;
                oCustomTask2.DataPumpOptions = 0;
 
                this.oCustomTask2_Trans_Sub1( oCustomTask2 );
  
  
                ObjDTS.Tasks.Add( oTask );
                oCustomTask2 = null;
                oTask = null;

            }

            /**//// <summary>
            /// 设置导出的源列和目标列的对应关系
            /// </summary>
            /// <param name="oCustomTask2"></param>
            private void oCustomTask2_Trans_Sub1( DTS.DataPumpTask2 oCustomTask2 )
            {
                
                DTS.Transformation2 oTransformation;
                DTS.Properties oTransProps;
                DTS.Column oColumn;
                oTransformation = ( DTS.Transformation2 )oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy");
                oTransformation.Name = "DirectCopyXform";
                oTransformation.TransformFlags = 63;
                oTransformation.ForceSourceBlobsBuffered = ( DTS.DTSForceMode )1;
                oTransformation.ForceBlobsInMemory = false;
                oTransformation.InMemoryBlobSize = 1048576;
                oTransformation.TransformPhases = 4;
  
                
                oColumn = oTransformation.SourceColumns.New("id" , 1);
                oColumn.Name = "id";
                oColumn.Ordinal = 1;
                oColumn.Flags = 102;
                oColumn.Size = 255;
                oColumn.DataType = 130;
                oColumn.Precision = 0;
                oColumn.NumericScale = 0;
                oColumn.Nullable = true;
            
                oTransformation.SourceColumns.Add( oColumn );
                oColumn = null;
    
        

                oColumn = oTransformation.DestinationColumns.New("id" , 1);
                oColumn.Name = "id";
                oColumn.Ordinal = 1;
                oColumn.Flags = 24;
                oColumn.Size = 10;
                oColumn.DataType = 129;
                oColumn.Precision = 0;
                oColumn.NumericScale = 0;
                oColumn.Nullable = false;
            
                oTransformation.DestinationColumns.Add( oColumn );
                oColumn = null;
                oTransProps = oTransformation.TransformServerProperties;
                oTransProps = null;

                oCustomTask2.Transformations.Add( oTransformation ); 
                oTransformation = null;
            }
            #endregion

            /**//// <summary>
            /// 在SQL语句中得到目标表的列名 , 本来是想根据SQL语句来决定列名,没有找到高效的分解SQL语句的方法。
            /// </summary>
            private bool GetColumns()
            {
                try
                {    
                    SqlConnection conn=new SqlConnection("server=.;uid=sa;pwd=;database=bsee");
                    SqlDataAdapter da=new SqlDataAdapter(strSQL,conn);
                    DataSet ds = new DataSet();    
                    da.Fill(ds,"qqq");                                    
                    for ( int i = 0; i < ds.Tables[0].Columns.Count; i++ )
                    {
                        this.arrDestinationColumns.Add(ds.Tables[0].Columns[i].ColumnName );
                        this.arrSourceColumns.Add(ds.Tables[0].Columns[i].ColumnName);
                    }
                    return true;
                }
                catch 
                {
                    return false;
                }
            } 
        }

数据库名“bsee”，表名：OTHER_MainPTJob，表内只有一个字段：id

转载于:https://www.cnblogs.com/gjahead/archive/2007/04/17/717295.html