关闭

写一个在程序中创建dts包,实现数据用dts导入到sql的类。

1029人阅读 评论(0) 收藏 举报

 

//备注,从getItemToItem()取的字段对应,getSourceTableName()取得表关系对应。


using System;
using System.Data;
using System.Data.SqlClient;

namespace Import
{
 /// <summary>
 /// 导入学生中考成绩DTS包
 /// </summary>
 public class ImportStuGrade
 {
  //DTS包对象
  public DTS.Package2Class goPackage = new DTS.Package2Class();
  //要导入的文件路径
  private string strFilePath;
  //机构ID
  private string strOrgID;
  //SQL服务器地址
  private string strSQLPath;
  //SQL登录用户名
  private string strSQLUserID;
  //SQL登录密码
  private string strSQLPassword;
  //SQL数据库名
  private string strDataBaseName;
  //表对应关系ID
  private string strRelationID;
  //要导入的表名
  private string strSourceTableName;
  
  /// <summary>
  /// 要导入的文件路径
  /// </summary>
  public string FilePath
  {
   get
   {
    return strFilePath;
   }

   set
   {
    strFilePath = value;
   }
  }
  
  /// <summary>
  /// //机构ID
  /// </summary>
  public string OrgID
  {
   get
   {
    return strOrgID;
   }

   set
   {
    strOrgID = value;
   }
  }
  
  /// <summary>
  /// SQL服务器地址
  /// </summary>
  public string SQLPath
  {
   get
   {
    return strSQLPath;
   }

   set
   {
    strSQLPath = value;
   }
  }
  
  /// <summary>
  /// SQL登录用户名
  /// </summary>
  public string SQLUserID
  {
   get
   {
    return strSQLUserID;
   }

   set
   {
    strSQLUserID = value;
   }
  }
  
  /// <summary>
  /// SQL登录密码
  /// </summary>
  public string SQLPassword
  {
   get
   {
    return strSQLPassword;
   }

   set
   {
    strSQLPassword = value;
   }
  }

  /// <summary>
  /// SQL数据库名
  /// </summary>
  public string DataBaseName
  {
   get
   {
    return strDataBaseName;
   }

   set
   {
    strDataBaseName = value;
   }
  }
  
  /// <summary>
  /// 表对应关系ID
  /// </summary>
  public string RelationID
  {
   get
   {
    return strRelationID;
   }

   set
   {
    strRelationID = value;
   }
  }
  
  /// <summary>
  /// 要导入的源表名
  /// </summary>
  public string SourceTableName
  {
   get
   {
    return strSourceTableName;
   }

   set
   {
    strSourceTableName = value;
   }
  }
  
  
  public ImportStuGrade()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  /// <summary>
  /// 导入学生中考信息
  /// </summary>
  public void ImportStuGradeToDB()
  {
   try
   {
    //设置包属性
    goPackage.Name = "ImportStuGradeToDB";               
    goPackage.Description = "导入中考成绩信息DTS包";
    goPackage.WriteCompletionStatusToNTEventLog = false;
    goPackage.FailOnError = true;
   
    goPackage.PackagePriorityClass = (DTS.DTSPackagePriorityClass)2;
    goPackage.MaxConcurrentSteps = 4;
    goPackage.LineageOptions = 0;
    goPackage.UseTransaction = true;
  
    goPackage.TransactionIsolationLevel = (DTS.DTSIsolationLevel)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;
    
    //创建连接用户数据文件的连接
    DTS.Connection2 oConnection;
    
    //截取文件名后缀名
    string strFileFix;
    strFileFix = this.strFilePath.Substring(this.strFilePath.Length-3);
    strFileFix = strFileFix.ToUpper();
    oConnection = null;
    //根据文件后缀名判断文件类型,并根据文件类型来定义连接的类型
    if (strFileFix == "MDB")
    {
     oConnection = (DTS.Connection2)goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0");
     oConnection.ConnectionProperties.Item("Mode").Value = 1;
    }
    else if ( strFileFix == "XLS" )
    {
     oConnection = (DTS.Connection2)goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0");
     oConnection.ConnectionProperties.Item("Extended Properties").Value = "Excel 8.0;HDR=YES;";
    }
    else if ( strFileFix == "MDC" )
    {
     oConnection = (DTS.Connection2)goPackage.Connections.New("vfpoledb.1");
    }
    
    //设置连接属性
    oConnection.ConnectionProperties.Item("Data Source").Value = this.strFilePath;
   
    oConnection.Name = "连接1";
    oConnection.ID = 1;
    oConnection.Reusable = true;
    oConnection.ConnectImmediate = false;
    oConnection.DataSource = this.strFilePath;
    oConnection.ConnectionTimeout = 60;
    oConnection.UseTrustedConnection = false;
    oConnection.UseDSL = false;
  
    goPackage.Connections.Add((DTS.Connection)oConnection);
    oConnection = null;
    
    //创建连接SQL数据库的连接
    oConnection = (DTS.Connection2)goPackage.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.strDataBaseName;
    oConnection.ConnectionProperties.Item("Data Source").Value = this.strSQLPath;
    oConnection.ConnectionProperties.Item("Application Name").Value = "DTS 导入/导出向导";
   
    oConnection.Name = "连接2";
    oConnection.ID = 2;
    oConnection.Reusable = true;
    oConnection.ConnectImmediate = false;
    oConnection.DataSource = this.strSQLPath;
    oConnection.UserID = this.strSQLUserID;
    oConnection.Password = this.strSQLPassword;
    oConnection.ConnectionTimeout = 60;
    oConnection.Catalog = this.strDataBaseName;
    oConnection.UseTrustedConnection = false;
    oConnection.UseDSL = false;
   

    goPackage.Connections.Add((DTS.Connection)oConnection);
    oConnection = null;
    
    //定义步骤对象
    DTS.Step2 oStep;
  
    oStep = (DTS.Step2)goPackage.Steps.New();

    //设置步骤属性
    oStep.Name = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 步骤";
    oStep.Description = "Copy Data from student to [" + this.strDataBaseName + "].[dbo].[StuGrade] 步骤";
    oStep.ExecutionStatus = (DTS.DTSStepExecStatus)1;
   
    oStep.TaskName = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 任务";
    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;
    goPackage.Steps.Add(oStep);
    oStep = null;
    Task_Sub1(goPackage);
  
    //保存包
 //   object objStg;
 //   objStg = "dfd";
 //   goPackage.SaveToSQLServer("192.168.0.7", "sa", "",DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","",ref objStg,true);

    //执行包
    goPackage.Execute();
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    //在内存中卸载DTS包
    goPackage.UnInitialize();
    goPackage = null;
   }
  
 

  }

  /// <summary>
  /// 具体定义DTS包中的任务
  /// </summary>
  /// <param name="goPackage">包对象</param>
  private void Task_Sub1(DTS.Package2Class goPackage)
  {
   //任务对象
   DTS.Task oTask;

   DTS.DataPumpTask2 oCustomTask1;
   oTask = goPackage.Tasks.New("DTSDataPumpTask");
   oCustomTask1 = (DTS.DataPumpTask2)oTask.CustomTask;

   //任务的名称
   oCustomTask1.Name = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 任务";
   //任务的描述
   oCustomTask1.Description = "Copy Data from StuGrade to [" + this.strDataBaseName + "].[dbo].[StuGrade] 任务";
   //任务用来连接源数据文件的连接ID
   oCustomTask1.SourceConnectionID = 1;
   //设置查询源数据文件表中的数据的SQL语句
   oCustomTask1.SourceSQLStatement = "select ";

   DataTable dtabItemToItem;
   dtabItemToItem = getItemToItem();
   for ( int intLoop = 0 ; intLoop < dtabItemToItem.Rows.Count ; intLoop++ )
   {
    oCustomTask1.SourceSQLStatement += "`" + dtabItemToItem.Rows[intLoop]["SourceItem"] + "`,";
   }
   
   oCustomTask1.SourceSQLStatement = oCustomTask1.SourceSQLStatement.Substring(0,oCustomTask1.SourceSQLStatement.Length-1);
   if ( this.strSourceTableName =="" || this.strSourceTableName == null )
   {
    this.strSourceTableName = this.getSourceTableName();
   }
   oCustomTask1.SourceSQLStatement += " from `" + this.strSourceTableName + "`";
   //任务用来连接目标数据的连接ID
   oCustomTask1.DestinationConnectionID = 2;
   //任务用到的目标数据库名称
   oCustomTask1.DestinationObjectName = "[" + this.strDataBaseName + "].[dbo].[StuGrade]";
   oCustomTask1.ProgressRowCount = 1000;
   oCustomTask1.MaximumErrorCount = 0;
   oCustomTask1.FetchBufferSize = 1;
   oCustomTask1.UseFastLoad = true;
   oCustomTask1.InsertCommitSize = 0;
   oCustomTask1.ExceptionFileColumnDelimiter = "|";
   oCustomTask1.ExceptionFileRowDelimiter = @"/n";
   oCustomTask1.AllowIdentityInserts = false;
   oCustomTask1.FirstRow = 0;
   oCustomTask1.LastRow = 0;
   oCustomTask1.FastLoadOptions = (DTS.DTSFastLoadOptions)2;
   oCustomTask1.ExceptionFileOptions = (DTS.DTSExceptionFileOptions)1;
   oCustomTask1.DataPumpOptions = 0;
       
   oCustomTask1_Trans_Sub1(oCustomTask1);
               
               
   goPackage.Tasks.Add(oTask);
   oCustomTask1 = null;
   oTask = null;

  }
  
  /// <summary>
  /// 具体的任务中的对应关系设置
  /// </summary>
  /// <param name="oCustomTask1">任务对象</param>
  private void oCustomTask1_Trans_Sub1(DTS.DataPumpTask2 oCustomTask1)
  {

   DTS.Transformation2 oTransformation ;
   DTS.Properties oTransProps;
   //定义一个列对象
   DTS.Column oColumn;
   oTransformation = (DTS.Transformation2)oCustomTask1.Transformations.New("DTS.DataPumpTransformScript");
   oTransformation.Name = "AxScriptXform";
   oTransformation.TransformFlags = 63;
   oTransformation.ForceSourceBlobsBuffered = 0;
   oTransformation.ForceBlobsInMemory = false;
   oTransformation.InMemoryBlobSize = 1048576;
   oTransformation.TransformPhases = 4;

   #region 定义目标表中的列,目标表的列必须定义,源表列可不定义
   oColumn = oTransformation.DestinationColumns.New("StuTestID", 1);
   oColumn.Name = "StuTestID";
   oColumn.Ordinal = 1;
   oColumn.Flags = 104;
   oColumn.Size = 16;
   oColumn.DataType = 130;
   oColumn.Precision = 0;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Chinese", 2);
   oColumn.Name = "Chinese";
   oColumn.Ordinal = 2;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Maths", 3);
   oColumn.Name = "Maths";
   oColumn.Ordinal = 3;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("English", 4);
   oColumn.Name = "English";
   oColumn.Ordinal = 4;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
         
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Politics", 5);
   oColumn.Name = "Politics";
   oColumn.Ordinal = 5;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Physics", 6);
   oColumn.Name = "Physics";
   oColumn.Ordinal = 6;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Chemistry", 7);
   oColumn.Name = "Chemistry";
   oColumn.Ordinal = 7;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Biology", 8);
   oColumn.Name = "Biology";
   oColumn.Ordinal = 8;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("History", 9);
   oColumn.Name = "History";
   oColumn.Ordinal = 9;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Geography", 10);
   oColumn.Name = "Geography";
   oColumn.Ordinal = 10;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Integration1", 11);
   oColumn.Name = "Integration1";
   oColumn.Ordinal = 11;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Integration2", 12);
   oColumn.Name = "Integration2";
   oColumn.Ordinal = 12;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("ShtickSubject1", 13);
   oColumn.Name = "ShtickSubject1";
   oColumn.Ordinal = 13;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("ShtickSubject2", 14);
   oColumn.Name = "ShtickSubject2";
   oColumn.Ordinal = 14;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
   
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Standby1", 15);
   oColumn.Name = "Standby1";
   oColumn.Ordinal = 15;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Standby2", 16);
   oColumn.Name = "Standby2";
   oColumn.Ordinal = 16;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Standby3", 17);
   oColumn.Name = "Standby3";
   oColumn.Ordinal = 17;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
          
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;

   oColumn = oTransformation.DestinationColumns.New("Standby4", 18);
   oColumn.Name = "Standby4";
   oColumn.Ordinal = 18;
   oColumn.Flags = 120;
   oColumn.Size = 0;
   oColumn.DataType = 131;
   oColumn.Precision = 18;
   oColumn.NumericScale = 0;
   oColumn.Nullable = true;
   
   oTransformation.DestinationColumns.Add(oColumn);
   oColumn = null;
   #endregion

   oTransProps = oTransformation.TransformServerProperties;
   #region 用VB脚本设置字段对应关系
   oTransProps.Item("Text").Value = "'**********************************************************************" + "/n";
   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "'  Visual Basic Transformation Script" + "/n";
   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "'  Copy each source column to the" + "/n";
   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "'  destination column" + "/n";
   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "'************************************************************************" + "/n";
   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "Function Main()" + "/n";
   
   
   DataTable dtabItemToItem;
   dtabItemToItem = getItemToItem();
               
   for ( int intLoop = 0 ; intLoop < dtabItemToItem.Rows.Count ; intLoop++ )
   {
    oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "   DTSDestination(/"" + dtabItemToItem.Rows[intLoop]["TargetItem"] + "/") = DTSSource(/"" + dtabItemToItem.Rows[intLoop]["SourceItem"] + "/")" + "/n";
   }

   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "   Main = DTSTransformStat_OK" + "/n";
   oTransProps.Item("Text").Value = oTransProps.Item("Text").Value + "End Function";
   oTransProps.Item("Language").Value = "VBScript";
   oTransProps.Item("FunctionEntry").Value = "Main";
            #endregion
   oTransProps = null;
   oCustomTask1.Transformations.Add(oTransformation);
   oTransformation = null;

  }

  /// <summary>
  /// 获得字段对应关系记录
  /// </summary>
  /// <returns>字段对应关系的DataTabl</returns>
  private DataTable getItemToItem()
  {
   string strSQL;
   string strSQLConnectionString = "server=" + this.strSQLPath + ";uid=" + this.strSQLUserID + ";pwd=" + this.strSQLPassword + ";database=" + this.strDataBaseName;
   strSQL = "select * from ImportItemToItem where RelationID='" + this.strRelationID + "'";
   SqlConnection sqlcon = new SqlConnection(strSQLConnectionString);
   
   SqlDataAdapter sqlda = new SqlDataAdapter(strSQL,sqlcon);
   DataSet ds = new DataSet();
   sqlda.Fill(ds,"ImportItemToItem");   
   return ds.Tables["ImportItemToItem"];
  }
  
  /// <summary>
  /// 获得用户表的表名
  /// </summary>
  /// <returns>用户表的表名</returns>
  private string getSourceTableName()
  {
   string strSQL;
   string strSQLConnectionString = "server=" + this.strSQLPath + ";uid=" + this.strSQLUserID + ";pwd=" + this.strSQLPassword + ";database=" + this.strDataBaseName;
   strSQL = "select SourceTable from ImportTableToTable where SrNo='" + this.strRelationID + "'";
   SqlConnection sqlcon = new SqlConnection(strSQLConnectionString);
   
   SqlDataAdapter sqlda = new SqlDataAdapter(strSQL,sqlcon);
   DataSet ds = new DataSet();
   sqlda.Fill(ds,"ImportTableToTable");   
   return ds.Tables["ImportTableToTable"].Rows[0]["SourceTable"].ToString().Trim();
  }


 }
}

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1338次
    • 积分:22
    • 等级:
    • 排名:千里之外
    • 原创:1篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章存档