Wednesday, April 25, 2012

How to call SSIS package in asp.net

1.to implement this,if u have SQL server 2005 then include reference of following dll and path is:

C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies :
1.Microsoft.SqlServer.SQLTaskConnectionsWrap.dll
2.Microsoft.SqlServer.TxScript.dll
3.Microsoft.SQLServer.ManagedDTS.dll
4.Microsoft.SqlServer.DTSPipelineWrap.dll


2.if u have SQL server 2008 then include reference of following dll and path is:

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies :
1.Microsoft.SqlServer.SQLTaskConnectionsWrap.dll
2.Microsoft.SqlServer.TxScript.dll
3.Microsoft.SQLServer.ManagedDTS.dll
4.Microsoft.SqlServer.DTSPipelineWrap.dll

and in code behind include namespace like this:


using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Dts;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

//Note: here i have put package in my local drive C:\Ajay\Package1.dtsx


protected void btnexecute_Click(object sender, EventArgs e)
        {
            try
            {
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
               Microsoft.SqlServer.Dts.Runtime.Package package = null;
                package =(Microsoft.SqlServer.Dts.Runtime.Package) app.LoadPackage(@"C:\Ajay\Package1.dtsx",null,true);
                Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();

                if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
                {
                    foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in package.Errors)
                    {

                        Console.WriteLine("Package Execution results: {0}", local_DtsError.Description.ToString());
                        Console.WriteLine();
                    }
                }

            }
            catch (Exception ex)
            {
                string SS = ex.Message;
            }
        }

No comments:

Post a Comment