April 18, 2008 at 9:51 am
Hello Everyone,
We have two softwares, one uses Access 2000 and has a table named Patient. The other uses MSDE and has a table name Patient.
I have already created a SSIS package that will transfer all patient records from the Access 2000 database, performs a transform or two (they use different datatypes for Gender), and places the data in a temp table in the MSDE database. I then run an Execute SQL Task that first goes through and updates all Patients in the MSDE Patient table to make sure they have up-to-date addresses, names (think marriage), etc by matching them up with the temptable. I then another Execute SQL Task that loads new patients in the Patient table from the temp table. For those of you wondering, the primary key from the Access table is a foreign key in the MSDE Patient table thanks to a wild stroke of luck (the MSDE patient table has two extra columns from the manufacturer for comments, so we all agreed to use the second one to store the primary key from the Access table and the users could have the first comments field).
My question: is there a better way to update patients or add new patients to an existing table in the Data Flow? Or is executing SQL statements the way to go?
JamesNT
April 18, 2008 at 1:27 pm
James Summerlin (4/18/2008)
Hello Everyone,We have two softwares, one uses Access 2000 and has a table named Patient. The other uses MSDE and has a table name Patient.
I have already created a SSIS package that will transfer all patient records from the Access 2000 database, performs a transform or two (they use different datatypes for Gender), and places the data in a temp table in the MSDE database. I then run an Execute SQL Task that first goes through and updates all Patients in the MSDE Patient table to make sure they have up-to-date addresses, names (think marriage), etc by matching them up with the temptable. I then another Execute SQL Task that loads new patients in the Patient table from the temp table. For those of you wondering, the primary key from the Access table is a foreign key in the MSDE Patient table thanks to a wild stroke of luck (the MSDE patient table has two extra columns from the manufacturer for comments, so we all agreed to use the second one to store the primary key from the Access table and the users could have the first comments field).
My question: is there a better way to update patients or add new patients to an existing table in the Data Flow? Or is executing SQL statements the way to go?
JamesNT
James, you could change your package to use a lookup transform to check the target table for existince of the unique key, and use an OleDB Destination to insert the unmatched (new) records, and an OleDB Command to update the existing rows.
However, unless there is a specific problem (performance, ease of maintenance, etc.) that you need to solve, I would say that the solution you have in place is just fine. I do a lot of updates with the Execute SQL task, and it's fast, easy to document, and works very well for large-scale updates. It doesn't feel as "SSIS-ey" (???) to use the Execute SQL task, but it gets the job done.
hth,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
April 18, 2008 at 10:17 pm
SSIS-ey?
LOL
We are only talking about 10,000 rows of patients and yes, it does work. I was thinking there might be a more elegant way, but it appears according to your response it's not worthy the effort this time around. And that usually is the case with solutions that are more elegant - they are rarely worth the time and effort to maintain them.
I guess sometimes you just can't beat running a good ol' query against your database and just calling it a day.
Thank you for your input. The Execute SQL Tasks stay.
JamesNT
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply