SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SSIS to export data from SQL 2005 to Excel 2007


Using SSIS to export data from SQL 2005 to Excel 2007

Author
Message
Duppie
Duppie
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 118
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...BigGrin

Thanks in advantage!

Michaël Dupuis
bitbucket-25253
bitbucket-25253
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30177 Visits: 25280
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

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
bitbucket-25253
bitbucket-25253
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30177 Visits: 25280
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:
http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

Hope these help.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Duppie
Duppie
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 118
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 BigGrin

Thanks,

Michaël
adrinkwine
adrinkwine
SSC-Enthusiastic
SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)SSC-Enthusiastic (195 reputation)

Group: General Forum Members
Points: 195 Visits: 98
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!
Duppie
Duppie
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 118
Hey Aaron,

The errorlist comes from the output window, but the problem is resolved. BigGrin

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
Studdy05
Studdy05
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 156
BUMP ....same issue with me.... anyone else seen this?
Duppie
Duppie
Old Hand
Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)Old Hand (343 reputation)

Group: General Forum Members
Points: 343 Visits: 118
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
Ashish Kumar Mehta
Ashish Kumar Mehta
SSC Veteran
SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)SSC Veteran (272 reputation)

Group: General Forum Members
Points: 272 Visits: 245
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
Studdy05
Studdy05
Right there with Babe
Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)Right there with Babe (741 reputation)

Group: General Forum Members
Points: 741 Visits: 156
It turned out to be an XML setting for us. We configured this:

Changed the following entry in reportserver.config file for csv:
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