OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error

or

ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)" returned message "the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.".

Here is the quick resolution for this fix, try run below commands in SQL Server

USE [master] 
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

This resolved my problem!

Can't locate Microsoft.Office.Interop.Word

Microsoft Office primary interop assemblies (PIAs) are used for development purpose when you are working with Word or Excel.

Usually Microsoft Interop libraries are installed automatically at the time of Microsoft Office in development computer. However, in some cases you might need to install PIAs separately.

Download : Microsoft Office 2010: Primary Interop Assemblies Redistributable

How to: Show the Developer Tab on the Ribbon

To access the Developer tab on the ribbon of an Office application, you must configure it to show that tab because it doesn't appear by default.

1. Right click anywhere on the ribbon, and then click Customize the Ribbon. 

2. Under Customize the Ribbon, on the right side of the dialog box

3. Check the Developer check box. 

4. Click OK.

5. You can find the Developer tab next to the View tab. 

Hope this helps!!

How to change Excel column names form A,B,C to 1,2,3 or vice versa

This happens when excel column labels are numeric rather than alphabetic. For example, instead of seeing A, B, and C at the top of your worksheet columns, you see 1, 2, 3, and so on.

The A1 Reference Style

By default, Excel uses the A1 reference style, which refers to columns as letters (A through IV, for a total of 256 columns), and refers to rows as numbers (1 through 65,536). These letters and numbers are called row and column headings.

The R1C1 Reference Style

Excel can also use the R1C1 reference style, in which both the rows and the columns on the worksheet are numbered. The R1C1 reference style is useful if you want to compute row and column positions in macros. In the R1C1 style, Excel indicates the location of a cell with an "R" followed by a row number and a "C" followed by a column number.

To toggle between A1 and R1C1 Reference styles 

1. Start Microsoft Excel.
2. On the Tools menu, click Options.
3. Click the General tab.
4. Under Settings, click to clear the R1C1 reference style check box (upper-left corner), and then click OK.

If you select the R1C1 reference style check box, Excel changes the reference style of both row and column headings, and cell references from the A1 style to the R1C1 style.

How to add/concatenate string in Excel

The CONCATENATE function in Excel is designed to join different pieces of text together or combine values from several cells into one cell.

The syntax of Excel CONCATENATE is as follows:
CONCATENATE(text1, [text2], …)

Concatenating the values of several cells
The simplest CONCATENATE formula to combine the values of cells A1 and B1 is as follows:
=CONCATENATE(A1, B1)

Concatenating a text string/formulas and cell value
There is no reason for the Excel CONCATENATE function to be limited to only joining cells' values. You can also use it to concatenate various text strings and formulas to make the result more meaningful.
For example:
=CONCATENATE("Last Updated: ",TEXT(TODAY(), "mm/dd/yyyy"))

How to Kill Excel Ghost Process in SSIS using VB.NET

Here is the simple method to kill Excel Ghost process which is running behind scene while running SSIS package. Here is a small method to kill Excel process if we know the excel caption or excel title we want to close.
Code snippet of the method to kill Excel process

Private Sub KillExcellProcess(ByVal excelCaption As String)
    Dim proc As System.Diagnostics.Process
    For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
        If (proc.MainWindowTitle = excelCaption) Then
            proc.Kill()
        End If
    Next
End Sub

Use this method in SSIS script task to kill the Excel Ghost process by calling this method in your code

 

Dim xlApp As Excel.Application
Dim xlBook As Excel.WorkbookClass
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlApp.Visible = False
xlApp.DisplayAlerts = False
' Once done with excel stuff and when you want 
' to kill excel use the below lines of code
Dim sCaption As String
sCaption = xlApp.Caption
KillExcellProcess(sCaption)

 

Hope this helps!!

How to Kill specific Excel Ghost Process generated in SSIS using VB.NET

There are some scenarios where we might need to kill excel process programmatically.  Even though we use proper error handling and try catch to clear all excel objects still the excel ghost process remains in usage. So for these cases we might need to kill the excel process generated in SSIS package by identifying its process.   Here is how we can kill specific excel process by getting the process id of the excel that is been generated by SSIS package in script task. Refer to my previous blog for kill process by its name

<Runtime.InteropServices.DllImport("user32.dll")> _
 Private Shared Function EndTask(ByVal hWnd As System.IntPtr) As Integer
 End Function
<Runtime.InteropServices.DllImport("user32.dll")> _
 Private Shared Function FindWindow(ByVal lpClassName As String, ByVal lpWindowName As String) As IntPtr
 End Function
<Runtime.InteropServices.DllImport("kernel32.dll")> _
 Private Shared Function SetLastError(ByVal dwErrCode As Integer) As IntPtr
 End Function
<Runtime.InteropServices.DllImport("user32.dll", SetLastError:=True)> _
 Private Shared Function GetWindowThreadProcessId(ByVal hWnd As System.IntPtr, ByRef lpdwProcessId As Integer) As Integer
 End Function

 Public Sub EnsureProcessKilled(ByVal MainWindowHandle As System.IntPtr, ByVal Caption As String)
     SetLastError(0)
   ' for Excel versions <10, this won't be set yet
     If System.IntPtr.Equals(MainWindowHandle, System.IntPtr.Zero) Then
       MainWindowHandle = FindWindow(Nothing, Caption)
     End If
     If System.IntPtr.Equals(MainWindowHandle, System.IntPtr.Zero) Then
         Return
     End If
     ' at this point, presume the window has been closed.
     Dim iRes As Integer = 0
     Dim iProcID As Integer = 0
     iRes = GetWindowThreadProcessId(MainWindowHandle, iProcID)
     ' can?t get Process ID
     If iProcID = 0 Then
        If EndTask(MainWindowHandle) <> 0 Then
             Return
         End If
         ' success
         Throw New ApplicationException("Failed to close.")
     End If
     Dim proc As System.Diagnostics.Process = Nothing
     proc = System.Diagnostics.Process.GetProcessById(iProcID)
     proc.CloseMainWindow()
     proc.Refresh()
     If proc.HasExited Then
         Return
     End If
     proc.Kill()
 End Sub

Use this method in SSIS script task to kill the Excel Ghost process by calling this method in your code

 

Dim sVer As String
sVer = xlApp.Version
Dim iHandle As IntPtr = IntPtr.Zero
If Val(Convert.ToDouble(sVer)) >= 10.0 Then
 iHandle = New IntPtr(CType(xlApp.Parent.Hwnd, Integer))
End If
'To kill process created by this job by process id
EnsureProcessKilled(iHandle, xlApp.Caption)

Hope this help!!

Export GridView to Excel in ASP.NET

Here is the same code to export GridView from C# in ASP.NET

protected void ExportButton_Click(object sender, EventArgs e)
{
   Response.AddHeader("content-disposition", "attachment;filename=Contacts.xls");
   Response.Charset = String.Empty;
   Response.ContentType = "application/vnd.xls";
   System.IO.StringWriter sw = new System.IO.StringWriter();
   System.Web.UI.HtmlTextWriter hw = new HtmlTextWriter(sw);
   ContactsGridView.RenderControl(hw);
   Response.Write(sw.ToString());
   Response.End();
 }

Hope this is useful