February 13, 2011 at 10:38 pm
Hello Forum,
I have successfully copied out a select view 'MyView' to a excel 2007 file.
I want to set this up as a job, to run basis to overwrite the results on the spreadsheet MyView.
Copying to `MyView` (Error)
Messages
• Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
(SQL Server Import and Export Wizard)
• Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (39)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (39)" 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.
(SQL Server Import and Export Wizard)
• Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - MyView" (28) failed with error code 0xC0209029 while processing input "Destination Input" (39). 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.
(SQL Server Import and Export Wizard)
Is there not an option to overwrite, I do not wish to append.
Full doc of my steps are attached
I have attached my stepss in the document
February 15, 2011 at 3:57 am
hi Trout,
I am assuming you have created siss package to export to excel you are using the export wizard and you are then running this as a job. You might want to at the end of the wizard instead of doing it just save the package first. I think what is happening is that the wizard creates the worksheet tab first excel using "myView" as the worksheet name using the create table sql command. And and unless this step is removed in the package it will aways try to re -create the same worksheet again (and since it already exists since you are targeting the same spreadsheet) it fails.
As a test try renaming the worksheet first and then run yr job...and see if it works
February 15, 2011 at 9:07 am
See this: http://samuelhaddad.com/2009/03/31/overwriting-an-excel-file-destination-using-ssis/
or search for "ssis excel overwrite" for other solutions.
February 15, 2011 at 8:32 pm
Hi Rookie,
Yes I have renamed the worksheet name and it works.
However the worksheet name needs to remain the same as it is linked to other spreadsheets.
What I am asking SSIS to do is overwrite the data in the excel spreadsheet nominated as 'myview'
if I call it myview2 etc then it will work fine, it will create another worksheet, but then I have to open up the other spreadsheets and change the links.
Can SSIS overwrite a excel spreadsheet using the same name?
February 16, 2011 at 3:04 am
hi Trout
What I did as a quick and dirty fix was to save the original spreadsheet (deleting the myView worksheet) as spreadsheet.xls.stru i.e as a template and in my package in the initialisation phase just do a filecopy task renaming the stru back to the xls...get my drift
this way the myview always gets created. However i don't know if yr requirement is to append to the existing data...in this case my fix wouldn't work...you only get fresh data. What i did after the spreadsheet was created, to save it with a datetime time stamp i.e spreadsheet.xls.YYYYMMDD. as my last task. This way you have a history of the downloads...not perfect but did the job
February 16, 2011 at 3:22 am
hi Trout,
I replied without checking the excellent article referenced above
http://samuelhaddad.com/2009/03/31/overwriting-an-excel-file-destination-using-ssis/
looks as if this kiddie answers all the work rounds...as i say mine was done as a quick fix
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply