January 22, 2016 at 7:56 am
I am with Ken on this one. I admire your solution but there are less complex ways of solving the problem. Good work though.
January 22, 2016 at 8:24 am
stan.geiger (1/22/2016)
I am with Ken on this one. I admire your solution but there are less complex ways of solving the problem. Good work though.
I use it to create invoices for hundreds of chains of vendors with tens or hundreds of individual outlets. Each invoice contains a header for the chain followed by headers, details and totals for each outlet followed by a set of grand totals for the chain.
How could I do this in a "less complex" way?
January 22, 2016 at 9:44 am
Koen Verbeeck (2/11/2014)
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.
I find the native support for Excel lacking personally.
Create a simple dump... Data Flow, OLE DB Source, pick a table... Excel Destination, connect them, then create the excel connection manager, and try to create a sheet... if you click the dropdown... you get there are no sheets (because there is no file... duh)... you can't type in the name of the sheet, and if you click the button to make a new sheet nothing happens (at least not for me when developing for SSIS 2014 using VS 2013).
Compare that to the CozyRoc Excel Destination Plus, which works and is sensible.
http://www.cozyroc.com/ssis/excel-destination
If you follow the same process, you hit a page where you can free form type in the name of the sheet to put the data on, which lets you get to the mapping of fields much faster than the MS destination. Of course purchasing their add-ons gives you a whole pile of other awesome features in addition to excel, but these days I don't even bother with the MS source or destination because they pale in comparison.
January 24, 2016 at 9:07 am
Why are you still running Windows XP?
January 24, 2016 at 10:35 am
Dave Pendleton (1/24/2016)
Why are you still running Windows XP?
This article was originally published two years ago when my company was still using XP and when XP was still supported.
January 25, 2016 at 3:01 pm
I've successfully duplicated your solution, except I did it in a .net web form. My project is using the 4.0 .NET framework.
I tried to reproduce your solution in SSDT ,--here is what happened--
I am using VS 2012, so my IDE didn't behave exactly like yours did. . I tried to add the EPPPlus to the GAC, but can't even find the GAC utility on my machine. So I simply set it up as a reference, and with the line "Dim p As New ExcelPackage(newFile)" as part of the code (no other code from the example afterwards), I get a DTS Script Task runtime error.
When I change the Target .NET framework to 3.5, the lines "<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase" is underlined with warnings "Type Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute is not defined" and "Type Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase" is not defined"
It seems that the EPPlus dll is a great tool. Tricky to get set up in SSIS, and I'm investigating how to get it working in 2012 SSIS, using the .net framework 4.0. I'd like to be able to just copy my code from the code behind of the webform, but seems the complexities of the microsoft solutions are making it difficult (or, more likely, I just don't know enough yet).
Thanks for the article. I didn't know about the EPPlus dll, so I learned something. which is the entire point of me being involved in this site...
February 25, 2016 at 4:33 pm
Elaborate article, thanks.
April 5, 2016 at 6:18 am
Did you manage to get the EPPPlus dll working?
I followed this article, my case SQL server is on a remote machine
have referenced the dlls in the local dev environment, compilation etc passed.
In the remote machine have copied epp plus dll to a local directory and executed GACUtil.exe to install.
when i execute the package in SSIS,
i get the error
"Exception has been thrown by the target of an invocation."
any one came across this scenario before, if yes please suggest steps followed to resolve.
Thanks
April 5, 2016 at 8:43 am
I've had trouble with SSIS and DLLs when installing in the GAC. It seems to not recognize them in SSIS packages. The way I get DLLs to work in SSIS is to copy them to the Microsoft SQL Server program installation directory under Program Files\Microsoft SQL Server\110 (or whatever version you are using) and then in the \DTS\Binn and \SDK\Assemblies folders. I think you only need one location, but I forget which one is used when executing. It doesn't hurt to be in both. Also, note that if you are running the SSIS as 32-bit, you will want to copy the DLLs into the Program Files (x86) installation structure for SQL. If 64-bit, then just Program Files.
Hope this helps.
April 5, 2016 at 10:00 am
That did the trick!!
Thank you very much 🙂
It works!!
June 4, 2016 at 6:21 pm
there is 64-bit ACE engine now, too.
But, don't install 32-bit ACE *and* 64-bit ACE on same server. They interfere with each other (been there, done that).
Especially don't do this on a computer with Office, as the combination will break the Office apps, each in their own peculiar but not-so-fatal way, until you uninstall the different bitness ACE engine compared to your Office apps (again, been there, done that).
June 4, 2016 at 6:29 pm
the gac util will be in the Microsoft.Net framework file (C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\gacutil.exe)
August 2, 2016 at 12:25 pm
There is a 64-bit version of the Access Database engine, as well as 32-bit. On a 64-bit computer, both seem to coexist OK. But only if you don't have any Office apps installed on the same system.
One or the other Access Database engine "bitness" can cause the Office apps of different "bitness" to not run well or at all, if both Access Database engines are installed on the same 64-bit OS computer.
August 2, 2016 at 12:44 pm
That being said, I'm using Powershell + EPPlus + SQLPS to push data into a .xlsx file, and simply use SSIS as the process control layer. Seems to work OK for me.
Like others, the usual ways before EPPlus (and not being able to buy CozyRoc's custom controls or others) for getting data into and out of Excel via SSIS just seem...lacking, in my experience. the Excel Source and Destination data flow tasks work, barely. For some things, that's more than OK. Using a template spreadsheet file is...hacky.
Solutions that require Excel to be installed on the SSIS server work, but they're definitely not what Microsoft considers to be even good practice, as Excel isn't really developed to be a multi-user solution. So that leaves out some of the other options, such as Excel Automation thru its COM Objects, however you want to approach that. Plus, now you have Excel doing its usual data interpretations on any data being imported that may not end up as desired or expected (like, importing US SSNs or Zip codes properly). The ACE engine works too, sometimes good enough, sometimes not.
I could, but currently do not, want to write custom data flow controls (source & destination) using EPPlus, so kicking out to Powershell works good enough, plus gives me control of the output format styles, putting the output data into an Excel Table, etc. If I were contemplating that, I'd also seriously just contemplate trying to get work to buy the CozyRoc controls.
And there's more available to do through EPPlus, too...set up Pivot tables, set up many (but not all types of) charts, etc.
December 15, 2016 at 1:50 pm
How do you get EPPlus.dll installed in GAC on windows server 2008 R2 without GACUTIL or Win SDK?
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply