February 11, 2014 at 8:16 am
Honestly, even if we still have to deal with this AccessDatabaseEngine.exe garbage I still think your EPPlus stuff is wonderful and will definitely use it.
With SoftArtisans ExcelWriter, you don't need anything but standard data sets and data tables. Again, can be expensive, but if you are doing a ton of Excel automation and need full control (formatting, freeze panes, etc.), you save money in the long run. Additionally, it has 32 and 64 bit versions and likely can handle any scenario you throw at it.
February 11, 2014 at 12:53 pm
I figured out how to do this and found it useful so I thought I would share what I had learned in case other people might find it useful.
I didn't mean to offend anyone by ignoring the Excel destination. I just never found it very useful for what I needed to do. I have also found that an Excel file exported from SSRS can be a sloppy mess with overlapping fields and columns.
This is a free, reliable solution and it allows you to construct the spreadsheet the way you want it.
February 12, 2014 at 10:45 am
My colleague and I had the same error message ("Could not load file or assembly"). We resolved it by adding the EPPlus assembly to the GAC as described in this article: http://emendezrivas.com/45/ which has a link to these specific instructions: http://emendezrivas.wordpress.com/2011/03/19/add-an-assembly-to-the-gac/).
February 13, 2014 at 2:39 am
Thanks a lot! Your solution worked for me!!!
And now the whole things looks pretty good
🙂
February 13, 2014 at 5:33 pm
I also find the Excel external data source feature handy. You can put your query into a view or select/insert into a transient table if view isn't possible for your query and slurp in up to 1 million records for excel 2010 (64bit is what I have) on my desktop. The insert into option is particularly handy sometimes when I have a query that isn't practical to run all at once and must be run in smaller chunks (with a not exists for pk in my transient table so no duplicates). From an excel template you choose Data > From Other Sources > From SQL Server
I think you can also have your query live in the excel file connection, but I haven't explored that.
February 18, 2014 at 9:46 am
I have the same problem. Any solutions to this issue.
February 18, 2014 at 9:46 am
I have the same problem, any solutions to this issue?
February 18, 2014 at 10:07 am
bindu.reddy (2/18/2014)
I have the same problem. Any solutions to this issue.
kathypdx gave a resolution last week:
My colleague and I had the same error message ("Could not load file or assembly"). We resolved it by adding the EPPlus assembly to the GAC as described in this article: http://emendezrivas.com/45/ which has a link to these specific instructions: http://emendezrivas.wordpress.com/2011/03/19/add-an-assembly-to-the-gac/).
February 18, 2014 at 11:02 am
That worked! Thank you so much.
March 12, 2014 at 1:24 pm
I am having a little trouble getting this to work.
I keep getting the 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_2a6e4db473504d2f985a440256eaa3d9.csproj.ScriptMain.Main()
I added the Reference exactly as you specified.
I put the files in C:\EPPlus 3.1.3 and the properties in the Script indicate that it is there.
March 12, 2014 at 3:30 pm
christine.collins 55481 (3/12/2014)
I am having a little trouble getting this to work.I keep getting the 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_2a6e4db473504d2f985a440256eaa3d9.csproj.ScriptMain.Main()
I added the Reference exactly as you specified.
I put the files in C:\EPPlus 3.1.3 and the properties in the Script indicate that it is there.
Add the assembly to the GAC (global assembly cache) as kathypdx said on an earlier post:
My colleague and I had the same error message ("Could not load file or assembly"). We resolved it by adding the EPPlus assembly to the GAC as described in this article: http://emendezrivas.com/45/ which has a link to these specific instructions: http://emendezrivas.wordpress.com/2011/03/19/add-an-assembly-to-the-gac/[/url]).
June 7, 2014 at 9:04 pm
Koen Verbeeck (2/11/2014)
What exactly is wrong with the Excel Destination in SSIS?
Because of your question, I decided to figure out the Excel destination and write an article on how to use it.
Now I have a question for you; have you ever tried to use the Excel destination?
It is, for all practical purposes, unusable, and even if you do manage to get it to work, it doesn't give you any more functionality than writing a CSV file.
So, have you ever used the Excel destination? And if you have, how is it better than simply writing a CSV file?
September 5, 2014 at 8:51 am
There is nothing inherently wrong with the Excel destination, but over the last several years I have found some significant limitations.
1) SSIS can only recognize and use the 32-bit MS Office drivers. Microsoft only allows either 32-bit or 64-bit drivers on a system - they cannot peacefully coexist.
2) To export to Excel you need to have a 32-bit copy of MS Office installed on the system. On a desktop this is not an issue, but on a server, it costs an additional license that will only be used by SSIS.
3) Mechanically speaking, the best way to use the Excel destination is to create a spreadsheet with all the named ranges you need and then copy it off to a template that you can copy as the correctly named spreadsheet file prior to export.
Personally, I don't want to install MS Office on my database servers just to use data tools that Microsoft should have provided the functionality for in the first place. I don't even want to go down the 32/64 bit path as it gets me mad enough to want to go out to Redmond with my butt kicking boots on.
Thanks for the article, it doesn't get me what I really want, but it does get me a way to what I need to do.
March 24, 2015 at 4:25 pm
In my case, I was trying to use the EPPlus library in a SSIS package and I was getting this same error. My solution was to put the file in this folder: c:/Program Files (x86)/Microsoft SQL Server/100/DTS/binn/
January 22, 2016 at 7:43 am
I totally spaced on when this article came out and the fact that I responded to it previously :hehe:
One thing I didn't mention then that is helpful to communicate is that, based on my experience, the one catch to writing Excel files programmatically is that you have to pay attention to how you setup the styles of your cells. If you follow this example, individual cells style properties are being set. While this does work just fine, Excel does have a limit on how many unique styles can be in a workbook (64,000 for Excel 2010) and in the code example, each one of those cells would have their own style. By setting the individual cell styles, each cell style is considered unique and saved in the file separately for each cell, even if you set the properties exactly the same. If there is a worksheet with 7,000 rows with 8 columns, this will cause the worksheet to have 56,000 styles, causing the Excel file to be VERY large.
Instead, create style objects within the worksheet apply it to those cells. Those cells will used that shared style and only one style is saved to the file, saving quite a bit of space. I don't know if EPPlus supports this (would expect that it should), but ExcelWriter does. It does require additional coding and being organized on how you reference and apply styles, but it saves a lot of headache down the road. Your files will be much smaller in size and won't run into errors when producing a large data output.
Also, it is important to note that if you use worksheet styles and apply them to the cells, but then you change a cell style property after that, that cell style will become unique.
Excel 2010 Specifications and Limits Source: https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply