January 25, 2008 at 11:40 am
hi,
I am trying to delete first 10 rows from an excel table by using SSIS. I am just using a simple sql command to delete first 10 rows from the excel file.
Would anyone know a sql query to just delete first 10rows from the table without knowing column names?
I was thinking about using "DELETE FROM 'table' where" and after where I don't what do put.
Please help.
Thanks.
Ravi.
------------
🙂
January 25, 2008 at 12:08 pm
Why are you using SQL to delete from an Excel spreadsheet? What are you trying to accomplish?
January 25, 2008 at 12:45 pm
Brandie Tarvin (1/25/2008)
Why are you using SQL to delete from an Excel spreadsheet? What are you trying to accomplish?
I have to import data from an excel file. First 10 rows of the excel file are just random description of client that I don't need. I tried to import all the data in one table, but it gives metadata mismatch error. It also tries to tuncate the data. I think what SSIS does is it looks at first couple of rows in excel and tries to determine data type, and it ends up deciding on the wrong ones.
The only way I was able to run the package is manually deleting first 10 rows of the table. But I have to automate it. Every month someone else will run the package once I create it. Let me know if you have any other ideas?
Take a look at pictures. It'll give you more idea what i'm talking about. One screen shot is the error and other one is actual excel file.
Thanks.
Ravi.
------------
🙂
January 25, 2008 at 12:56 pm
Have you looked at ignoring the data errors or redirecting them to another table during import? That's one of the options you can set up on your Excel Data source.
Also - during the bulk import process you can specify where to start (starting row).
Redirect row is a bit more work, but better in the long run, since it will help you catch any data errors that might creep in within the stuff you DO want to see.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 25, 2008 at 3:29 pm
Without knowing at least one column name, there's no good way to do a delete other than manually before the run of the package.
I recommend redirecting the rows out of the data source as previously suggested.
January 28, 2008 at 7:24 am
Thank you so much for everyone's suggestions.
Seems like Bulk Import would work. I tired it, but it says I don't have permission to do a bulk import. The DBA here can't figure out how to give me access (it kinda sucks).
I'm little new to SSIS, so i've never looked redirecting rows. I will go through a tutorial today on redirecting rows. Thanks for your guidance. I think after going through the tutorial, I should be okay.
Ravi.:)
------------
🙂
January 28, 2008 at 7:35 am
Ravi,
It might be the DBA doesn't want to give that kind of access. But just in case the DBA really can't figure it out, tell him/her to grant you access to the BulkAdmin server role. Then you should be able to use Bulk Insert (assuming there are no other DENY permissions preventing you from doing this).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply