Categories
Asp, Asp.net

Run SSIS Package in .Net

Run SSIS Package in .Net

run ssis package from .net

In this tutorial we will learn about how to Run SSIS Package in .Net. Running a SSIS package from asp.net is sometime a must requirement from functional person or business user. Because they do not want to hit their head on SQL Server for checking SQL Server job runs successfully or not. Sometime requirement is to run the package by itself, depending upon the situation(manually).

Running a SSIS package from .net is a easy task, if you know how to write code in asp.net. First of all you have to add reference of Microsoft SQL Server Managed DTS runtime(Microsoft.SqlServer.Dts.Runtime) to the project. To locate assambly follow this:

In SQL Server 2017 assembly located in <drive>:\Windows\Microsoft.NET\assembly. You can find all of the Integration Services assemblies under this path, usually in the GAC_MSIL folder.

As in previous versions of SQL Server, the core Integration Services extensibility .dll files are also located at <drive>:\Program Files\Microsoft SQL Server\100\SDK\Assemblies.

Below is example of running a SSIS package from .net, which is writing records from a SQL Server table to a Flat File.

Design:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
        body {
            width: 980px;
            margin: 0px auto;
            text-align: center;
            padding-top: 50px;
            font-size: 20px;
        }

        .red {
            color: darkred;
        }
    </style>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <h1>Run SSIS Package in .Net</h1>
            <asp:Button ID="btn1" runat="server" Text="Run SSIS Package" OnClick="btn1_Click" />
            <br />
            <br />
            <asp:Label ID="result" CssClass="red" runat="server" Visible="false"></asp:Label>
            <br />
            <br />
            <br />
            <br />
            All rights reserved by <a href="http://www.hightechnology.in">www.Hightechnology.in</a> |
Hosting partner <a href="http://www.grootstech.com" target="_blank">Grootstech</a>
        </div>
    </form>
</body>
</html>

Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.SqlServer.Dts.Runtime;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btn1_Click(object sender, EventArgs e)
    {
        string pkgLocation = @"D:Company DatacodeRun SSIS From .NetRun SSIS From .NetPackage.dtsx";

        Package pkg;
        Application app;
        DTSExecResult pkgResults;
        //Variables vars;

        app = new Application();
        pkg = app.LoadPackage(pkgLocation, null);

        //vars = pkg.Variables;
        //vars["A_Variable"].Value = "Some value";

        pkgResults = pkg.Execute(null, null, null, null, null);
        result.Visible = true;
        if (pkgResults == DTSExecResult.Success)
            
        result.Text = "Package ran successfully";
       
        else
       
        result.Text = "Package Failed";
    }
}