Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.

I was getting following errors while Importing Excel file to SQL Server

Following is list of error details on Importing Excel 2007 to SQL Server 2008

Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Could not allocate space for object 'dbo.MyTable'.'PK_MyTable' in database 'MyDatabase_1384_8481' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.".

 (SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  

The "input "Destination Input" (44)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (44)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

 (SQL Server Import and Export Wizard)

Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  

The ProcessInput method on component "Destination - MyTable" (31) failed with error code 0xC0209029 while processing input "Destination Input" (44). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. (SQL Server Import and Export Wizard) 

Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  

The PrimeOutput method on component "Source - 'ip-to-country$'" (1) returned error code 0xC02020C4.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

 (SQL Server Import and Export Wizard)

Work Around to deal with this situation

I have observed that from last couple of month my database has grow unexpectedly without my knowledge, and that was main reason for this error.  So if you encounter this problem, don't follow solution blindly otherwise you would never solve root cause of problem.

Do following

• Try to go through one by one table and check which table had grown unexpectedly.  Tip try from checking table in which rows are inserted automatically.  Example: Errorlog tables and other depends on your application.
• Ones you find the that table, identify what is causing sudden growth, is it due to code change in application or due to some automatic script run by someone (virus) this problem is causing.  Try to fix that problem.
• Best approach is to take backup of that table and delete that table and recreate it.  That will fix problem as lot of primary keys are free. 
• After solving this root cause problem try to apply solution.

Solution

• Make sure there is enough Hard Disk space where database files are stored on server.
• Turn on AUTOGROW for file groups.

Now try to Import your Excel File, make sure that you follow all points mentioned in this blog post in order to Import Excel 2007 file to SQL Server.