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