How to Modify an excel file using SSIS?

Question

How to Modify an excel file using SSIS?

//Ravi

SSCrazy

Points: 2612
More actions
January 25, 2008 at 9:01 am

#154090

Hi,
Is there a way in SSIS to access an excel file and delete certain number of rows in it?
Thanks.
Ravi.
------------
🙂

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

If your worksheet is properly set up as a table, you can use an ExecuteSQL task with a regular "DELETE FROM X WHERE C='A'" type T-SQL statement.

Jay1234 SSCommitted Points: 1624 More actions · Answer 2

I tried to delete records in excel using execute sql task and I recieved following error -

[Execute SQL Task] Error: Executing the query " delete from products where ProductID = 'ABC'" 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.

Please help.

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 3

I would go with Michael's method too. But you said you have tried it. Well, what and why are you deleting from an excel file? Also, where is the excel file? Source or destination? Some more elaboration will let us help you more.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Jay1234 SSCommitted Points: 1624 More actions · Answer 4

I'm trying to delete few records in excel file using execute SQL task with excel as connection manager. I'm able to update excel file but not delete records.

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 5

That's kindda tricky right there! But I believe if you know some .NET you will get what you want. My friends are saying . Net would be able to do something like that.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Dipak-387207 Mr or Mrs. 500 Points: 566 More actions · Answer 6

Here I have question, I am using SQL server as remotely, and I am trying to create Excel file using SSIS, so on local machine its works fine, but when i am trying to put that SSIS pakcage on server side somehow it is not working, and i don't know Excel installed or not on SQL server, so to access excel file using SQL server do we need to installed MS-office on SQL server?

Jay1234 SSCommitted Points: 1624 More actions · Answer 7

Did you copy the excel file you created to remote server?

what is the error message you got?

SQLdevotee SSCarpal Tunnel Points: 4820 More actions · Answer 8

Dipak (3/6/2008)
Here I have question, I am using SQL server as remotely, and I am trying to create Excel file using SSIS, so on local machine its works fine, but when i am trying to put that SSIS pakcage on server side somehow it is not working, and i don't know Excel installed or not on SQL server, so to access excel file using SQL server do we need to installed MS-office on SQL server?

I would say "YES" , you have to install it if you want to have the result in Excel. But if that does not matter, just use a Flat File Destination. I always use this to figure out if there is anything wrong with it first. Then go from there.

[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]

Dipak-387207 Mr or Mrs. 500 Points: 566 More actions · Answer 9

ya true BCP we can use it, but again there is one problem using BCP, In my table there is one column as varchar and in that column values as '00012345' and '0001455' means starts with some zeros, now if i am going to use BCP, its works fine but when i see the excel file then values as 12345 and 1455, zeros are complete gone, so using BCP you can not see 000 values as begining, i don't know its bug or what.

//Ravi SSCrazy Points: 2612 More actions · Answer 10

Excel usually formats all the leading zeros by default. You can't format cells using SSIS.

As mentioned earlier, I think it will be better to use flat files if you want to keep the zeros.

------------
🙂

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 11

Leading zeroes in a number do not change the value of the number; it is simply a formatting issue. In your Excel worksheet, just assign a custom format (e.g. 00000000 for a left zero filled 8 position field) to the cell/column.

RE the question of deleting Excel rows, I saw a MS Knowledge base entry that said you simply could not do it.

(I'll try to locate it again.)

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 12

Here is the link:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q316934

that contains the statement re delete:

Delete Records

Although the Jet OLE DB Provider allows you to insert and update records in an Excel workbook, it does not allow DELETE operations. If you try to perform a DELETE operation on one or more records, you receive the following error message:

Deleting data in a linked table is not supported by this ISAM.

This limitation is inherent in the treatment of Excel workbooks as databases.

Jay1234 SSCommitted Points: 1624 More actions · Answer 13

Jay1234

SSCommitted

Points: 1624

March 11, 2008 at 7:35 am

#787959

Thanks Jim. Thats what I thought.

Jim Russell-390299 SSCrazy Eights Points: 9333 More actions · Answer 14

And as Chris Morris so wisely just pointed out in this thread:

http://www.sqlservercentral.com/Forums/Topic466056-149-1.aspx#bm467382

"You can't delete a row, but since you can blank out cells, for most purposes is doesn't matter. "