Office 365 and Access Engine

If you are developing an application using an Office 365 Access database, you may come across this error.

System.InvalidOperationException HResult=0x80131509 Message=The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. Source=System.Data StackTrace: at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at 

If you are like myself, the instant reaction is to look for an Office 365 runtime. Indeed there is one available, however, this does not only not solving your problem, but it also overtake your proper "full" Access when you are trying open an accdb file.

So, what should I use then?  The answer is an Access Engine.  You may ask WTF, an Access Engine?  I am afraid I don't have an answer for this.  I can only say the Access Engine works but the runtime doesn't.  You can find the engine at:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

After the installation, your application should be able to connect to OLEDB database as usual.

Here is an example I wrote:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString =
                @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=SampleDatabase.accdb;Persist Security Info=False;";

            Console.WriteLine($"Connecction String: {connectionString}");

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();

                OleDbCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT TOP 1 message FROM Sample";

                string message = (string)cmd.ExecuteScalar();

                Console.WriteLine($"Message from database: {message}");
            }
        }
    }
}

If you still encounter the "Microsoft.ACE.OLEDB.12.0" error after installing the engine, you may want to change the platform target from ANY to x86 for 32bit or x64 for 64bit engine.

Updated 2020-02-26

If you have stability issues, e.g. SSIS project keeps crashing when using OLEDB to connect to a data source, you may want to downgrade the engine to 2010 (https://www.microsoft.com/en-gb/download/details.aspx?id=13255)

SSIS Tips and Tricks

Recently I have got myself involved in developing Sql Server Integrate Services (SSIS) packages.  While I was doing it I had picked up a few tips and tricks that I would like to share here.

First trick is to pass parameters from Job Agent into SSIS.  It is possible to assign values to your package on Agent Job's configuration tool.  Details can be found at:

http://www.buildingmeaning.com/?p=171

For example, if you are a package variable called Subject, then you can set (override) this variable with the steps decribed in the link by adding the property path \Package.Variables[User::Subject].Properties[Value] with the desired value.

Second trick is to connect to your data source setup in your package from Script Task.  For example, if you have a data source called "My Data Source" setup in your SSIS, and you can use it in Script Task by doing:-

SqlConnection conn = Dts.Connections["My Data Source"].AcquireConnection(Dts.Transaction) as SqlConnection;


http://msdn.microsoft.com/en-us/library/ms136018.aspx


Third is actually a tip for preventing SQL injection by using parameterized sql

SqlCommand command = new SqlCommand(commandText, conn);
command.Parameters.Add("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = customerID;

The above specifics the ID is an integer type, so it can prevent values that is not a valid integer.

Hope you find them useful.