April 1, 2008 at 8:02 am
Hello,
I made a package in SSIS to copy some data from SQL server 2005 SP2 to Excel 2007. The package works fine, but generate errors. If I replace the OLE DB destination for Excel 2007 with a Excel destination for Excel 2003 then they errors don't appear. The problem is that I have to use Excel 2007 because the data contains more than 65000 records. I thought maybe it was to much date, but if I limit the amount of data with top 100 it also generate errors.
The errors are:
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Execute phase is beginning.
Information: 0x40043008 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Post Execute phase is beginning.
Error: 0xC0047018 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: component "Source Declaratiegegevens uit NZDF op NED_NDFSQL01" (1) failed the post-execute phase and returned error code 0x80004002.
Error: 0xC0047018 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: component "Source Declaratiegegevens uit NZDF op NED_NDFSQL01" (1) failed the post-execute phase and returned error code 0x80004002.
Information: 0x40043009 at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Declaratiegegevens NZDF naar Excel, DTS.Pipeline: "component "Destination Excel 2007" (142)" wrote 353858 rows.
Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
I also tried to rase the maximum error count, but that hadn't any effect.
I hope someone gots a answer...:D
Thanks in advantage!
Michaël Dupuis
April 2, 2008 at 2:03 pm
I hope you are aware that Excel 2007 is based on XML ... so far this is the only documentation I was able to find that appears to have a bearing on what you are attempting to do
How to connect Excel to SQL Server 2005 Analysis Services:
http://support.microsoft.com/kb/940167
Hope this helps
April 2, 2008 at 2:36 pm
Other possible aids
Applies to:
SQL Server 2005 Reporting Services
Excel 2007
Word 2007
http://msdn2.microsoft.com/en-us/library/aa964136.aspx
Found in a blog:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2524069&SiteID=1
SSIS sql server 2005 uses Microsoft Office 12.0 Access Database Engine OLE DB Provider for excel 2007 SSIS sql server 2005 uses Microsoft Office 12.0 Access Database Engine OLE DB Provider for excel 2007
Choose a Data Source (SQL Server Import and Export Wizard)
http://technet.microsoft.com/en-us/library/ms189667.aspx
SQL Server 2005 Books Online (September 2007)
OLE DB Connection Manager:
http://technet.microsoft.com/en-us/library/ms141013.aspx
2007 Office System Driver: Data Connectivity Components:
Hope these help.
April 3, 2008 at 1:13 am
Hi bitbucket,
Thanks for the reply!
I already read the articles you reply to, but I read them again now but the don't give me a solution for the errors. In my opinion there isn't much that I can do wrong and the package works fine, but the errors bother me. I have searched the internet for the errors but I have searched for 3 days know and I beginning to wonder of any else have this problem....
If you have more suggestions, i would like to hear them 😀
Thanks,
Michaël
April 4, 2008 at 6:13 am
This might be a moot point but where are you getting this error list from - the progress window? If so, Ive found that the Output window gives more discription on the errors. Just open that and find where it says "Error" on the left - usually several lines.
Hope this helps!
April 4, 2008 at 6:45 am
Hey Aaron,
The errorlist comes from the output window, but the problem is resolved. 😀
I also placed this question on technet and there someone gave me the solution. I used a datasource reader, but I had to use a OLEDB. Now that I use OLEDB the exports works fine.
Thanks for the reply and have a nice weekend.
Best regards,
Michaël
May 8, 2008 at 9:36 am
BUMP ....same issue with me.... anyone else seen this?
May 9, 2008 at 2:34 am
Hey Studdy05,
Do you use a OLEDB or a datasource reader? If you use a datasource reader, then try to use a OLEDB because that resolved my problem.
If you use a OLEDB then I can try to help you, but if you use a datasource reader then I can't help you because I couldn't solve that problem.
Best regards,
Michaël
July 29, 2008 at 10:57 am
Please visut the below article for the issue resolution
http://www.sql-server-performance.com/articles/biz/How_to_Export_Data_to_Excel_2007_p1.aspx
Also see the following website for excellent articles. http://sqlserverarticle.blogspot.com
July 29, 2008 at 1:42 pm
It turned out to be an XML setting for us. We configured this:
Changed the following entry in reportserver.config file for csv:
July 29, 2008 at 1:43 pm
Changed the following entry in reportserver.config file for csv:
---
---
---
---
--
--
--
May 18, 2011 at 12:11 pm
I don't suppose someone could actually post what was changed in the config files for these past 2 posts? 🙂
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply