October 28, 2008 at 9:38 am
I am having trouble with Excel exports. I want to be able to run the same export more than once without intervention and I want each run to clear the Excel worksheet from the previous run(s). I used the Import/Export Wizard and it set everything up for me but when I test the package, I get the following error:
Error: 0xC002F210 at Clear Worksheet Task, Execute SQL Task: Executing the query "DELETE FROM `RepList`
" failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Clear Worksheet Task
I've tried adjusting the ResultSet property settings but no luck. Anyone know how to fix this to make it work for me?
If I remove the Delete Worksheet step, The process just keeps appending new records to my original worksheet and I end up with duplicated data which is not what we are looking for! Please help - this should be a very easy task!
Thanks!
mj
~mj
October 28, 2008 at 11:38 am
Hi,
Would you check for the excel destination connection ? Some times this could also because of Excel version.
Rajesh
October 28, 2008 at 11:41 am
I have checked the settings - It is set to Microsoft Excel 97 - 2005 and I am working with Excel 2000. I am guessing this is correct??...
~mj
October 28, 2008 at 11:55 am
Would you please mention the tasks you have from the wizard. Ok, here you go. You should be having a task to delete records from the excel. check this task. I would also try to give you the steps when I get time 🙂
Raj
October 28, 2008 at 5:27 pm
Hey there, i ran across a similar issue. deleting data from excel is real pain, even when you delete it tends to remember where the last row of data was then starts appending after that point. I worked around the problem by creating a template excel file that it used to replace the previous run file so it was always starting out with a fresh copy each time. Works great. Just use a File system task.
October 29, 2008 at 10:50 am
AS for steps, the wizard created two simple steps. First a delete and second the import. Run the very first time works fine but it is after that I continue to receive the message above.
As for creating a template and replaceing each time, I'm not sure how to do this? I used file system task and was able to delete the file but then the package errors out because the file no longer exists and it doesn't seem smart enough to create a new file??
This is getting overly frustrating! :angry:
~mj
October 29, 2008 at 12:34 pm
In the past, I've typically done a "drop table", "create table" to handle things like this. The wizard is designed for the basics. If you need this to work more reliably or with more options, you need to work inside the package a bit.
October 29, 2008 at 1:32 pm
Okay, Maybe I am just having a true blonde moment here, but I don't understand. I do have a delete table task but that is what is not working.
If I do not include the delete table task, the process works but I end up getting data results appended to the same worksheet thus having duplicate data from the previous run.
What I currently have is an Execute SQL Task that tries to Delete records from the Excel worksheet (this is the step that fails on subsequent runs). Then that flows to a Data Flow task that simply exports data from an SQL view into Excel.
I'm really not sure what else I can do? Can anyone provide some example or a link to something that I can look at to help? Even a screen shot of what you are talking about would help clarify things for me a bit.
Thanks!
~mj
October 29, 2008 at 2:23 pm
I think in place of deleting you can move the file thru control flow and load to a fresh file...
Thanks [/font]
October 29, 2008 at 3:45 pm
I follow what is being said, but I still get errors.
I need some way to overwrite the data in my Excel worksheet each time the package runs...
Do I need to resort to using .NET?
~mj
October 30, 2008 at 5:43 am
A simple solution might be as follows:
In the Control Flow, create a file task to delete the Excel file before you load it with data.
The data flow will create the Excel file from scratch.
Isaac
October 30, 2008 at 9:18 am
When I used the File System task to delete my file in the Control Flow, the DataFlow errored telling me that the file was not found??!!
It's like I'm stuck in some sort of loop that is meant to drive me totally crazy! :w00t:
~mj
October 31, 2008 at 2:20 am
"In the past, I've typically done a "drop table", "create table" to handle things like this. The wizard is designed for the basics. If you need this to work more reliably or with more options, you need to work inside the package a bit."
MJ
I have tryed to use SSIS for thinks like you describe and it is a pain.
So my advice to you is to use Excel VBA ADO instead. If you and your
organization is a "heavy user" of Excel it is worth to learn how to do it
if you are not familiar to the topic. There are a lot of stuff on the net
and books like Excel2003 VBA from WROX (it also works in 2007 at least
what I have seen).
//Gosta
October 31, 2008 at 11:54 am
A previous poster provided the solution, but I think you missed an important piece of it. Here's a more detailed perspective:
1.) Create an empty spreadsheet as you would want it to exist immediately prior to the export. Save it somewhere on the SQL Server in a directory OTHER than the one the file to be exported to is located in. Give this spreadsheet the same filename as the one to be updated.
2.) Use a File System task to delete the previously updated spreadsheet file.
3.) Use a File System task to copy the "template" spreadsheet to the directory where you deleted the previously updated file from, in Step 2.
Your export should work fine at this point, because for every attempt to export, you start with a fresh worksheet that you know is in the proper state to receive data.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 3, 2008 at 7:40 pm
I have done the following and it works fine.
1. Create an Excel file that has only the output column names in it. Call it something like "OutFile_Template.xls".
2. Create a "File Task" that copies "OutFile_Tamplate.xls" to "OutFile.xls". Make sure you set the task to allow overwrite.
3. Export into "OutFile.xls".
This will give you an empty spreadsheet each time. There may be a better way to do this, but this will work and it is pretty easy. It seems strange that in the "Old DTS" doing an excel export was very easy. In the "New and Super Duper SSIS", doing the same thing is often quite difficult. I still use DTS as an ad hoc way of importing Excel data into a Work Table. It takes 2-3 minutes. If I tried to do this with SSIS it would take 4-8 hours or tedious programming and mapping of data types and coulmn names to do the same thing.
Note - This is the same as the post above. I did not read all the way to the end.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply