Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Create/write to an Excel 2007/2010 spreadsheet from an SSIS package Expand / Collapse
Author
Message
Posted Monday, February 10, 2014 11:26 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 124, Visits: 891
Comments posted to this topic are about the item Create/write to an Excel 2007/2010 spreadsheet from an SSIS package
Post #1540043
Posted Tuesday, February 11, 2014 12:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 13,258, Visits: 10,137
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1540050
Posted Tuesday, February 11, 2014 1:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:19 PM
Points: 2,891, Visits: 1,781
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
Post #1540079
Posted Tuesday, February 11, 2014 2:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 13,258, Visits: 10,137
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1540086
Posted Tuesday, February 11, 2014 2:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:32 AM
Points: 5, Visits: 67
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
Post #1540094
Posted Tuesday, February 11, 2014 3:01 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:06 AM
Points: 337, Visits: 1,981
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
Post #1540099
Posted Tuesday, February 11, 2014 3:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:32 AM
Points: 5, Visits: 67
yes, I followed the article exactly. The files are saved to a local directory and the correct reference set accordingly.
Post #1540100
Posted Tuesday, February 11, 2014 6:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 124, Visits: 891
Is EPPlus showing up under references as shown in this screen shot?

Post #1540172
Posted Tuesday, February 11, 2014 6:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 10, 2014 8:32 AM
Points: 5, Visits: 67
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
Post #1540173
Posted Tuesday, February 11, 2014 7:24 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:38 PM
Points: 124, Visits: 891
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

Post #1540182
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse