SSIS - Native OLE DB - Multiple Steps

Question

I have Visual Studio 2008 The DB is SQL Server 2008 R2 The steps to reproduce the error are:

1. Create new SSIS Package
2. Create Native OLE DB\Microsoft Jet 4.0 OLE DB Provider
3. Create SQL Task and config:
   • Resulset: None
   • Connection Type: OLE DB
   • Connection: Connection created ant 2
   • SQL Source Type: Direct Input
   • SQLStatements: SELECT 1 FROM table

When I run this package the following error apears:

SSIS package "Package.dtsx" starting.
Warning: 0x0 at SQL_Q_Mensual: Multiple-step OLE DB operation generated errors. 
         Check each OLE DB status value, if available. No work was done.
Warning: 0x0 at SQL_Q_Mensual: Multiple-step OLE DB operation generated errors. 
         Check each OLE DB status value, if available. No work was done.
SSIS package "Package.dtsx" finished: Success.

and if a use a ".Net Providers for OleDb\Microsoft Jet 4.0 OLE DB Provider" the package don't generate error.

I read a lot of notes on Internet but I don't know how to resolve this. I want to use Native OLD db and not the .net Provider.

Best regards, sorry because my poor english.

Walter

Answer 1

This is often a datatype problem. Make sure you are passing valid datatypes to whatever is going on in the database. For example, check that you are not passing a NULL or empty string value to a column that doesn't accept them (either manually defined or, say, a DATETIME column). Make sure that all VARCHAR lengths and numeric bounds are adhered to. Make sure that you SELECT column1, column2, columns3 instead of using SELECT *

Note: 64-bit SQL Server cannot use Jet, there is no 64-bit OLEDB provider for Jet.