July 13, 2007 at 11:30 am
Ok, what am I missing here? Is there a SSIS DataFlow component that will update a table in the database instead of trying to always Insert (besides using a Script Component)?
July 16, 2007 at 6:51 am
The answer is yes, there are in fact a number of components that can do an update. You probably want the OLE DB Command component for a simple update. Set up your SQLCommand to something like UPDATE MyTable SET Field1 = ? WHERE Field2 = ?. Then set up the paraeter mapping, etc.
You can also use an OLE DB Destination component. If you add the component and then open the advanced editor for it, you can change the access mode from OpenRowset to SQL Command, then set your SQLCommand property to something like UPDATE MyTable SET Field1 = ? WHERE Field2 = ?. Then set up the paraeter mapping, etc. It works a lot like the OLE DB Command object but does not have a data flow out.
Another cheater option is to use the Slowly Changing Dimension Wizard which will allow you to use a GUI to set up an insert new records / update existing records pretty easily - when you get through the wizard, it will have done the work of figuring out which records are new and which are already there and do the appropriate insert or update. If you don't want the insert portion, you can just delete the insert component.
I would recommend starting with looking at the Slowly Changing Dimension wizard. I have had good luck using it for a lot of updating and inserting with little effort. If you canwrite a query from your source data that looks just like what you want in the destination, this works pretty well. Just watch out - it is sensitive about data types, case, and trailing spaces so it may see differences in columns that don't appear to be different.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply