Could not delete from specified MS access tables from SSIS execute task

Question

Could not delete from specified MS access tables from SSIS execute task

bjeyakumar 89545

SSC Veteran

Points: 202
More actions
October 11, 2017 at 11:38 pm

#329155

I am working in migration project to upgrade current sql server 2008 SSIS jobs into new SQL server 2014 in windows 2012 server.
One of my SSIS job step using MS Access 2010 (.mdb) tables in SQL Execute Task which deletes all the rows in table, this job working fine in sql server 2008 SSIS package
but same job is not working in sql server 2014 sql server agent job.
I got the following error message in agent job history,
Executed as user: PRXUSER. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:25:23 AM Error: 2017-10-12 10:25:24.20
Code: 0xC002F210 Source: Execute SQL Task 2 Execute SQL Task Description: Executing the query "delete * from
emp" failed with the following error: "Could not delete from specified tables.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:25:23 AM
Finished: 9:25:24 AM Elapsed: 0.343 seconds. The package execution failed. The step failed.
Data Source=C:\table\emp.mdb;Provider=Microsoft.Jet.OLEDB.4.0;
SQL statement I given in Execute SQL task editor - delete from emp
Can you please help me resolve this issue.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

TheCTEGuy Ten Centuries Points: 1032 More actions · Answer 1

bjeyakumar 89545 - Wednesday, October 11, 2017 11:38 PM
I am working in migration project to upgrade current sql server 2008 SSIS jobs into new SQL server 2014 in windows 2012 server.
One of my SSIS job step using MS Access 2010 (.mdb) tables in SQL Execute Task which deletes all the rows in table, this job working fine in sql server 2008 SSIS package
but same job is not working in sql server 2014 sql server agent job.
I got the following error message in agent job history,
Executed as user: PRXUSER. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 32-bit
Copyright (C) Microsoft Corporation. All rights reserved. Started: 10:25:23 AM Error: 2017-10-12 10:25:24.20
Code: 0xC002F210 Source: Execute SQL Task 2 Execute SQL Task Description: Executing the query "delete * from
emp" failed with the following error: "Could not delete from specified tables.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not
established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:25:23 AM
Finished: 9:25:24 AM Elapsed: 0.343 seconds. The package execution failed. The step failed.
Data Source=C:\table\emp.mdb;Provider=Microsoft.Jet.OLEDB.4.0;
SQL statement I given in Execute SQL task editor - delete from emp
Can you please help me resolve this issue.

Try by checking the 32-bit run time. This can be seen under your job configuration..

First solve the problem then write the code !

reinhardcilliers SSC Eights! Points: 891 More actions · Answer 2

Hi

Check that your user PRXUSER has access to the location where your access database files (.mdb) are stored.

bjeyakumar 89545 SSC Veteran Points: 202 More actions · Answer 3

Hi,

Thank you very much for suggestion, I given access to my proxy ID and it is working fine.