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
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
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.
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.
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/
SQL Service is stopped on failure of an ssis packa
Posted - 05/31/2012 : 13:21:10
