EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

EXECUTE SQL TASK --> Object Reference Not Set to An Instance of an Object

Hi all,

Does anyone see the error below before?

I am using SSIS Execute SQL Task (ADO.NET) to update a table using a stored procedure.

It works like this many times for me and all of a sudden, not sure what is changing in the environment, I kept getting this WARNING when I click on PARSE QUERY

“Object Reference Not Set to An Instance of an Object” when I click on PARSE QUERY.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

OLEDB Sample also give me syntax error

exec dbo.updDimBatch ?,?,'Load Activity Increment','6/27/2007','6/27/2007',1,?,?,0,0,'6/27/2007',''

I tried to change to OLEDB and call the stored procedure like this but got syntax error?

Not sure what is the error here.

I believe there is a known issue when trying to do a Parse Query with ADO.NET, when variables or parameters are being used. Does the task succeed if you execute it?|||

yes, it executes fine.

This is going against SQL SERVER 2005 SP2 x64 Enterprise.

Note that this task executes fine and the stored procedure updates data.

The stored procedure does the following.

This is just an example. But to call ANY STORED PROCEDURE will give the same problem.

I think it is a bug with the ADO.NET query parser.

Note that I saw this exact same error LAST YEAR with SP1. I think it never get fixed.

work around? customers don't want to use OLEDB because the syntax ?,? etc is not straightforward and easy to maintain.

They elect to just ignore it.

There are other stored procedures of different kinds and they all worked.

But all of them give this error when I click on PARSE QUERY.

Code Snippet

DECLARE @.TodayDate datetime

SET @.TodayDate = GETDATE()

Exec dbo.updDimBatch

@.BatchKey = @.BatchKey,

@.ParentBatchKey = @.ParentBatchKey,

@.BatchName = 'Load Customer Increment',

@.BatchStartDate = NULL,

@.BatchEndDate = @.TodayDate,

@.StatusKey = NULL,

@.RowsInserted = @.Count_Insert,

@.RowsUpdated = @.Count_Update,

@.RowsException = NULL,

@.RowsError = NULL,

@.UpdatedDate = @.TodayDate,

@.BatchDescription = NULL

EXECUTE permission deny

Any one can help me, below error messages for reference, thanks!

Exception Details:System.Data.SqlClient.SqlException: EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.

Source Error:

Line 96: cmdMid.Connection = conMid;Line 97: cmdMid.CommandText = "exec sp_insertspend '" + uid + "','" + Mid + "','" + status + "','" + spend + "'";Line 98: cmdMid.ExecuteNonQuery();Line 99: conMid.Close();Line 100:

Source File:f:\Microsoft Visual Studio 8\Web\Soccer\main.aspx.cs Line:98

Stack Trace:

[SqlException (0x80131904): EXECUTE permission denied on object 'sp_insertspend', database 'master', owner 'dbo'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734934 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 _Default.btnbet_Click(Object sender, EventArgs e) in f:\Microsoft Visual Studio 8\Web\Soccer\main.aspx.cs:98 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

Hi there,

In SQL Server Managment Studio under Database Properties / Permissions you have to tickgrant execute permissionthe user or role you are using to connect to the database.

Execute Package Task using SSIS Package Store package

From the Execute Package Task Editor, what info do you have to enter to
reference a SSIS Package Store package?
For the Location, I've tried both "SQL Server" and "File" and nothing
works except selecting the actual location from the "File" option.
For the Connection, I've tried the default "Native OLE DB\..." and "..OLE DB
Provider for DTS.." as Provider with no result.
For PackageName, I've tried various combination "\File System\PackageName",
"PackageName", etc... and nothing works.

I'm assuming this task allow for the 3 different ways to store the package (SQL Server/MSDB, File System, Package Store (hybrid)).

Any ideas!!!?

The documentation for the task here

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/042d4ec0-0668-401c-bb3a-a25fe2602eac.htm

says that the package can originate only from the File System or MSDB.

You could however use DTExec to fire the package with the /D[ts] package_path
parameter
Allan|||Remember as well that all the package store is the only the logical container for MSDB and the folder location specified in MsDtsSrvr.ini. The thing about it is that the service is aware of this location.
Allan|||The problem with the Package Store is that it is both or "managed" File System. I think someone has submitted a bug report on my behalf for this. Thanks.