December 5, 2006 at 3:12 pm
Hello,
Does any body has idea how to delete records (rows) from the excel file using SSIS.
I have an Excel file with 2 worksheets.
In actual I have to do lot with this excel file but now I am asking just how to delete the rows from the worksheets using SSIS.
Can I retain the first row (column headings) and delete the rest of rows, As I have to reload the same excel file with new data.
Please help.
Thanks in Advance
December 5, 2006 at 5:52 pm
Hello,
For now I have a blank copy of the file with column names.
I just copy (overwrite) the blank file to the dataFile, then reload the file.
Thanks,
Sunil
March 21, 2008 at 5:11 pm
I have not been able to find much on this subject that I've been able to get working in a test environment either.
March 21, 2008 at 6:23 pm
ok, just figured it out for what i needed, in my previous testing it kept appending - pain in my butt.
initially, all i had on my control flow was an export task, the data flow of that was just two datasources, sql exporting to excel (after testing i found it was appending).
i had to:
1. create a file system task in control flow, deleting the file.
2. create a sql task, excel connection type, i used my previous excel connection for this, then used a create table script as my sql statement
3. left my previous export object the same, then linked the flow of these in this order described.
hope this helps anyone else out there.
i've read of other examples out there that would delete rows, and leave white space in the excel files. not what i would have wanted for a solution either.
and to me, a simple task such as this should not have been so hard to find information on, or that hard to figure out - but it was for me. what gives msft?
May 27, 2008 at 5:25 pm
Thanks Matt Dawson, that worked perfectly for me!
May 29, 2008 at 12:52 am
Hey Matt,
Having delivered my very first end-to-end SSIS component, a lot of things I would have thought there would be tons of readups on simply do not exist.
I had issues with virtually each and every step of delivering the package, some originating between the keyboard and the chair due to a lack of understanding, but others more to do with no proper writeups existing anywhere.
It was indeed one of the steepest learning curves that took place, and I have no doubt that I am not even halfway up that mountain...
What I would reaaaaally give my left arm for is some real world examples of .NET for SSIS. Undoubtedly if I didnt have an in-house .NET developer, I would have drowned by now.
The only advice I can give you is to build a really impressive repertoire of blogging sites. For example, the fellahs at Conchango are quite impressive with their responses.
Good luck!
~PD
June 19, 2008 at 5:13 pm
I stumbled on an alternative solution when I couldn't get my scripts to run.
Create your excel file with all the sheets and the top row how you like it.
Make a copy of your file, this will just be the table structure.
In BIDS Create a File System task to delete the old file.
Create a second File System task to copy the table structure file and name DestinationConnection the same name as the deleted file.
If there's a chance someone will mess with the table structure file, make it hidden/write protected (you might need to the BIDS steps above before hiding it).
If you do end up changing the attributes of the table structure file, do a third file system task to change the attributes.
September 8, 2008 at 5:20 pm
@mdawson: Thank you very much for your post. Your solution worked for me. What I'm trying to figure out is why isn't your solution already set up in a package in a suite of samples that one gets when one installs SSIS? How else does Microsoft figure a person would use Excel as an output of a job, if you can't rerun it over & over without it appending to the Excel file? I mean, I suppose there is a possible use case out there where you would want a file to grow infinitely, but that would be way more obscure a case than one in which you want to replace the file with the latest data. I agree with those that have commented that there is a strange dearth of information about SSIS. In my opinion, there is no better way to learn than by example. Examples like webcasts where you watch someone do something you want to do, or prefab sample code that you can invoke and then modify to your own purposes. Why do they create a powerful tool and then leave us to more or less fend for ourselves?
February 19, 2009 at 10:38 am
You can configure a SQL Task against an Excel connection object...so, I'm trying something like "DELETE * FROM SHEET1$" (the "*" is needed b/c the connection manager uses Jet). I'd think that this would work, but I keep getting an error message:
[Execute SQL Task] Error: Executing the query "DELETE FROM Sheet1$" failed with the following error: "Syntax error in FROM clause.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I've tinkered with the query a bit, but still getting the same error msg
March 24, 2009 at 11:25 am
thx mad. this works great for me too.
September 11, 2009 at 2:40 pm
This was excellent and works great. When creating the excel table, would you possibly know how to name the excel sheet? Thanks
September 13, 2009 at 8:16 pm
maria.markoff (9/11/2009)
This was excellent and works great. When creating the excel table, would you possibly know how to name the excel sheet? Thanks
The "create table" SQL that you code for your Exec SQL statement will naturally have a table name. Since the output is an Excel worksheet, that becomes the sheet name.
November 10, 2009 at 2:14 pm
mdawson,
What is your "create table script" that you use to create the excel file and sheet?
February 14, 2010 at 12:33 am
This worked like a champ for me. Thanks.
February 14, 2010 at 12:34 am
This worked like a champ for me. Thanks.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply