/// <summary>
/// Exports data by ssis.
/// </summary>
/// <param name="ssisPath">package full name(including path)</param>
/// <param name="excelFullName">excel full name(including path)</param>
public void ExportDataBySSIS(string ssisPath, string excelFullName)
{
#region Old logic
//Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//Package package = app.LoadPackage(ssisPath, null);
//string excelDest = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;HDR=YES\";", excelFullName);
//Microsoft.SqlServer.Dts.Runtime.Connections conns = package.Connections;
////package.
//package.Connections["DestinationConnectionExcel"].ConnectionString = excelDest;
//DTSExecResult r = package.Execute();
#endregion
//Add an local DLL
//DLL reference path: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
//DLL Name: Microsoft.SQLServer.DTSRuntimeWrap.dll
//packageErrorMsg = string.Empty;
DtsRunTime.Application app = new DtsRunTime.Application();
DtsRunTime.IDTSPackage100 package = app.LoadPackage(ssisPath, false, null);
string excelDest = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;HDR=YES\";", excelFullName);
package.Connections["DestinationConnectionExcel"].ConnectionString = excelDest;
PackageEvenet pevent = new PackageEvenet();
DtsRunTime.DTSExecResult result = package.Execute(null, null, pevent, null, null);
if (DtsRunTime.DTSExecResult.DTSER_FAILURE == result)
{
//MessageBox.Show("SSIS Run Failed and error message is " + packageErrorMsg);
}
}
/// <summary>
/// Exports data by ssis.
/// </summary>
/// <param name="ssisPath">package full name(including path)</param>
/// <param name="excelFullName">excel full name(including path)</param>
public void ExportDataBySSIS(string ssisPath, string excelFullName)
{
#region Old logic
//Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//Package package = app.LoadPackage(ssisPath, null);
//string excelDest = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;HDR=YES\";", excelFullName);
//Microsoft.SqlServer.Dts.Runtime.Connections conns = package.Connections;
////package.
//package.Connections["DestinationConnectionExcel"].ConnectionString = excelDest;
//DTSExecResult r = package.Execute();
#endregion
//Add an local DLL
//DLL reference path: C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies
//DLL Name: Microsoft.SQLServer.DTSRuntimeWrap.dll
//packageErrorMsg = string.Empty;
DtsRunTime.Application app = new DtsRunTime.Application();
DtsRunTime.IDTSPackage100 package = app.LoadPackage(ssisPath, false, null);
string excelDest = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;HDR=YES\";", excelFullName);
package.Connections["DestinationConnectionExcel"].ConnectionString = excelDest;
PackageEvenet pevent = new PackageEvenet();
DtsRunTime.DTSExecResult result = package.Execute(null, null, pevent, null, null);
if (DtsRunTime.DTSExecResult.DTSER_FAILURE == result)
{
//MessageBox.Show("SSIS Run Failed and error message is " + packageErrorMsg);
}
}
// Saves a new package to file
internal string SavePackage()
{
// Creates DTS Runtime Application instance
app = new Microsoft.SqlServer.Dts.Runtime.Wrapper.Application();
// Save DTSX file to temp folder
string strPath = System.IO.Path.GetTempPath() + Guid.NewGuid().ToString() + ".dtsx";
app.SaveToXML(strPath, _objPackage, null);
return(strPath);
}