SQLSERVER的数据转换服务DTS的调用代码

最新推荐文章于 2018-11-23 14:50:00 发布

原创最新推荐文章于 2018-11-23 14:50:00 发布 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sqlserver #exception #string #class #url #object

本文介绍了几种执行DTS包的方法，包括从本地路径加载并执行、从SQL Server上加载并执行，同时也支持传递全局变量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

/// <summary>
/// url是包的全路径，packageName是包名；该方法抛出异常;执行的是DTS的结构化文件

  /// </summary>
  /// <param name="url">包的全路径</param>
  /// <param name="packageName">包名</param>
  /// <returns></returns>
  public static void   ExceDTSPackage(string url,string packageName)
  {
   DTS.Package2Class package = new DTS.Package2Class();
   try
   {

    Object MIA=System.Reflection.Missing.Value;
    package.LoadFromStorageFile(url,"","","",packageName,ref MIA);
    package.Execute();

   }

   catch(Exception e)
   {
    throw new Exception("执行“"+url+"”失败："+e.Message);
   }
   finally
   {
    package.UnInitialize();
   }

}

  /// <summary>
  /// DTS需要传入全局变量的,当然如果你原来设计的包没有全局变量,也不会影响到包执行的正确性.
  /// </summary>
  /// <param name="url"></param>
  /// <param name="packageName"></param>
  /// <param name="gdjdm"></param>
  /// <param name="bdzdm"></param>
  public static void   ExceDTSPackage(string url,string packageName,String bdzid_scmis,String bdzid_ztpj)
  {
   DTS.Package2Class package = new DTS.Package2Class();
   try
   {

    Object MIA=System.Reflection.Missing.Value;
    package.LoadFromStorageFile(url,"","","",packageName,ref MIA);
    package.GlobalVariables.Item("bdzid_scmis").let_Value(bdzid_scmis);
    package.GlobalVariables.Item("bdzid_ztpj").let_Value(bdzid_ztpj);
    package.SaveToStorageFile(url,"","",ref MIA,false);
    package.Execute();

   }

   catch(Exception e)
   {
    throw new Exception("执行“"+url+"”失败："+e.Message);
   }
   finally
   {
    package.UnInitialize();
   }

}

  /// <summary>
  /// 执行SQLSERVER上的DTS包
  /// </summary>
  /// <param name="ip"></param>
  /// <param name="userid"></param>
  /// <param name="pswd"></param>
  /// <param name="packagename"></param>
  public static void ExceDTSSqlServer(string ip,string userid,string pswd,string packagename)
  {
   DTS.Package2Class package = new DTS.Package2Class();
   try
   {

    Object MIA = null;
    package.LoadFromSQLServer(ip,userid,pswd,DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","",packagename,ref MIA);
    package.Execute();


   }
   catch(Exception e)

   {
    throw new Exception("执行服务器“"+ip+"”上的包“"+packagename+"”时失败："+e.Message);
   }
   finally
   {
    package.UnInitialize();
   }

}

  /// <summary>
  /// DTS需要传入全局变量的
  /// </summary>
  /// <param name="ip"></param>
  /// <param name="userid"></param>
  /// <param name="pswd"></param>
  /// <param name="packagename"></param>
  /// <param name="gdjdm"></param>
  /// <param name="bdzdm"></param>
  public static void ExceDTSSqlServer(string ip,string userid,string pswd,string packagename,string bdzid_scmis,string bdzid_ztpj)
  {
   DTS.Package2Class package = new DTS.Package2Class();
   try
   {

    Object MIA = null;
    package.LoadFromSQLServer(ip,userid,pswd,DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","",packagename,ref MIA);
    package.GlobalVariables.Item("bdzid_scmis").let_Value(bdzid_scmis);
    package.GlobalVariables.Item("bdzid_ztpj").let_Value(bdzid_ztpj);
    package.SaveToSQLServer(ip,userid,pswd,DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,"","","",ref MIA,false);
    package.Execute();


   }
   catch(Exception e)