Rojas, Joe
joe.rojas at symmetrynb.com
Thu Feb 19 14:00:51 CST 2009
Just in case anyone is looking for a similar solution. I found this excellent link that shows step by step. http://vsteamsystemcentral.com/cs21/blogs/applied_business_intelligence/ archive/2007/05/21/ssis-design-pattern-incremental-loads.aspx Joe -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Thursday, February 19, 2009 10:24 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server 2005 - How to import data that couldeither update a row or insert Hi Joe I don't use the SSIS package so I can't tell. But do a test in Access - it is very simple and easy to do - and then redo the SQL modified for your actual tables in SSIS. /gustav >>> joe.rojas at symmetrynb.com 19-02-2009 16:18 >>> Thanks Gustav, I should have mentioned that I'm still learning how to make SSIS packages. How can I achieve the steps below in an SSIS package? Joe -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Thursday, February 19, 2009 9:51 AM To: dba-sqlserver at databaseadvisors.com Subject: Re: [dba-SQLServer] SQL Server 2005 - How to import data that couldeither update a row or insert a row. Hi Joe It can be done, if the tables have a unique key. This old tip from Smart Access is one of my favourites. It's for Access but nothing Access specifics are used for the code: <quote> Update and Append Records with One Query By Alan Biggs Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld. Follow these steps: 1. Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld. 2. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld. 3. Select all the fields from tblOld and drag them onto the QBE grid. 4. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld. 5. Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.) 6. Run the query and you'll see the changes to tblNew are now in tblOld. This will only add records to tblOld that have been added to tblNew. Records in tblOld that aren't present in tblNew will still remain in tblOld. </quote> /gustav >>> joe.rojas at symmetrynb.com 19-02-2009 15:33 >>> I'm using SSIS to create a Package to import data into SQL Server 2005. I need to pull data from our ERP system via ODBC. I'm doing this because we are pulling labor "punches" from our ERP system into SQL Server and the processing them to be exported to our Time and Attendance system. Our ERP system stores the "in" punch and the "out" punch for a day in one row. I will be pulling the punches several times a day which will result in some rows have both "in" and "out" punches and some rows having just an "in" punch. If a row just has an "in" punch, I still need to pull it. Because of this, I will need to "re-pull" this row each time to check for an "out" punch. So...my question. The dataset I pull will have rows that match to an existing row in the SQL Server table AND new rows that do not exist in the SQL Server table. Having one dataset pulled from the ODBC source, can I parse row by row and, based on if the row exists, I will need to execute an UPDATE statement or an INSERT statement? If so, how would I do this in a data flow task? Joe _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com