I currently have an Access Query that I would like to export to to Excel 2007 on one tab, then using that data I would like to copy it over to a prebuilt pivot table, that would update automatically upon opening the excel sheet. It was exporting using the qry view of the Access table to the excel document but it was not actually updating the data. I changed it to a SQL statement of the qry for the source to go to an excel destination with a data conversion in between, but I recieve the following errors, no matter how much I cange the source or destination: Error: 0xC0202009 at Data Flow Task 1, Destination - zqryFinal [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. Error: 0xC0209029 at Data Flow Task 1, Destination - zqryFinal [31]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (42)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (42)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. Error: 0xC0047022 at Data Flow Task 1, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - zqryFinal" (31) failed with error code 0xC0209029 while processing input "Destination Input" (42). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Information: 0x40043008 at Data Flow Task 1, SSIS.Pipeline: Post Execute phase is beginning. Information: 0x4004300B at Data Flow Task 1, SSIS.Pipeline: "component "Destination - zqryFinal" (31)" wrote 0 rows. Information: 0x40043009 at Data Flow Task 1, SSIS.Pipeline: Cleanup phase is beginning. Task failed: Data Flow Task 1 Task failed: Execute Package Task Warning: 0x80019002 at Package: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (2); 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 "marketing_bonus_dealer_pull.dtsx" finished: Failure. Any Suggestions?

This is a conversion error, thus you need to troubleshoot your data [quality]. In addition to doing the data conversion you may need some reformatting, but you do not post examples of the data that fails the package.Arthur My Blog

What do you mean by troubleshoot the data? I am very new to SSIS and I am not sure what types of reformatting should be done. If I could break down my problem a little I need to know: What Source Provider should be used to connect the Access Source Query to the Excel 2007 Table. Will the Access Querry Run if the it is set to table view of the Qry, instead of SQL Command. Will the pivot table update automatically if the source data is uploaded. I currently have the package set up as follows:

You already get data, but one of the target columns in Excel does not accept it. This is why I asked for the examples of data. You need some experimentation to figure out what needs to be done. The 1st step toward fixing would be trying to remove the offending column from the package, may be you need to test with a different Excel file set to work w/o that column in the Pivot. Also you can check what data is coming in by placing a Data Viewer on the link leading to Excel - just double-click the connector and poke in the menus until you set a grid that will cause the package execution to suspend and present you with the data currently about to hit Excel. See if it looks good, you may also want to to copy a row and insert into Excel as a test outside the package to see if that works.Arthur My Blog

I played around with the trasnformations and it runs now, But I do not get the data from the Access Query. The data that is in the excel sheet is not updated, and does not match the Qry

What I understood the data gets extracted from the source (you can see it in the Data Viewer), but it does not get inserted, correct? And no errors?Arthur My Blog

I really don't know I have never used a data viewer, I have tried to set up the data viewer but I don't really know how I looked up articles and tried to configure it but it doesn't seem to do anything at all.

Shown how to here really well: http://sqlblog.com/blogs/andy_leonard/archive/2010/03/08/ssis-snack-grid-data-viewer.aspxArthur My Blog

I followed the steps as the link you gave me had said, and it did not pause the the task and show any data I hit execute SQL task and it just ran what is below. I also posted the dataviewer for the dataconversion to destination. Does this mean that the data is not actually flowing through the task? I greatly appriciate your help on this!

it is exactly that! I suspected that the data is not being picked up by the OLEDB Source. Now the task #1 is to get it. Please post here how did you configure the data retrieval.Arthur My Blog

The Sql Command I pulled from the Access SELECT qryDistinctAuctions.co_nm, qryDistinctAuctions.[Month], qryDistinctAuctions.[Year], qryDealerCarsSoldByAuction.Cars_Sold AS Purchased, qryUniqueCountOfDealersByAuction.Expr1 AS [Unique Purchaser Count] FROM qryDealerCarsSoldByAuction RIGHT JOIN (qryUniqueCountOfDealersByAuction RIGHT JOIN qryDistinctAuctions ON (qryUniqueCountOfDealersByAuction.co_nm = qryDistinctAuctions.co_nm) AND (qryUniqueCountOfDealersByAuction.[month] = qryDistinctAuctions.[Month]) AND (qryUniqueCountOfDealersByAuction.[year] = qryDistinctAuctions.[Year])) ON (qryDealerCarsSoldByAuction.co_nm = qryDistinctAuctions.co_nm) AND (qryDealerCarsSoldByAuction.[Month] = qryDistinctAuctions.[Month]) AND (qryDealerCarsSoldByAuction.[Year] = qryDistinctAuctions.[Year]) WHERE (((qryDistinctAuctions.[Year]) = 2012)) ORDER BY qryDistinctAuctions.co_nm, qryDistinctAuctions.[Year], qryDistinctAuctions.[Month]; I reran it with the sql statement again and I do recieve data both before and after the data conversions, and the data is up to date. HOWEVER Now it is failing at the excel destination as it had done before. It does not give any description really other than this: [Excel Destination [46]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (57)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (57)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination" (46) failed with error code 0xC0209029 while processing input "Excel Destination Input" (57). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. Above is a picture of the data conversion editor perhaps I hae the wrong data type?

Sorry I am continually messing with this package. I created another option that have a data flow to export from access to a .csv, then there is another dataflow to export from csv to excel. It is the csv to excel where the data viewer stops working, it works from teh Access Query to CSV, but nothing comes up on the data veiwer when I try to take the data From CSV to Excel. I haven't deleted the other process I was just trying to work around the excel dilemna. I have two options now but both are encountering package fails.

So now after some changes the data is showing up in the DataViewer that is placed after the data source, right? If the data shows up in the viewer, but not in the file then you did not either connect the flow, or did not map it. Let's concentrate on getting the data 1st and making sure it hits say the CSV file.Arthur My Blog

I can now get the data from the Access database no problem. It shows up in the data viewer and in the document, BUT when I use a CSV file the data does not all get put into the columns correctly, I assume it has to do with the column deliminator but there are not others that work with this data, if possible I would like to set it from Access to Excel directly, The data shows up in the data viewer for that one also but it still shows the same error at the excel destination, [Excel Destination [49]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. [Excel Destination [49]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Excel Destination Input" (60)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Excel Destination Input" (60)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Excel Destination" (49) failed with error code 0xC0209029 while processing input "Excel Destination Input" (60). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. What data types should be used for this process? I have 2 sets of numbers, the month number and year?

There could me more messages above. And perhaps you get warnings that are going to be even more useful. Review what they are. On the surface it is dependent that you did not get the data dumped into the CSV yet as per your desire, so you have to fine - tune this part 1st of all. Then may be try just dumping to a plain new Excel file, see how that works, do not be too worried about the data types, they are abstractions because any data in a file is just plain text. I advocate also to enable the import on the Excel early to avoid other issues, see this: http://sqlblog.de/blog/2009/04/ssis-excel-import-column-data-types/Arthur My Blog

I was able to dump the data into an new excel file when it is empty. I tried to then do a file system task to delete the worksheet and copy a template over, but that does not seem to work. Do you know if there is a SQL statement to Drop/delete just one table in the current excel document then perhaps I could use another to re-create the table?

I stand corrected. Here is a similar post. please review the post by Eileen Zhao which give a pretty good insight. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cc21cc57-05e0-4784-a42f-8e6625c5583b

Thanks, After I am able to load the correct data into excel is there a way to update the pivot table with the new information. Right now the pivot table shows up iwith all zeros where the sums should be.

I used the links and advice to creaste a package that exports to an excel sheet, I then copied the data to a new sheet called zqryfinal and I use zqryfinal to create a pivot table in excel, however all of the data is displaying as zeros, I am not sure if this is a formatting issue or if the data from SSIS cannot be used in an excel pivot? Here is how I set up the pivot:

I eneded up creating a template page the qry export and an addition report excel doc in SSIS where I pasted the excel formulas for the pivot table. Thanks