June 1, 2009 at 10:18 pm
Hi All,
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.
Error:
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
June 2, 2009 at 1:19 am
June 2, 2009 at 8:44 am
Hi Thanks for your reply.
I tried the way you said :
Set View-- >Options/ General/Show System objects to "yes".
When I set that I could only see those system objects in MS Access db but getting the same error in SSIS.
June 2, 2009 at 11:13 am
Are you able to query user tables in the Access db?
Just wondering whether any additional permissions are required to query Access' system objects.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 2, 2009 at 8:57 pm
Hi,
I could not access the MS Access system tables from SSIS.
June 2, 2009 at 8:58 pm
Yes I could access the MS Access user tables from SSIS using Microsoft Jet OLE DB provider but could not access system tables like Msysobjects.
June 2, 2009 at 9:50 pm
You could try making a view (in Access) of MSysObjects and attempt to access that?
--edit: (cough) Oops! I think they're just called 'queries' in Access 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 3, 2009 at 4:52 am
Hello again,
Now that the msysobjects table is visible in the Access GUI, you should be able to grant read permission to the table for the user-id that you are supplying in your Connection (from SSIS).
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
October 9, 2019 at 8:19 pm
Sorry to ressurect a 10 year old thread...
However, I experience the same issue, when i run my 'Execute SQL Task' querying my AccessDB connection
"Record(s) cannot be read; no read permission on 'Msysobjects'."
I can set the permissions if i go into the access table, but is it possible to set the permissions in a SSIS script task using c# or VB?
I cant seem to find any script template via multiple google searches
Thanks in advance
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply