McLeanIS
asked on
SSIS package runs manually but fails when run from code
I have an SSIS package (.dtsx file) that exports data from a SQL Server view to an Excel file that it creates. All takes place on the same server. When I run it manually, it works fine. When I run it from code behind an ASP.net web page, the Excel file is created but no data is exported. The returned error message simply reads "DTSER_FAILURE". I am not sure how to get more error details.
Code that runs the package:
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = _
"C:\Export\dtsSEIFiledIndi
pkg = app.LoadPackage(pkgLocatio
pkgResults = pkg.Execute()
txtResults.Text = pkgResults.ToString
Thanks for the help.
Code that runs the package:
Dim pkgLocation As String
Dim pkg As New Package
Dim app As New Application
Dim pkgResults As DTSExecResult
pkgLocation = _
"C:\Export\dtsSEIFiledIndi
pkg = app.LoadPackage(pkgLocatio
pkgResults = pkg.Execute()
txtResults.Text = pkgResults.ToString
Thanks for the help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>and the owner that executes the package has read-write privs on it
Also that the owner that executes the SSIS package has SQL Server privs to execute/select from the data source. I've run into this many times where a service account is used, an SSIS package executes a Stored Proc, and then fails because I didn't assign execute privs on the SP to the service account.
Also that the owner that executes the SSIS package has SQL Server privs to execute/select from the data source. I've run into this many times where a service account is used, an SSIS package executes a Stored Proc, and then fails because I didn't assign execute privs on the SP to the service account.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried a UNC path but that failed. The local path works. The package can create the new Excel file but the data won't transfer.
I decided to try using the package event handlers to get more error info. I set up an OnPostExecute event for the package with an Execute SQL Task. The task is supposed to insert data from package variables into a table on the server. This caused more problems.
When I run the package manually and use Parameter Mapping within the task editor, the task fails. If i hard code values to insert, no problems.
In the execution progress window, this error comes up concerning the INSERT statement:
"Multiple-step OLE DB operation generated errors. " There is more but it is of no help.
I decided to try using the package event handlers to get more error info. I set up an OnPostExecute event for the package with an Execute SQL Task. The task is supposed to insert data from package variables into a table on the server. This caused more problems.
When I run the package manually and use Parameter Mapping within the task editor, the task fails. If i hard code values to insert, no problems.
In the execution progress window, this error comes up concerning the INSERT statement:
"Multiple-step OLE DB operation generated errors. " There is more but it is of no help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My code worked as I wanted it to after implementing my solution.
ASKER