.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

SSIS Error handling

Posted By:      Posted Date: October 11, 2010    Points: 0   Category :Sql Server


I have an ssis package to transfer data [Data flow task] from excel to sql server, for error handling this job, i have created an database table to save the error, can you please tell me how to get the system error and save it to database table i have created .




View Complete Post

More Related Resource Links

Error Handling loading Excel Files with SSIS 2005


I have an SSIS job which watches a directory for new files being submitted. On detecting a file(s) it determines the process to perform on it (based on the file name) and then reads in the file, loads it into the database, and deletes the file. All is fine. I want to add error handling though, specifically for the instance where the worksheet name does not correspond to that defined in the Excel Connection manager for this file.

At the moment if I change the sheet name to be different to that defined in the connection manager, the job fails and so (in turn) the whole process ceases (i.e. the for loop also goes to status failed).

I have tried event handler variables and checkpoints but without success. Could someone give me some advise, or point me to a article that would help?


Error handling in SSIS package


I am new to error handling in SSIS. What should I use package level, task level error handling....?

I want to capture following information while loading data from CSV to sql table:

1. SSIS package start time

2. Data validation like input data is not in correct formart or duplicate

3. Database SQL (insert operation) error

4. SSIS package End Time

Can anyone please suggest how to proceed on this.


Best practices for error handling ,debugging,and performence in ssis

Hi all,
I need some  sujjestions for best practices of ssis error handling  ,debugging,and performence .Recently i had finished a datawarehouse project .But we implemented in hurry,we didnt handle the erros in a proper way and even the performence was poor,so iam looking for some information in implemting the best practices

Many Thanks


Handling 404 page not found with Error page



      How do i handle 404 page not found?

Error while using exception handling block el 4.1


Hi Folks,


i am trying to use tyhe exception handling block of Enterprise library 4.1.


I just createda polisy and used IndexOutOfRangeException and added a replace handler and a logging handler to it.

I am using the NotifyRethrow PostHandlingAction to the exception.


During runtime I am getting an exception on the HandleException event, The exception is added below:



Object reference not set to an instance of an object.

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.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 42:             catch (Exception ex)
Line 43:             {
Line 44:                 bool reThrow = ExceptionPolicy.HandleException(ex, "ReplacePolicy");
Line 45:         

Foundations: Error Handling In Workflows


Workflows often define long-running processes and an unhandled failure usually means termination. Avoid this scenario by handling exceptions properly.

Matt Milner

MSDN Magazine February 2009

Error Handling: Throwing Custom Exception Types from a Managed COM+ Server Application


Exception handling semantics in .NET are based on type, so you can create custom exceptions that have their own properties and methods. In .NET, exceptions are first-class citizens, and since they're the built-in error handling mechanism, all .NET-compliant languages must support exceptions. In addition, COM+ services are available to .NET code as Enterprise Services, so you can leverage exceptions in your Enterprise Services design.In this article the author describes custom exceptions, throwing exceptions across COM interop boundaries, and working with Enterprise Services.

Bob DeRemer

MSDN Magazine March 2004

.NET Exceptions: Make the Transition from Traditional Visual Basic Error Handling to the Object-Orie


If you're used to Visual Basic 6.0 and you're making the transition to Microsoft .NET, you will find that error handling is quite different from what you've used for years. Visual Basic .NET uses a more object-oriented solution to signaling and responding to unexpected problems while your program is running. This approach, called structured exception handling, has a number of advantages over the On Error statements provided in previous versions of Visual Basic. For instance, exceptions give you lots more information about exactly what went wrong in your app. To help you take advantage of this modern error handling paradigm, this article will show you how to raise and respond to exceptions, as well as how to create your own custom exception classes.

Jesse Liberty

MSDN Magazine November 2002

Propagate Error Info: Use ATL and C++ to Implement Error-Handling COM Objects


Predefined error codes returned from HRESULT aren't always much help for debugging COM C++ code. The C++ macros provided with this article produce an XML file listing the error and its context to make debugging easier. This article begins with an overview of error handling in COM, then discusses the COM interfaces used in the macros. It explains how C++ exceptions are caught and converted to COM-compatible error information, how events are logged with the event viewer, and how context is reported in the description string of IErrorInfo. The macros handle logic errors and errors returned by an object or API.

Panos Kougiouris

MSDN Magazine October 2000

SSIS Import/Export Wizard: "Unexpected Unrecoverable Error"

For whatever reason, whenever the SSIS Import/Export Wizard needs to display an error message, it throws up "Unexpected Unrecoverable Error" instead. The three buttons - Abort, Retry, and Ignore - all cause the wizard to close. I see some activity in Process Monitor that looks like DTSWizard.exe is probing around for some System.Web assemblies, and not finding them. I'm running 2008 SP1 on Win 7 x86, and I've reinstalled the client tools and Integration Services. Anybody else seen this issue? Should I file a report on Connect?

Execption handling in SSIS

Hi , i am using SSIS to store data from database to flat file, every thing work fine, i want to take care any exception happen. i want to store in event viewer with proper error code, any one have idea how to do Thanks and Regards

SQL Job SSIS Error Event

Hello, I would like to execute a secondary package when a primary one fails. Can we do that directly with ssis ?   Thanks

Strange Problem - SSIS Fails with "Syntax error, permission violation, or other nonspecific error".

Hello, I have a strange issue while I am deploying a package to one of the environment server. I have 2 XML Source, in the DataFlow, and one will extracted based on a value of variable that is passed from run time (package is executed from a Job) and other will be extracted all time. The next Step I have is a Execute SQL Task in ControlFlow wihich will execute after DataFlow. This has 2 input parameters and some SQL query that uses the param. Now this one fail on the target environment with below error: failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. I got the error when I did a SSIS Text File Log. Note: If I run the package in BIDS with the same XML files it work. Also when I deploy the package to my Dev Server it works. When I compare the Dev DB with the target environment DB - Both are same. The Service Acount has permission - as I can see the DataFlow task completed. Another Point: The XML Load Data Flow that executes based on the Variable Value does not execute on target environment, even if the value is passed as "True" (It is Boolean Type) But this variable is NOT an input for the Execute SQL task that fails. I am not sure w

Customize SSIS Error row Redirection

Is there a way I can get actual field values for Error Rows while redirecting the error rows to a flat file.  I  have a data flow task with Oledb Source and flat file destination.  I configured the Flat File Source Error Output and am only getting the ErrorCode and ErrorColumn in the error output for "Text was truncated error"   -1071607675, 22180   But i'm not sure which column in which row resulted in this truncation error. Is there a way I can pinpoint the error Source?     thanks.

How to get error row in ssis

Hi All, When i use ssis to import data from oltp to data market (using a .dtsx file) an error raise in data flow, i find the detail in log file: An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E2F  Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_DimPlant_DimCountry". The conflict occurred in database "MaxMinManufacturingDM", table "dbo.DimCountry", column 'CountryCode'.".   but is there a way to  find which row cause the error? Thanks a lot.  

SSIS Package working on SQL Client Machine but when trying to run on SQL Server machine giving error

Hi We are trying to run SSIS package on sql server machine but sometimes it is giving error for data flow task containing Script Component. Error is - [SSIS.Pipeline] Error: component "SCR DimensionRelation" (1) failed the post-execute phase and returned error code 0x80004002. This error is not coming continuously. And also not for specific DFT. It comes for different DFTs as we rerun the package. Can anybody help me on this? We are using SQL server 2008. We are not getting any error on client machine :(:(

Excel Data Source SSIS AcquireConnection failed 0x80004005 unspecified error

Nuances of using Excel data sources with SSIS Your Development Machine Setup Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough. Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will see the title error "Unspecified Error" and "80004005" code. Resolution: Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)". Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.  Live Server (32-bit and 64-bit) Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here  (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en). There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same. You shoul
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend