Hi,

I am loading from SQL Server 2008 to Access 2010 using SSIS. One of the columns in the table I am loading into is a Number datatype and Fieldsize is long integer. The values are being truncated, so I want to change the Fieldsize to DOUBLE.  However, when I do that I receive the error below. What should I do? I would like not to change my Windows registry.

__________________________________________________________________________________________________

This error can be caused by one of the following:

The maximum number of columns allowed in a table or the maximum number of locks for a single file is exceeded.
The indexed property of a field is changed from Yes (Duplicates OK) to Yes (No Duplicates) when duplicate data exists in the table.
An expression is not specified in the Expression property of a calculated field. 
If the maximum number of locks per file was exceeded, you can increase the number by editing a registry entry. However, this is not a recommended option.

If you use Registry Editor incorrectly, you could cause serious problems that require you to reinstall the operating system. Microsoft cannot guarantee that you can solve problems that result from using Registry Editor incorrectly. Use Registry Editor at your own risk.

Make a backup of the registry. Find the MaxLocksPerFile registry value by using the Windows Registry Editor, and then increase the value. The MaxLocksPerFile value is saved as part of the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE

If the Indexed property of a field and duplicate data is located in the table, reset the Indexed property to the previous setting, or remove duplicate records from the table.

----------------------------------------------

While I was loading to the same table a few days ago, I received a warning and the task took approx 9 hours. I am attaching the screen shot.

• Edited by NitaM 15 hours 7 minutes ago
• Moved by Edward ZMicrosoft contingent staff 3 hours 33 minutes ago related with ssis

Hi. Not sure if that's really the answer to your problem, but you can set that value from within Access. Try executing the following line in the Immediate Window:

DBEngine.SetOption dbMaxLocksPerFile, 50000

Or whatever huge number you would like to use. Hope that helps...

where do I enter the line? First line in the SSIS source query?

No, it would be something you would do within Access. When you said you tried to change the field type, where were you doing that? In SQL Server?

PS. As I mentioned above, I wasn't sure if what you found during your search is really the answer to your original problem, but I was just offering a way to do it without hacking the registry because you said you were not comfortable modifying the registry yourself. I figure, if you tried running the code to modify the registry and it worked, then you're done. If not, then you'll know to look for another solution.

• Edited by the DB guyMVP 12 hours 19 minutes ago

Truncated Longs!? In all my years using Access and SQL Server I have never seen a number get truncated. Strings, yes, but that's another issue entirely. Are you sure they are truncated and not just hiding the right side? Widen the field in your table and look again.

Yes, it is getting truncated - rounded to the nearest decimal actually. For example, a 73.2 becomes 73.0 and 36.8 because 37.0.

There are other columns that are Number and Double and they work fine. The only column that is Number and long integer is the issue.

My query is run SSIS and the destination is MS ACCESS. The source is SQL Server. So I am not really opening Access to run a query, simply viewing after the load. Due to the issue, I checked in the design view of the table and noticed the field size different than the other Number columns.

I am restricted from changing a Registry entry at work even with my Administrative access.

Hi NitaM,

This forum is for Developer discussions and questions involving Microsoft Access. Your issue is more related with SSIS, I will move this thread to the more related forum.

Reference: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlintegrationservices

Thanks for your understanding.

Best Regards,

Edward