我在利用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)
                    

    
                    
//新建一个新的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;                               
                
forint 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]="管理号";
                forint 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
posted on 2007-05-10 10:38  过江  阅读(609)  评论(3编辑  收藏  举报