Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Running Sql Server Integration services package from a C Sharp application

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!


 

 

 

  Attachment: RunDTSPackage.zip

Tags: , ,
What's This?
  
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.



Related Content
   Confirming Actions With an "Are you sure?" Prompt in Infor CRM (Saleslogix)
I'm not a huge fan of bugging end users. I figure, if an end user clicked something, he or she meant
Posted on Nov 18, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Programmatically Using Security Roles and Secured Actions in Infor CRM (Saleslogix)
In most places in the Infor CRM (Saleslogix) web client, it's pretty easy to use security roles. You
Posted on Nov 07, 2014 by Ryan Farley to Ryan Farley's Blog
 
   Using an Array - SQL vs Crystal.
In data, an Array is used to store multiple values in a single variable.  I often use Arrays to qual
Posted on Jul 17, 2014 by George Jensen to George Jensen's Blog
 
   SQL Stored Procedure to Create SalesLogix Table ID Values
Most of the SalesLogix systems I see now days no longer use remote users. Instead, all users VPN into a c
Posted on Dec 23, 2013 by Ryan Farley to Ryan Farley's Blog
 
   Evaluating strings in C# Code snippets
When checking for two (or more) different values for a property in a code snippet, you can't simply l
Posted on Dec 13, 2013 by Jason Buss to Jason Buss' Blog
 
Comments

 

Twitter Trackbacks for Running Sql Server Integration services package from a C Sharp application - Jason Buss' Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Running Sql Server Integration services package from a C Sharp application - Jason Buss' Blog         [customerfx.com]        on Topsy.com

January 20, 2011 12:41 PM
 

Phil Parkin said:

Very interesting!

What needs to be installed on the client to make this work?

Surely something needs to be calling DTExec.exe on the server where SSIS is installed to make this work?

Cheers

Phil

January 20, 2011 1:24 PM
 

Jason Buss said:

As far as I know, SSIS doesn't have any remote execution features, so unfortunately this would have to be run on a machine with SSIS installed using this method.

January 21, 2011 10:56 AM
 

john said:

thank great help!!!

September 26, 2011 4:25 PM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2014 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register