I made a SQL Taks to remove data out off an mdb ms Access database. It has always worked but now i am getting error's. The statement is "DELETE from table" Parse Query is okay! It turns green! but there is a warning multiple-step operation generated error.. No work was done! Then my Data Flow Taks starts and that stops en turns red! because somehowe! there is still data left? Because when i remove it myself the dataflow works great.. any idea's? i am stuck!

Hi Snow Rabbit, How do you delete the data off the Access in the package? Please also paste the error message in its entirety.Arthur My Blog

I run a query on the Access mdb.. Execute SQL Task: Delete from Tblcate.. (this works fine if i do it by hand) Then i am putting the SQL 2008 data in... ---Error--- SSIS package "Package.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Warning: 0x0 at Execute SQL Task: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Warning: 0x0 at Execute SQL Task: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. Information: 0x40043006 at Data Flow Task, SSIS.Pipeline: Prepare for Execute phase is beginning. Information: 0x40043007 at Data Flow Task, SSIS.Pipeline: Pre-Execute phase is beginning. Information: 0x4004300C at Data Flow Task, SSIS.Pipeline: Execute phase is beginning. Error: 0xC0202009 at Data Flow Task, OLE DB Destination [81]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC0209029 at Data Flow Task, OLE DB Destination [81]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (94)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (94)" 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. Error: 0xC0047022 at Data Flow Task, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (81) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (94). 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. Information: 0x40043008 at Data Flow Task, SSIS.Pipeline: Post Execute phase is beginning. Information: 0x4004300B at Data Flow Task, SSIS.Pipeline: "component "OLE DB Destination" (81)" wrote 0 rows. Information: 0x40043009 at Data Flow Task, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package.dtsx" finished: Failure.

Do you run the package in BIDS when it errors out? And the MS Access, is it installed on your machine? What version of both SSIS and MS Access, bitness, etc do you have?Arthur My Blog

MS Access version 2003... and i got visual studio 2008 SQL Sever Management Studio Complete 10.0.4064 OS Windows Server 2008R2

Yess error is in bids debug mode!

This error doesn't indicate an error with your Execute SQL Task. It indicates an error with an OLE DB Destination inside a Data Flow Task. I'd fix errors before I started worrying about warnings. The error you're receiving is due to an implicit type cast. You're sending data to your destination table (Access?) that is of a different data type (string?) than what the table expects (numeric?) and the driver is encountering rows that it can't convert (empty string, all chars, ...) If you happen to have transactions "required" on a container containing both of those tasks, then any delete operation would be rolled back. (Probably not likely you have transactions enabled.) Anyway - solve the error first... Talk to me now on

Hello Todd, Your right! destination table is Access. I got one field in that table thats driving me nuts! It a numeric Access field. I wanna put a int field in that field. But thats! working! but as soon as i use a SQL Taks it freaks out. Its like the data its not been removed! or its removing everthing! its really strange! Because it has always worked.... but a soon as exclude that field everything is working great. And i can always change the field in the source SQL 2008 table.. But wich type can compare to Access numeric? smallint?

An Access Numeric typed field would be more like a decimal type or floating point to SSIS. I'd try DT_NUMERIC or DT_DECIMAL. Talk to me now on