converting xlsm to xls using a script task in SSIS

Question

converting xlsm to xls using a script task in SSIS

NewbieCoding

Say Hey Kid

Points: 689
More actions
December 9, 2015 at 1:38 pm

#399446

i need to import a file that has macros without additional drivers installed. If I use the existing drivers for excel i get an error stating that i dont have the correct version to use the driver .xlsx or .xls. Anyone have a solution on how to accomplish this?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

NewbieCoding (12/9/2015)
i need to import a file that has macros without additional drivers installed. If I use the existing drivers for excel i get an error stating that i dont have the correct version to use the driver .xlsx or .xls. Anyone have a solution on how to accomplish this?

Yes. Unfortunately, you've identified the solution above.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 2

Unfortunately I do not know VB or C#.net to accomplish this

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Ah. Understood.

The only thing that you'll be able to do in SQL Server is import the data from the spreadsheet whether it's .xlsx or .xls. The macros won't come with it. Neither will most of the meta-data. It'll be mostly raw data. The cool part is that it does not require any knowledge of VB or C# whatsoever.

If that's what you want to do, I can put a small example together tonight along with some instructions on how to correctly install the required ACE driver/provider on the SQL Server side. Even if you can't personally install the driver, your DBA could (assuming that you're not the DBA here).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

NewbieCoding Say Hey Kid Points: 689 More actions · Answer 4

I was hoping of saving the xlsm file to xlsx file using SSIS and then importing the file into a table. so all we have to do is call the package and run automatically

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

I doubt very much that your problem is caused by macros. I suspect it's more like that you don't have the latest version, or you don't have the right 32 bit vs 64 bit driver.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

NewbieCoding (12/11/2015)
I was hoping of saving the xlsm file to xlsx file using SSIS and then importing the file into a table. so all we have to do is call the package and run automatically

The XLSM file is simply a "Macro enabled file". You shouldn't need to copy it to an XLSX file. You should (although I've not tried because I only gest XLS and XLSX files) be able to open it with OPENROWSET using the ACE drivers that I previously identified and the method. You don't actually need SSIS to do this in an automated fashion but you could use SSIS for it.

What does this spreadsheet look like? I ask because spreadsheets are rarely in the form of "normalized table data" that can be easily imported without human touch like when they add columns each month. I know how to fix that but it's not worth the time I'd spend explaining how to do it if you don't need that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)