SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Create/write to an Excel 2007/2010 spreadsheet from an SSIS package


Create/write to an Excel 2007/2010 spreadsheet from an SSIS package

Author
Message
Stan Kulp-439977
Stan Kulp-439977
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 1179
Comments posted to this topic are about the item Create/write to an Excel 2007/2010 spreadsheet from an SSIS package
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)

Group: General Forum Members
Points: 131413 Visits: 13345
What exactly is wrong with the Excel Destination in SSIS?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Dave Poole
Dave Poole
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31966 Visits: 3664
Koen Verbeeck (2/11/2014)
What exactly is wrong with the Excel Destination in SSIS?


Nothing. This article is demonstrating a proof of concept using a library without having to install Excel on a server. If all you want is a straight dump of data from SQL Server to Excel then the Excel Destination is fine.

If you want a more complex spreadsheet produced then the library will allow a much more tailored output. Apache POI allows something similar and there is a .NET port called NPOI on codeplex. http://npoi.codeplex.com/

Where it really gets interesting is that these libraries allow you to read Excel and all the bizarre formatting that gets put into a typical workbook.
I'm sure there will be an excellent follow up article on doing just that.

This is a good introduction starting with a nice simple example. The author hasn't fallen into the trap of producing an overly complicated example.

LinkedIn Profile
www.simple-talk.com
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)SSC Guru (131K reputation)

Group: General Forum Members
Points: 131413 Visits: 13345
David.Poole (2/11/2014)
Koen Verbeeck (2/11/2014)
What exactly is wrong with the Excel Destination in SSIS?


Nothing. This article is demonstrating a proof of concept using a library without having to install Excel on a server. If all you want is a straight dump of data from SQL Server to Excel then the Excel Destination is fine.

If you want a more complex spreadsheet produced then the library will allow a much more tailored output. Apache POI allows something similar and there is a .NET port called NPOI on codeplex. http://npoi.codeplex.com/

Where it really gets interesting is that these libraries allow you to read Excel and all the bizarre formatting that gets put into a typical workbook.
I'm sure there will be an excellent follow up article on doing just that.

This is a good introduction starting with a nice simple example. The author hasn't fallen into the trap of producing an overly complicated example.


The reason I reacted this way is because the article summary states the following:

but SSIS lacks direct support for writing Excel files


which just isn't true.

I get that SSIS can only do data dumps to Excel and that if you want nicely formatted Excel files you either use a library - like in the article - or you use an SSRS report. But SSIS does support writing to Excel.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
t.mozelt
t.mozelt
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 103
Hi,
thanks for your interesting article! I would like to run your test scenario but I am stuck with following error:
...
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1' or one of its dependencies. The system cannot find the file specified.
File name: 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'
at ST_ff7fa3f713524285b0356dc135d66b25.csproj.ScriptMain.Main()
...
Does anyone have a hint for me?
many thanks, Thomas
Samuel Vella
Samuel Vella
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4189 Visits: 2144
t.mozelt (2/11/2014)
Hi,
thanks for your interesting article! I would like to run your test scenario but I am stuck with following error:
...
Error: 0x1 at Script Task: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.IO.FileNotFoundException: Could not load file or assembly 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1' or one of its dependencies. The system cannot find the file specified.
File name: 'EPPlus, Version=3.1.3.0, Culture=neutral, PublicKeyToken=ea159fdaa78159a1'
at ST_ff7fa3f713524285b0356dc135d66b25.csproj.ScriptMain.Main()
...
Does anyone have a hint for me?
many thanks, Thomas


Did you download the EPPlus files and set the reference correctly?

the package might not retain the DLL files internally so you will need to make sure that the DLLs are available on all target servers
t.mozelt
t.mozelt
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 103
yes, I followed the article exactly. The files are saved to a local directory and the correct reference set accordingly.
Stan Kulp-439977
Stan Kulp-439977
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 1179
Is EPPlus showing up under references as shown in this screen shot?


t.mozelt
t.mozelt
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 103
yes, it is shown. i also tried to set specific version true/false. Same error. It builds fine but if I run the package the error shows up...
sorry

by the way- I am using Visual Studio2008 with .Net 3.51 Sp1 on a Win7 64bit machine
Stan Kulp-439977
Stan Kulp-439977
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5138 Visits: 1179
This article has some troubleshooting ideas for this error:

http://stackoverflow.com/questions/1540887/system-io-filenotfoundexception-could-not-load-file-or-assembly-when-the-asse
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search