0

I have an xlsm file with macros that enable data collection from OLE DB source. I just need to make an import package with it in sql server. But when I'm trying to use import/export wizard or ssis-it shows that it can not deal with .xlsm extentions. Is there any way to deal with it?

Keithx
  • 2,994
  • 15
  • 42
  • 71
  • Why are you using Excel macros to pull data instead of using SSIS to pull the data from the OLEDB sources? An `xlsm` file needs an Excel installation to run the macros so it's not the best format for transferring data (to put it mildly). – Panagiotis Kanavos Nov 18 '14 at 10:09
  • because we use remote connection with one server and local machine with another. so it's the only way to deal with it – Keithx Nov 18 '14 at 10:10

2 Answers2

1

follow this : 1-Create an Excel Connection Manager to refer to ANY other spreadsheet with a "valid" XLS or XLSX extension. (You don't have to save your XLSM file "as" an XLSX.) 2-Select the Excel Connection Manager in your Connection Managers pane. 3-Open the Properties Window (F4). 4-Change the "Server Name" property to the full pathname of your XLSM file.

note: check the file should not be in read only mode

Rahul Sharma
  • 453
  • 3
  • 10
1

Had the same issue (SQL Server 2008 R2), Rahul's solution worked for me, but I just want to add a couple of things:

  • Select Excel 2007 in Excel version when creating Excel Connection.
  • Use SQL Command as Data access mode when editing the Excel Source. Visual Studio crashed for me every time I was trying to use Table or view.
rufus1530
  • 765
  • 4
  • 19