ASP.NET

7-Oct-09 3:45

Hi,
I have created a SSIS package with the following task:
1-Execute a SQL Task to delete the data in a table

I can execute this package from ASP.NET and all seems fine.

Now i have modified the SSIS package by adding a control flow which imports data from excel into the deleted table in step 1.

The ssis package on it's own works fine.
Now I try once again to execute the ssis package from ASp.NET.
I get a failure and i know the problem is to do with the excel connection but can not figure out what needs to be edited in the code to correct this issue.

here is the code to execute the ssis package from ASp.NET in C#

I have indicated where the problem seems to be.
Any thoughts please?

protected void btnExecute_Click(object sender, EventArgs e)
         {
                  Application app = new Application();
                  Package package = null;

                  try
                  {
                           string fileName =
                                    Server.MapPath(System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName.ToString()));
                                    FileUpload1.PostedFile.SaveAs(fileName);

                           //Load DTSX
                           package = app.LoadPackage(@"C:\Work\Package1.dtsx", null);

                           //Global Package Variable
                           Variables vars = package.Variables;
                           vars["Business_ID"].Value = txtBusinessID.Text;
                           vars["Business_Name"].Value = txtBusinessName.Text;

//PROBLEM is on this connectionstring for eacel...
                           //Specify Excel Connection From DTSX Connection Manager
                           package.Connections["SourceConnectionExcel"].ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;";

                           //Execute DTSX.
                           Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
                           lblResult.Text = results.ToString();
                  }
                  catch (Exception ex)
                  {
                           throw ex;
                  }
                  finally
                  {
                           package.Dispose();
                           package = null;
                  }

         }

dojohansen7-Oct-09 4:01

Re: execute ssis from asp.net [modified]

7-Oct-09 4:01

The first thing to check is what "fileName" actually contains. Unless the server is on the same machine as the one executing the application containing your code, the path must of course be a network path and not a local-machine path. If they are on the same machine, it will at least need to be a rooted path (e.g. c:\toto\files\file.tata, not files\file.tata).

Also, on an aside, you may want to remove that catch block. First of all it's a waste of space as it doesn't do anything. Worse, when you rethrow like that you rewrite the entire stack trace. Therefore, whereever in your try block or something the try-block calls into an exception is thrown, the rethrown exception will only refer to the line "throw ex", which is rather less useful!

arkiboys7-Oct-09 4:10

7-Oct-09 4:10

filename contains:
c:\work\filename.xls
The problem still remains.

modified on Wednesday, October 7, 2009 10:18 AM

dojohansen7-Oct-09 4:43

7-Oct-09 4:43

Which is a local-machine path. So unless Sql Server and the web server are both on the same machine that isn't going to work. (You didn't say if they are.)

It shouldn't be necessary to mention this, and please don't take offense: If the file is on the client computer it's obviously also not going to work. You can of course let a user upload a file to the web server and then use it there.

In general, the spreadsheet file needs to be in a location where Sql Server can reach it, and referred to with the correct path from Sql Server's point of view. In general, you probably want your application to work whether the Sql Server instance is running locally or on another computer (your app after all probably only knows what connection string it is using).

If it is an uploaded file you may want to create a shared folder on the web server, set up with the right permissions so Sql Server can access it. You can go the other way and put the file on the machine Sql Server is on, but I believe this would require impersonation since by default the asp.net user is a local machine user that cannot use any network resources (for security reasons; should your app be compromised in some way, at least the entire network isn't at risk).

dojohansen7-Oct-09 4:50

7-Oct-09 4:50

Btw did you remove the catch block? If so, what's the stacktrace in the exception you're getting? Just to know if it even gets to the "Execute" step...

arkiboys7-Oct-09 5:11

7-Oct-09 5:11

There is no error in the code.
It just returns failure (As mentioned before).

dojohansen7-Oct-09 7:04

7-Oct-09 7:04

The method returns void, not failure. So there must be an exception if you get any notice of a failure. Attach a debugger and find out what's happening.

arkiboys7-Oct-09 11:23

7-Oct-09 11:23

If you look closely you will see, results.ToString();
It returns failure or success.
I step through t he code and see the failure.

dojohansen7-Oct-09 11:28

7-Oct-09 11:28

protected void btnExecute_Click(object sender, EventArgs e)

It returns void. It assigns a value to the text of a label, if it ever gets to that statement.

If you cannot be bothered to do simple things I ask for so that I can help you, like remove the catch block and tell me if you get an exception and if so what the stack trace is, I am not sure I can see why I should bother trying to help.

arkiboys7-Oct-09 5:10

7-Oct-09 5:10

I am developing on a local machine with both sql server and visual studio installed.
Any thoughts?

dojohansen7-Oct-09 11:29

7-Oct-09 11:29

Not as long as I have no idea what the error is. All I know is that "it doesn't work".

arkiboys7-Oct-09 11:34