SSIS ETL from Microsoft SQL Server 2008 to Microsoft Access throwing error at OLE DB Destination component

We have the plain Microsoft SQL Server 2008 on one of our servers

Here are the about the versions of other Components on our server:

• Microsoft SQL Server Management Studio 10.0.5512.0
• Microsoft Analysis Services Client Tools 10.0.5500.0
• Microsoft Data Access Components (MDAC) 3.85.1132
• Microsoft MSXML 2.6 3.0 4.0 6.0
• Microsoft Internet Explorer 8.0.6001.18702
• Microsoft .NET Framework 2.0.50727.3655 Operating System 5.1.2600

We decided to migrate the Microsoft SQL Server 2000 Extract-Transform-Load DTS packages to Microsoft SQL Server 2008 SSIS packages

We decided to create DTSX files on the filesystem so that we can use BIDS 2008 to open the DTSX files.

The Migration passed all the tests.

However, I've had numerous Error Messages when I run the DTSX files from within BIDS 2008.

Our ultimate aim is to For one of the Data Flow Components that does extracts selected data from a Microsoft SQL Server 2008 Database, and places it in Microsoft Access Database MDB files, I get the following errors:

Diagnostic,,"OLE DB Source" (1),{00000001-0000-0000-0000-000000000000},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,0,0x,ExternalRequest_post: 'ICommandWithParameters::GetParameterInfo succeeded'. The external request has completed. OnProgress,,DTSTask_DTSDataPumpTask_24,{D1E141EB-593C-4016-90C8-6AA0C33661AA},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,33,0x,Pre-Execute OnError,,DTSTask_DTSDataPumpTask_24,{D1E141EB-593C-4016-90C8-6AA0C33661AA},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. OnError,,Generic_Application_Data_Export_SSIS,{177DC585-3B2B-4E9D-A80E-07D49B1A30E9},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,-1071636471,0x,SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21. OnError,,DTSTask_DTSDataPumpTask_24,{D1E141EB-593C-4016-90C8-6AA0C33661AA},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid. OnError,,Generic_Application_Data_Export_SSIS,{177DC585-3B2B-4E9D-A80E-07D49B1A30E9},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,-1071636443,0x,Cannot create an OLE DB accessor. Verify that the column metadata is valid. OnError,,DTSTask_DTSDataPumpTask_24,{D1E141EB-593C-4016-90C8-6AA0C33661AA},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,-1073450982,0x,component "OLE DB Destination" (70) failed the pre-execute phase and returned error code 0xC0202025. OnError,,Generic_Application_Data_Export_SSIS,{177DC585-3B2B-4E9D-A80E-07D49B1A30E9},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,-1073450982,0x,component "OLE DB Destination" (70) failed the pre-execute phase and returned error code 0xC0202025. OnProgress,,DTSTask_DTSDataPumpTask_24,{D1E141EB-593C-4016-90C8-6AA0C33661AA},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,66,0x,Pre-Execute OnInformation,,DTSTask_DTSDataPumpTask_24,{D1E141EB-593C-4016-90C8-6AA0C33661AA},{4901FE69-78A3-4280-8989-45ABC5743A18},6/19/2014 1:53:08 AM,6/19/2014 1:53:08 AM,1074016264,0x,Post Execute phase is beginning.

EDIT: The Control Flow Components that takes data from around 18-19 columns from a Microsoft SQL Server 2008 SQL select query, and then converts the values in order to place them in the Microsoft Access table throws the aforementioned errors, I have a number of columns that I retrieve from Microsoft SQL Server 2008 table using a DataFlow Component called OLE DB Source. After retrieving the data, I have a Data Converter data Flow Component that converts the values. Finally, the converted values are placed by an OLE DB Destination data Flow Component, and placed in a Microsoft Access table.

The errors could be caused by mismatch between data type and/or length of some of the source columns and the corresponding destination columns. As I mentioned before, we retrieve the data values from around 18-19 columns from a Microsoft SQL Server 2008 SQL select query that are the source. The aforementioned error fails to mention which one is causing the mismatch between data type and/or length. How could I narrow down the column that causes the problem?

Is it possible to use new OLEDB Destination and do the mappings for the columns coming upstream and the actual destination columns? Error is related with meta data. May be data type or length of some of the destination columns have been changed.

If possible try to use Derived Column with the expression for type conversion.

Derived Column Transformation