September 22, 2010 at 4:05 am
Hi
I am very new to SSIS, having just got back from a training course and not really used it in the real world yet! Perhaps you guys can help by telling me if SSIS is the right tool for this job or should I just use something else!
I have an excel file which contains several columns which contain values I want to insert into a SQL statement. The SQL statement would be as follows
DELETE FROM Table
WHERE VersionID=ColumnA
AND DocID=ColumnB
AND ProviderID=ColumnC
I have several hundred rows of data in the excel file so I would really appreciate a way of automating this! From what I learned in my course, I guess I could use a package level variable for each of the 3 columns and probably put a dataflow task inside a foreach loop container but I am unsure as to what task I should use to "generate" the code, perhaps as a string to a flat file.
Any tips would be very gratefully received!
September 22, 2010 at 7:05 am
So you only want to create the delete statements from the SSIS package?
Why not do the delete within the SSIS package? You can do an Execute SQL Task after reading the Excel file with variables that you map to the values in the excel file.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2010 at 6:32 am
That is a great ideo of Jack, but I'm not sure how you can make it one big delete statement instead of a lot of different small delete statements (each deleting one row) with variable mappings.
My choice would be to read the Excel file and write the data to a staging table. Then I would perform a delete statement with an inner join on the staging table. This would certainly improve performance. Or, if I'm not making sense at all, please correct me 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 23, 2010 at 9:40 am
da-Zero,
Definitely a better idea which makes the process set-based, while my suggestion would work, but would process per row, meaning a delete statement for each row in the spreadsheet.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 23, 2010 at 9:46 am
Wow, you guys are brilliant but unfortunately this is going over my head slightly! So far I am debugging using a insert statement into another table but for some reason I am only returning a few records! I also went down the road of using a staging table. I would be interested in how you would do the delete statement with an inner join as I am already running into buffer errors!
September 23, 2010 at 9:51 am
delete a
from TableA a join TableB b
on a.ID = b.ID
deletes rows from table A which have matching records (by ID) in table B.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply