Click here to monitor SSC
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
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
David.Poole
David.Poole
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3662 Visits: 3111
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

Newbie on www.simple-talk.com
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16380 Visits: 13199
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?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
t.mozelt
t.mozelt
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 92
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
Old Hand
Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)Old Hand (391 reputation)

Group: General Forum Members
Points: 391 Visits: 2140
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
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 92
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
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

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


t.mozelt
t.mozelt
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 92
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
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
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