Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

delete rows from excel file in SSIS Expand / Collapse
Author
Message
Posted Tuesday, December 5, 2006 3:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 11, 2009 10:44 PM
Points: 13, Visits: 13

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

Post #328124
Posted Tuesday, December 5, 2006 5:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 11, 2009 10:44 PM
Points: 13, Visits: 13

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

 

Post #328158
Posted Friday, March 21, 2008 5:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:10 PM
Points: 2, Visits: 165
I have not been able to find much on this subject that I've been able to get working in a test environment either.
Post #473148
Posted Friday, March 21, 2008 6:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 1:10 PM
Points: 2, Visits: 165
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?
Post #473160
Posted Tuesday, May 27, 2008 5:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 27, 2008 5:24 PM
Points: 1, Visits: 1
Thanks Matt Dawson, that worked perfectly for me!
Post #507334
Posted Thursday, May 29, 2008 12:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, Visits: 400
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
Post #508202
Posted Thursday, June 19, 2008 5:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 24, 2013 1:21 AM
Points: 5, Visits: 84
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.
Post #520285
Posted Monday, September 8, 2008 5:20 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 24, 2009 4:23 PM
Points: 1, Visits: 6
@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?
Post #565833
Posted Thursday, February 19, 2009 10:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 24, 2010 1:17 PM
Points: 3, Visits: 20
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
Post #660584
Posted Tuesday, March 24, 2009 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 24, 2009 11:23 AM
Points: 1, Visits: 4
thx mad. this works great for me too.

Post #682633
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse