[scheduling] SSIS Package fails, but scheduler reports success

I have an SSIS Package that executes a stored procedure. It turns out my stored procedure was missing.

However, the SQL Job Scheduler reported that step as success.

Can anyone tell me why?

`Le

Because your package is not exiting with an indication of success/failure.

Moved to the SSIS forum.

|||
when I run it through the command line, it indeed gives me an error code of (1).

But the Scheduler still tells me that it is successful.

`Le
|||You need to check the JOB action to take when a step fails -perhaps it is set to continue and report success.|||The step itself is trying to run a stored procedure that does not exist. The Sql schedule should report a failure, but it is not. It reports a success.

The Sql Server job says "quit with failure" when there is an error. There is clearly a different problem here.

`Le

|||

It is difficult to help you when you have all of the details and we don't. We can't see what you are doing and what happens, Your descriptions are incomplete and do not allow a good problem determination and resolution.

Please post the entire command line that you are having problems with, including any success/failure statements after it executes.

Is this being executed by the Windows Scheduler?

What version of SQL Server are you using?

|||

Arnie Rowland wrote:

It is difficult to help you when you have all of the details and we don't. We can't see what you are doing and what happens, Your descriptions are incomplete and do not allow a good problem determination and resolution.

Please post the entire command line that you are having problems with, including any success/failure statements after it executes.

Is this being executed by the Windows Scheduler?

What version of SQL Server are you using?

Thank you for your reply. I will attempt to be more detailed:

SQL SERVER: 2005
VISUAL STUDIO: 2005
SSIS File Name: Foo.dtsx
Configuration File Name: Config.dtsConfig

This config file has multiple steps, including one step that executes a Stored Procedure.

When I run this through a command line, I run it as:
dtexec.exe /FILE "\\MyServer\Foo.dtsx.dtsx" /CONFIGFILE "\\MyServer\Config.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

When it is run this way, process exits with error (1), because the Stored Procedure in question does not exist on the database. This is the correct error message.

When I run it through SQL SERVER 2005 SCHEDULER, I run it as a SSIS package, with the command line:
/FILE "\\MyServer\Foo.dtsx.dtsx" /CONFIGFILE "\\MyServer\Config.dtsConfig" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

However, the package always exits with success.

|||if a task inside a SSIS Package fails doesn't mean that the whole package fails
and the sql agent step would only report an error if the whole package fails

so you could set the property 'Fail Package on failure' to TRUE ... in the task where you execute this stored procedure.

- paul
|||When I run the package via command line (dtexec.exe), I do get fail message:

DTExec: The package execution returned DTSER_FAILURE(1)

~Le

[:#] problems viewing the custom sql statement in the gridview control

Hi,

I really need some help trying to figure out why my gridview is not working when I create a custom sql statement. It "executes" the query, but gives me an error message when I "test the query". Here is the error message:"There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Syntax error: Expecting '.', identifier or quoted identifier."

Here is my sql statement:

SELECT TBLPROJECTS.NAME, TBLPROJECTTYPES.NAME AS PROJECTTYPE, TBLPROJECTS.DESCRIPTION, TBLUSERS_1.LOGIN AS OWNERNAME,
TBLUSERS.LOGIN AS MANAGERNAME, TBLPROJECTS.START_DATE, TBLPROJECTS.END_DATE, TBLAOI.NAME AS AREAOFINTEREST,
TBLPROJECTS.MANPOWER, TBLUNITS.NAME AS MANPOWERUNIT
FROM TBLPROJECTS INNER JOIN
TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID INNER JOIN
TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID INNER JOIN
TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID INNER JOIN
TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID INNER JOIN
TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID

I have tested it on a new project and still it does not work, I cannot find any problem, please help!!!!!!!!!!!!!!!!!!!!!!!!

Does the query compile and return results by itself if you execute it in query analyzer?

|||

Yes, it compiles in the "Execute Query" in the "Query Builder", but when I click next, and it is supposed to Test the query, it doesnt work. Then it gives me an error when I try to ctrl F5,

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.] System.Web.UI.DataBinder.GetPropertyValue(Object container, String propName) +198 System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +2775 System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +59 System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +12 System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +101 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +25 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68 System.Web.UI.WebControls.GridView.DataBind() +5 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

I hope this helps? Thanks

|||

Yes, it compiles in the "Execute Query" in the "Query Builder", but when I click next, and it is supposed to Test the query, it doesnt work. Then it gives me an error when I try to ctrl F5,

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.] System.Web.UI.DataBinder.GetPropertyValue(Object container, String propName) +198 System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +2775 System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +59 System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +12 System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +101 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +25 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68 System.Web.UI.WebControls.GridView.DataBind() +5 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

I hope this helps? Thanks

|||

Yes, it compiles in the "Execute Query" in the "Query Builder", but when I click next, and it is supposed to Test the query, it doesnt work. Then it gives me an error when I try to ctrl F5,

DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[HttpException (0x80004005): DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'ID'.] System.Web.UI.DataBinder.GetPropertyValue(Object container, String propName) +198 System.Web.UI.WebControls.GridView.CreateChildControls(IEnumerable dataSource, Boolean dataBinding) +2775 System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +59 System.Web.UI.WebControls.GridView.PerformDataBinding(IEnumerable data) +12 System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +101 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +25 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +140 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +68 System.Web.UI.WebControls.GridView.DataBind() +5 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +61 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +67 System.Web.UI.Control.EnsureChildControls() +97 System.Web.UI.Control.PreRenderRecursiveInternal() +50 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Control.PreRenderRecursiveInternal() +171 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5731

I hope this helps? Thanks

|||Hi dotnetjunkie17,

Yes, it compiles in the "Execute Query" in the "Query Builder", but when I click next, and it is supposed to Test the query, it does not work

Since your sql query string even doesn't pass the "Query Builder" , there should be something wrong with your sql query string itself.

I've checked your query string, and find some errors. You cannot use INNER JOIN that way, to correct your errors, following the above code:

SELECT TBLPROJECTS.NAME,
TBLPROJECTTYPES.NAME AS PROJECTTYPE,
TBLPROJECTS.DESCRIPTION,
TBLUSERS_1.LOGIN AS OWNERNAME,
TBLUSERS.LOGIN AS MANAGERNAME,
TBLPROJECTS.START_DATE,
TBLPROJECTS.END_DATE,
TBLAOI.NAME AS AREAOFINTEREST,
TBLPROJECTS.MANPOWER,
TBLUNITS.NAME AS MANPOWERUNIT
FROM
TBLPROJECTS

INNER JOIN
TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID
INNER JOIN
TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID
INNER JOIN
TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID
INNER JOIN
TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID
INNER JOIN
TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID