I'm trying to read the data in Access database using Execute SQL Task. I'm trying to get the user created tables in MS Access and transfer the data in each table into an temp table in sql server for comparison purpose. In that for getting the list of all user tables in MS access i've done the following.
1) I've created a connection manager using Microsoft Jet 4.0 provider and configured it. I took an Execute Sql task and selected the created connection manager. In that i've given the query as SELECT name FROM Msysobjects WHERE type=1 AND name NOT LIKE 'Msys*'
When i execute the above task i'm receiving the following error. Can some one help me out what that permission issue is.
SSIS package "Package2.dtsx" starting.
Error: 0xC002F210 at Getting the tables in MS Access, Execute SQL Task: Executing the query "SELECT name FROM Msysobjects WHERE type=1 AND name NOT LIKE 'Msys*'" failed with the following error: "Record(s) cannot be read; no read permission on 'Msysobjects'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Getting the tables in MS Access
Warning: 0x80019002 at Package2: The Execution method succeeded, but the number of errors raised (1) 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 "Package2.dtsx" finished: Failure.
Thanks in advance