SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


delete rows from excel file in SSIS


delete rows from excel file in SSIS

Author
Message
Sunil Bansal
Sunil Bansal
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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


Sunil Bansal
Sunil Bansal
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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


mdawson
mdawson
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 180
I have not been able to find much on this subject that I've been able to get working in a test environment either.
mdawson
mdawson
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 180
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?
chris-760952
chris-760952
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 1
Thanks Matt Dawson, that worked perfectly for me!
pduplessis-723389
pduplessis-723389
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3063 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
saubyj
saubyj
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 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.
enielsen
enielsen
Grasshopper
Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)Grasshopper (13 reputation)

Group: General Forum Members
Points: 13 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?
eric.donofrio
eric.donofrio
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
b.pichler
b.pichler
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 4
thx mad. this works great for me too.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search