Let’s develop a SharePoint WebPart for executing BAM SSIS Packages. The SharePoint site can be on remote server other than the BizTalk Database Server. This won’t be a fancy UI WebPart, just a basic dropdown to select a BAM SSIS Package and execute it. ;-) The approach I am used is of using a asp.net User Control hosted as a sharepoint webpart. There are bunch of different ways you can develop Sharepoint webpart. This is just one of them and easier one. Less error prone and easily debug able.
· Create a new solution and add a SharePoint WebPart project to it. Name it Tellago.WebParts.SSIS4BAM
· Rename the WebParts1 folder to say SSIS4BAM_WP.
· In the same solution add another ASP.NET Web App. Name it as Tellago.WebParts.WebApp.
· In the Web Application add a Web User control and name it as SSIS4BAM_UC.
· Let’s keep it simple with a Label, Dropdown and a Button. Below is the UI source.
· Now let’s first finish the User Control code behind. Below is the Page Load event code. Here, we are using the bam_Metadata_ActivityViews table from the BAMPrimaryImport Database but you can also use the bam_Metadata_Activities table.
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
using (SqlConnection conn = new SqlConnection("Integrated Security=True;Initial Catalog=BAMPrimaryImport;Data Source=localhost;"))
{
try
{
SqlCommand cmd = new SqlCommand("SELECT ActivityName, ViewName FROM bam_Metadata_ActivityViews", conn);
cmd.CommandType = System.Data.CommandType.Text;
conn.Open();
SqlDataReader sdr = cmd.ExecuteReader();
if (sdr != null && sdr.FieldCount > 0)
{
CountersDropDown.DataTextField = "ActivityName";
CountersDropDown.DataValueField = "ViewName";
CountersDropDown.DataSource = sdr;
CountersDropDown.DataBind();
}
else
{
EventLog.WriteEntry("SSIS4BAM--UC", "No Activities were fetched from the BAM Database.", EventLogEntryType.Error);
}
}
catch (Exception ex)
{
EventLog.WriteEntry("SSIS4BAM--UC", "Error occured while page load event for ResetCounterUC : "
+ ex.Message + ex.InnerException.ToString(), EventLogEntryType.Error);
}
}
}
}
· Next àExecute Button Event. For this reference the Microsoft.SqlServer.ManagerDTS assembly. If you are not able to find it in the Program Files, its sometimes hidden in the GAC_MSIL. Manually copy to the project and reference it.
· There are bunch of different blogs and references available online for how to execute SSIS packages programmatically. You can always extend this piece of code or have totally different approach with say Linq. There are many ways.
protected void ExecuteButton_Click(object sender, EventArgs e)
{
try
{
Application app = new Application();
DTSExecResult result;
Package pkg = new Package();
pkg = app.LoadFromSqlServer("BAM_AN_" + CountersDropDown.SelectedValue,"WIN2K8MK2", "", "", null);
result = pkg.Execute();
if (result == DTSExecResult.Failure)
{
foreach (DtsError error in pkg.Errors)
{
EventLog.WriteEntry("SSIS4BAM WebPart", "Error Description :" + error.Description, EventLogEntryType.Error);
}
}
EventLog.WriteEntry("SSIS4BAM Web Part.", "The SSIS Package for " + CountersDropDown.SelectedValue + " execution was a " + result.ToString(), EventLogEntryType.Information);
}
catch (Exception ex)
{
EventLog.WriteEntry("SSIS4BAM WebPart", "Error occured : " + ex.Message + ex.InnerException, EventLogEntryType.Error);
}
}
· Now getting back to the WebPart project. Create a folder structure as shown in the figure. And when you add an existing item for the last SSIS4BAM folder, make sure you add it as a link for the SSIS4BAM_UC.ascx.
· Below is the code for the SSIS4BAM WebPart.
namespace Tellago.WebPart.SSIS4BAM
{
[Guid("ecca03ad-ea87-496b-85ff-c68237457c19")]
public class SSIS4BAM_WP : System.Web.UI.WebControls.WebParts.WebPart
{
public SSIS4BAM_WP()
{
}
private SSIS4BAM_UC ssis4bamUC;
protected override void CreateChildControls()
{
base.CreateChildControls();
try
{
ssis4bamUC = (SSIS4BAM_UC)Page.LoadControl("~/_controltemplates/SSIS4BAM/SSIS4BAM_UC.ascx");
// Adds it to the controls collection of the Web Part
this.Controls.Add(ssis4bamUC);
}
catch (HttpException ex)
{
EventLog.WriteEntry("ResetCounters WebPart", "Error occured : " + ex.Message + ex.InnerException, EventLogEntryType.Error);
}
}
}
}
· Open the SSIS4BAM_UC.ascx and remove the CodeBehind line in the source file. Also change the namespace in the .cs and designer.cs file to the WebPart project namespace.
· Once again in the SSIS4BAM_UC.ascx source file change the Inherits value to the fully qualified name of the User Control file.
Like : Inherits="Tellago.WebPart.SSIS4BAM.SSIS4BAM_UC, Tellago.WebPart.SSIS4BAM, Version=1.0.0.0, Culture=neutral, PublicKeyToken=c04b4b556fc8f897"
· Build the solution and check for errors. Point the web part project to a SharePoint site URL and Deploy it. Walla..!!!
· Test it and if it errors out, check out the event viewer for errors. If you want to debug the User Control or the WebPart, place break points in the code and attach it to a w3wp.exe.
· If you get errors about SSIS Package failure due to following error SQLISPackage100 check out this link.
· If you have problems in loading the WebPart in SharePoint please refer this link and check if you might have not missed anything
· The Solution can be downloaded here..!!
Thanks,
Vishal Mody