Unable to initialize Microsoft.ACE.OLEDB.12.0 OLE DB provider data source object for linked server (null)

Question

DECLARE @PATH NVARCHAR(1000) = N'\\MY-SERVER\C$\Folder\\'
DECLARE @TABLE NVARCHAR(50) = SUBSTRING(@FILENAME,0,CHARINDEX('.',@FILENAME))
DECLARE @SQL NVARCHAR(4000) = 
    N'IF OBJECT_ID(''dbo.' + @TABLE + ''' , ''U'') IS NOT NULL 
    DROP TABLE dbo.[' + @TABLE + ']
    SELECT * INTO [' + @TABLE + ']
    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0''
                    ,''Text; Database='+@PATH+';''
                    ,''SELECT * FROM [' + @FILENAME + ']'')'

EXEC(@SQL)

Today I ran into a problem with the Microsoft.ACE.OLEDB.12.0 driver in SSIS 2012. The script above is in the stored procedure, which dynamically loads the CSV into the database based on the current file, which is delivered through the SSIS loop in which the stored procedure is stored. There are files in the directory.

The stored procedure runs correctly when executed directly in SQL Server Management Studio.

It works to this day. Today I get the following error:

"EXEC [dbo]. [CreateAndImportCSVs]?" : " OLE DB" Microsoft.ACE.OLEDB.12.0 " " (null) ".". : , ResultSet , .

!

Edit

, , , , Windows, ! :

Microsoft Office Access Runtime 2007 (SP3) http://support.microsoft.com/kb/2526310

Microsoft Office 2007 (KB967642) http://www.microsoft.com/downloads/details.aspx?FamilyId=E93AB1BE-ADE6-4FF8-8637-DBD3EBE3C5C5&displaylang=en

Answer 1

. , , :

Microsoft Office 2010 (KB4011188) 64- https://support.microsoft.com/kb/4011188

Microsoft Office 2010 (KB2553347) 64- http://support.microsoft.com/kb/2553347

Microsoft Office 2010 (KB2553338) 64- https://support.microsoft.com/kb/2553338

!

Answer 2

, :

• In Process Dynamic Provider ACE.
• Temp
• MemToLeave
• , EXCEL

• , " " (1)

  USE [master]
  GO
  
  EXEC sp_configure 'Show Advanced Options', 1
  RECONFIGURE
  GO
  
  EXEC sp_configure 'Ad Hoc Distributed Queries', 1
  RECONFIGURE
  GO
  
  EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
  GO
  
  EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
  GO
  

Microsoft.ACE.OLEDB.12.0 " "

, :

• OLE DB Microsoft.Ace.OLEDB.12.0 (null)

Answer 3

This may not be the solution, but you should run SQL with:

sp_executesql @SQL

It protects against injections and wotnot.