Hi, I am pretty new to SSIS. I have an excel sheet that has data that needs to be uploaded on a table on the database. There are two columns that will be uploaded and/or replaced. However, no new rows will be created. Do we use a lookup? or something else.

Thanks.

Well, a Column insert is not supported in SQL, so you can try loading the complete Excel data into the table in one single go. That will be quick and clean. And since we know already that no new rows will be added to the table, this becomes even safer and simpler to refresh the table data with latest excel. If you use lookups and filter out what data has changed and then try to load just that piece of data, you'll end up doing much more operations as compared to a simple Excel load!! So I woukd recommed doing a complete load of excel data into your destinaton table, as this looks simple, quick and straight forward.

You do NOT want to use a "destination" component. All of the Destination components insert data. You do want to use an OLE DB Command transform - although it's a little tricky to use, it is the component that will let you run an UPDATE T-SQL statement for each row in your Excel spreadsheet.

So, use an Excel Source to read the rows from the Excel spreadsheet, then use an OLE DB Command transform to issue an appropriate UPDATE command, mapping appropriate columns from the Excel sheet to the UPDATE command.

---

Do u mean data is inserted or updated?
Yes you can use the lookup task.

---

Hope this helps !! - Sudeep

Well, a Column insert is not supported in SQL, so you can try loading the complete Excel data into the table in one single go. That will be quick and clean. And since we know already that no new rows will be added to the table, this becomes even safer and simpler to refresh the table data with latest excel. If you use lookups and filter out what data has changed and then try to load just that piece of data, you'll end up doing much more operations as compared to a simple Excel load!! So I woukd recommed doing a complete load of excel data into your destinaton table, as this looks simple, quick and straight forward.

You do NOT want to use a "destination" component. All of the Destination components insert data. You do want to use an OLE DB Command transform - although it's a little tricky to use, it is the component that will let you run an UPDATE T-SQL statement for each row in your Excel spreadsheet.

So, use an Excel Source to read the rows from the Excel spreadsheet, then use an OLE DB Command transform to issue an appropriate UPDATE command, mapping appropriate columns from the Excel sheet to the UPDATE command.

---

Just for the sake of discussion, UPDATE will be done row-by-row, I wonder how much of an extra cost will an INSERT incur in terms of resources consumed. OLEDB COmmand component worked well in my testing though. ;)

I think that the general consensus is to test both an ole db update command and an insert command to staging table followed by a batch style update through an execute sql task and see which performs faster. It will of course depend on the data set, but in most cases the batch update should perform more efficiently.

---

Please mark answered posts. Thanks for your time.