SQL: SSIS的配置文件设置以及ssis包在web中的调用

private string SSISMatoMc()
    {
        ExecPackage ep = new ExecPackage();
        ep.SavePackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
        return ep.Excutepackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
    }
ssis 包Pmatomc.dtsx 作用是把mA数据库中的表同步到mC数据库中
这样在配置文件Cmatomc.dtsConfig中至少要包括以下几个参数：数据库名称、连接字符串、连接密码。这样才能在不同的环境中使用（当然你还可以添加需要同步的表名到参数里）

<?xml version="1.0"?>
<DTSConfiguration>
  <DTSConfigurationHeading>
    <DTSConfigurationFileInfo GeneratedBy="computername" GeneratedFromPackageName="Pmatomc" GeneratedFromPackageID="{92A86513-2B8F-43D7-BDF2-1A2E3060D0ED}" GeneratedDate="2008-11-4 15:51:03"/>
  </DTSConfigurationHeading>
 
  <!--mA configuration is below-->
  <Configuration ConfiguredType="Property" Path="/Package/Transfer SQL Server Objects Task.Properties[SourceDatabase]" ValueType="String">
    <ConfiguredValue>PRD_PPP_MA</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="/Package.Connections[localhost.sa].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>SqlServerName=localhost;UseWindowsAuthentication=False;UserName=sa;</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="/Package.Connections[localhost.sa].Properties[Password]" ValueType="String">
    <ConfiguredValue>sa</ConfiguredValue>
  </Configuration>

  <!--mC configuration is below-->
  <Configuration ConfiguredType="Property" Path="/Package/Transfer SQL Server Objects Task.Properties[DestinationDatabase]" ValueType="String">
    <ConfiguredValue>PRD_PPP_MC</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="/Package.Connections[CNPRDWEBS02/CNDEV01.sa].Properties[ConnectionString]" ValueType="String">
    <ConfiguredValue>SqlServerName=CNPRDWEBS02/CNDEV01;UseWindowsAuthentication=False;UserName=sa;</ConfiguredValue>
  </Configuration>
  <Configuration ConfiguredType="Property" Path="/Package.Connections[CNPRDWEBS02/CNDEV01.sa].Properties[Password]" ValueType="String">
    <ConfiguredValue>sa</ConfiguredValue>
  </Configuration>

</DTSConfiguration>
调用类

/// <summary>
/// Summary description for ExecPackage
/// </summary>
public class ExecPackage
{
    /// <summary>
    /// 为包添加配置文件
    /// </summary>
    /// <param name="PackageName"></param>
    /// <param name="DtsxName"></param>
    /// <returns></returns>
    public bool SavePackage(string PackageName,string DtsxName)
    {
        //新建dts执行程序
        Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
        //新建一个包
        Package pack = new Package();
        try
        {
            string PackagePath = System.Web.HttpContext.Current.Server.MapPath("") +"//"+PackageName;
            pack = app.LoadPackage(PackagePath, null);//加载包
            if (pack.Configurations.Contains("DTSConn")) pack.Configurations.Remove("DTSConn");
            //动态设置包的配置文件路径
            pack.EnableConfigurations = true;                                                          //启用包配置
            Microsoft.SqlServer.Dts.Runtime.Configuration conf = pack.Configurations.Add();            //新建一个配置
            conf.ConfigurationString = System.Web.HttpContext.Current.Server.MapPath("") +"//"+ DtsxName; //设置配置文件的值为已存在的配置文件xml的路径
            conf.ConfigurationType = DTSConfigurationType.ConfigFile;                                  //设置配置文件读取方式为xml文件
            conf.PackagePath = PackagePath;                                                            //设置被配置的SSIS包路径。
            conf.Name = "DTSConn";                                                                     //设置此包的名称。
            app.SaveToXml(PackagePath, pack, null);                                                    //保存SSIS,最关键的一步
            return true;
        }
        catch
        {
            return false;
        }
    }
    //运行DTS包
    /// <summary>
    /// 运行DTS包
    /// </summary>
    /// <param name="path">保存备份文件的路径</param>
    /// <param name="CreateTime">备份时间</param>
    /// <param name="Fname">备份文件文件名</param>
    public string Excutepackage(string PackageName, string DtsxName)
    {
        //新建dts执行程序
        Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
        //新建一个包
        Package pack = new Package();
        string message = "";
        try
        {
            //加载一个存在的包
            string PackagePath = System.Web.HttpContext.Current.Server.MapPath("") + "//" + PackageName;//"//Package_mAtomC.dtsx";
            pack = app.LoadPackage(PackagePath, null);//加载包
            //给变量赋值
            //pack.Variables["BeginTime"].Value = BeginTime;
            //pack.Variables["EndTime"].Value = EndTime;
            //pack.Variables["BakFilesPath"].Value = path;
            //执行包
            DTSExecResult result = pack.Execute();
          
            //捕捉错误        
            if (result.Equals(DTSExecResult.Failure))
            {
                for (int i = 0; i < pack.Errors.Count; i++)
                {
                    message += pack.Errors[i].Description;
                }
            }
            else message = result.ToString();
        }
        catch (Exception ex)
        {
            message = ex.ToString();
        }
        return message;
    }
}

web页面中调用：

  private string SSISMatoMc()
    {
        ExecPackage ep = new ExecPackage();
        ep.SavePackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
        return ep.Excutepackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
    }

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lekelaier_msn/archive/2008/11/26/3380924.aspx