If you’ve ever created a SSIS package and wanted to provide a way for end users to run that package without having to run the Business Intelligence Development studio, you can compile the package into a dtsx file for them to run. This would require them to manually alter connections and variables, which can get complicated for an average end user. Alternatively, it is pretty simple to put together an application which can be used to run the package. This would allow you to handle modifications to package variables in a much more user-friendly manner.
I have a relatively simple SSIS package which queries data from a view based on a date range, and exports the returned data to a CSV file.
First of all, you will need to add a reference to the Microsoft.SQLServer.ManagedDTS.dll as well as a reference to the Microsoft.SqlServer.DTS.Runtime namespace. Once that is done, you can reference the namespace to create objects referencing the package, and any other items you need to reference such as connections and variables.
This is a simple method I created in Visual Studio 2010 which calls the provided compiled DTS package and updates the connections and variables. (see the attached ZIP file to see the full application)
using Microsoft.SqlServer.Dts.Runtime;
namespace CFX_ExportPortalEmailLeads
{
public partial class frmExportData : Form
{
private void cmdRun_Click(object sender, EventArgs e)
{
Execute_Package(String.Format("{0:yyyy-MM-dd}", dtpStartDate.Value), String.Format("{0:yyyy-MM-dd}", dtpEndDate.Value));
}
private void Execute_Package(string StartDate, string EndDate)
{
string pkgLocation = txtUsePackage.Text;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
Variables vars;
Connections conns;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
//Set Connection Settings
conns = pkg.Connections;
conns["DestinationConnectionFlatFile"].ConnectionString = txtOutputFolder.Text + "\ExportFile.csv";
conns["SourceConnectionOLEDB"].ConnectionString = "Data Source=DataServer;User ID=sa;Password=1234567;Initial Catalog=DTSDatabase;Provider=SQLOLEDB;Auto Translate=false;";
//Set SSIS Variables
//**Note** - Variables MUST be package level variables in the SSIS package, otherwise an error will result when trying to set the variable
vars = pkg.Variables;
vars["StartDate"].Value = StartDate;
vars["EndDate"].Value = EndDate;
pkgResults = pkg.Execute(null, vars, null, null, null);
if (pkgResults == DTSExecResult.Success)
MessageBox.Show("Package Ran Successfully");
else
MessageBox.Show("Package Failed");
}
}
}
|
As you can see, this method is pretty simple, but provides a much simpler user interface than the compiled SSIS package.
Thanks for reading!
thank great help!!!