- private string SSISMatoMc()
- {
- ExecPackage ep = new ExecPackage();
- ep.SavePackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
- return ep.Excutepackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
- }
这样在配置文件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;
- }
- }
- private string SSISMatoMc()
- {
- ExecPackage ep = new ExecPackage();
- ep.SavePackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
- return ep.Excutepackage("Pmatomc.dtsx", "Cmatomc.dtsConfig");
- }