SQL Service is stopped on failure of an ssis packa

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SQL Service is stopped on failure of an ssis packa

Author

Topic

Nader
Starting Member

41 Posts

Posted - 2012-05-31 : 13:21:10

I have an ssis package that runs as a proxy and I selected log on to the server using windows authentication.
The package owner is a coldfusion user slq authenticated user if our coldfusion server is down the package fails and stops the sql server service how to fix that
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 16:16:12

what logic have you used inside ssis for onerror event handler?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Nader
Starting Member

41 Posts

Posted - 2012-05-31 : 23:24:15

i am using sql task that opens an excel spreadsheet and populates the sql table.

"SELECT * into "+@TempTable+" FROM OPENROWSET("+"'"+"Microsoft.ACE.OLEDB.12.0"+"'"+", "+"'"+"Excel 12.0;Database="+ @[User::FolderName]+ @[User::SourceFileName] +"'"+", [Grades$])"

What kind of error handling would you suggest. How to tell him leave the package completely .

In case of the dataflow I handle the error by writing the errors to a file.
but for sql tasks shall I drag a connector to the following task and right click it and specify if fail.

Note:
The error that was displayed in the windows log is
Package "Moodle_Assessment_GradeImport_Grades" started.
Next error
Eventid 17311
SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).

SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

I also set the sql server agent to restart automatically I do not know why it did not.
Thanks

Nader
Starting Member

41 Posts

Posted - 2012-05-31 : 23:25:57

What kind of error handling is the best for the above situation
sql task "SELECT * into "+@TempTable+" FROM OPENROWSET("+"'"+"Microsoft.ACE.OLEDB.12.0"+"'"+", "+"'"+"Excel 12.0;Database="+ @[User::FolderName]+ @[User::SourceFileName] +"'"+", [Grades$])"
Shall I write to a file how can I ask him to quite gracefully.

Nader
Starting Member

41 Posts

Posted - 2012-05-31 : 23:54:19

Shall I set failpackageonfailure to true for this task.
How to add an error handler for the task above.
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 23:56:16

the error is not related to any of tasks you posted. Are you doing some kind of wmi tasks or executing any extermal processes from package?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2012-06-01 : 11:04:22

I wrote a stored procedure to create a job for the package which I always do with no problem
the package includes a task to create a temporary table then an sql task that opens the excel spread sheet and runs the commands mentioned above. then other tasks that run sql stored procedures to get column name unpivot a table then drops the temporary table
I found that the error appears when a user added an excel spread sheet of an improper format
i.e. the first row did not have the columns it should have. It had several rows that are merged to display a title then the columns required.

Thank you for you help

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-01 : 12:11:34

quote:
Originally posted by sarahmfr

I wrote a stored procedure to create a job for the package which I always do with no problem
the package includes a task to create a temporary table then an sql task that opens the excel spread sheet and runs the commands mentioned above. then other tasks that run sql stored procedures to get column name unpivot a table then drops the temporary table
I found that the error appears when a user added an excel spread sheet of an improper format
i.e. the first row did not have the columns it should have. It had several rows that are merged to display a title then the columns required.

Thank you for you help

sarah

then it looks like an exception which excel provider throws while trying to access through OPENROWSET due to improper format

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources