July 19, 2009 at 4:08 pm
plesae
i need help to connect SSIS package with ASP.net
and use this code
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
Application app = new Application();
Package package = app.LoadPackage(@"c:ewew\Package.dtsx", null);
DTSExecResult result = package.Execute();
Response.Write(result.ToString());
}
}
and the result is Failuer
can any one help mee in it or tell me how to make it ?
July 20, 2009 at 4:53 am
I cannot answer your question but I can offer another method of executing packages from ASP.NET. Your user id will need to be able to use xp_cmdshell though.
The method uses dtexec to execute the package. dtexec is called using a sqldatasource;
<asp:SqlDataSource ID="SqlDataSource10" runat="server" ConnectionString=""
SelectCommand="declare
@cmdtxt as varchar(1024)
select @cmdtxt = 'dtexec /f C:\MyPackage.dtsx'
exec master..xp_cmdshell @cmdtxt">
Then call Select() for this datasource to run the SQL and execute the package.
July 20, 2009 at 10:45 am
@sief10 - > assuming this is a typo but the path is not well-formed.
Package package = app.LoadPackage(@"c:ewew\Package.dtsx", null);
Also, you may want to check windows event logs etc, when calling form ASP yo need to know what context (security/user-wise) you're calling from. If it's the anon user, then you need to set up security on the SSIS server and/or package appropriately.
Steve.
July 20, 2009 at 10:47 am
Your approach will not work unless you have installed Integration Services on the same server as your ASP.NET webserver.
If you want to execute a SSIS package on the database server (stored in SSIS) you will probably need to approach it another way.
We have had great success with the following approach:
- Install SSIS packages on the DB Server (in the MSDB repository)
- Create a SQL Agent job for the package
- Write stored procedures to abstract the SQL Agent stored procs: sp_start_job, sp_stop-job , etc. so you can have a standard interface for you application.
- Call your custom procs from your ASP.NET code to start/stop the SQL Agent jobs .. which control your SSIS packages.
We have taken it further and added HTTP Endpoints over our custom Job Manager procedures, so that our SSIS jobs can be controlled remotely from any system that supports WSDL.
GrayB
July 20, 2009 at 12:56 pm
hey Graham, that sounds like a really neat implementation, i like how y'all added the WS endpoints, very neat.
Steve.
July 20, 2009 at 6:10 pm
Yes, it took a bit of effort, but we have implemented a comprehensive set of stored procedures that can interact with our special SQL Agent jobs on the server. This gives us the ability to control any of our SSIS packages across the internal network. I would recommend using a much more secure mechanism for anything public (outside the firewall).
When writing the custom procedures, stick to using simple datatypes in the Input / Output parameters. Strings (VARCHAR) and Integer (INT) parameters are the easiest to work with. Otherwise you might have difficulty using your WSDL with Java, etc.
If you know that your application is mostly used with .NET, your procedures can return small datasets just by using a SELECT in the stored procedure. This is what we do with the WebMethod 'GetJobList' below. Returning a rowset from the SQL Sever procedure, results in a .NET dataset object being returned from the Web Reference. Very handy for populating grids, etc. in a Webpage.
Once you have the procedures written, the setup of HTTP Endpoints in SQL 2005 is quite easy. The only trick is make sure that the http.sys driver is installed and running on your SQL server.
For example, creating an endpoint would look like this:
CREATE ENDPOINT abcxyz
STATE = STARTED
AS HTTP
(
PATH = '/endpt/example/myabcservice',
AUTHENTICATION = ( INTEGRATED, NTLM, KERBEROS),
PORTS = ( CLEAR ),
CLEAR_PORT = 9999,
SITE = '*'
)
FOR SOAP
(
WEBMETHOD 'SetContext'
(NAME = 'MyDB.dbo.usp_SET_CONTEXT', SCHEMA=STANDARD ),
WEBMETHOD 'StartJob'
(NAME = 'MyDB.dbo.usp_START_JOB', SCHEMA=STANDARD ),
WEBMETHOD 'StopJob'
(NAME = 'MyDB.dbo.usp_STOP_JOB', SCHEMA=STANDARD ),
WEBMETHOD 'GetJobList'
(NAME = 'MyDB.dbo.usp_GET_JOB_LIST', SCHEMA=STANDARD ),
....
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'MyDB',
NAMESPACE = 'http://yourcompany.com/'
);
For endpoints, there is reasonable security ... in essentially three-layers (usually good enough for internal applications):
1. Security on the Endpoint itself
GRANT CONNECT ON ENDPOINT::abcxyz TO someID (or someRole)
GRANT VIEW DEFINITION ON ENDPOINT::abcxyz TO someID (or someRole)
2. By authentication method
AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS ...
3. Database role security
GRANT EXECUTE ON someproc TO someID
When the connection is initiated from an ASP.NET server, the connection context will be the login credentials of the ASP.NET server. Because of this you will probably want to have your ASP.NET server running under a service account setup for the application (or server). On the SQL Server you will need to grant rights to the Domain account associated with the Application (not the individual users).
However, it is important to know who the user is at the other end of the browser, so we had ASP.NET pass the User Context info from ASP.NET to our custom Job Manager database, where it is associated with the ConnectionID & SessionID. That way we know who is the end-user that is starting / stopping / viewing jobs. The webmethod for 'SetContext' is called each time we create an instance of the Web class in .NET. In addition to just tracking this information, you could use it to roll your own application level security as well.
For the ASP.NET project we just import the Web reference from the WSDL URL and presto, you have an instant class in .NET. ASP.NET does not even know it is talking directly to a database.
The WSDL URL takes the form:
http://{server}:{port}/{path}?wsdl
From the example above, your WSDL URL would become:
http://MYSQLSERVER:9999/endpt/example/myabcservice?wsdl
And you just add this Web reference to your ASP.NET project.
GrayB
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply