Fix : Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

I tried to read CSV file from SQL Server and seen vairous errors. Below thread helps to fix the issue

Syntax to read csv file from SQL Server

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Error
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

If you run on a 64bit environment we need to download new version of 2010 office system driver which supports both 32bit, 64 bit environments,the provider name is Microsoft.ACE.OLEDB.12.0.
Download Microsoft Access Database Engine 2010 Redistributable from Microsoft and Install suitable version of software on the machine.

After successful installation of provider you can verify it in the below location
SSMS -- Server Objects - Linked Server -- Providers -- Microsoft.ACE.OLEDB.12.0.

Modify your query as below

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

But not like below
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Driver={Microsoft Text Driver (*.txt; *.csv)};
 DefaultDir==E:\DataSqlCmd2009','SELECT * FROM DataSqlCmd2008.csv')
If you run above syntax you will get the below error,
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".

Run the command
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Error
 Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

To fix the above error, run the below command

Use master
go
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
Re-run the command again

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Again Error
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Make sure your SQL Service account has full permissions on the directory where csv files are exists.
Re-run the command again

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;
Database=C:\kalyan\CsvFiles','SELECT * FROM filename.csv')

Again Error
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "SELECT * FROM filename.csv". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Make sure the service account has full permissions to
c:\users\service account folder\appdata\local\temp folder -- If you receive error after doing this also we need to modify a value in the registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\Providers\Microsoft.ACE.OLEDB.12.0\
Create a new dwordvalue with the below key name and value

key name - AllowInProcess value - "dword:00000001" Close the registry and now it will work.

Run the below commands
USE [master] 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 
GO 
If you are still facing the error might be below is the reason
OS - Windows 64 bit
SQL Server - 64 bit
Office Products - 32 bit
Then you need to uninstall 32bit office products and install 64bit Microsoft Access Database Engine.