June 27, 2009 at 8:50 am
I'm a C# developer and I've been tasked with doing a Proof of Concept using SSIS to take data from our one product which uses Informix as a backend DB to one of 3 possible go forward products we have, each of these uses a SQL Server 2005 database, but as you know table structure is different between all three target products.
For this simple POC I'm taking a person table from the informix database and moving only to the one target product currently.
The Person table in the source product is something like this
CREATE TABLE person (
personkey SERIAL NOT NULL,
lname CHAR(25),
suffix CHAR(3),
fname CHAR(25),
mi CHAR(1),
street1 CHAR(30),
street2 CHAR(30),
street3 CHAR(30),
city CHAR(20),
state CHAR(2),
zip CHAR(6),
zip_plus4 CHAR(4),
marstat CHAR(1),
sex CHAR(1),
birthdate DATE,
ssno CHAR(11),
harea CHAR(3),
hphone CHAR(8),
warea CHAR(3),
wphone CHAR(8),
wext CHAR(4),
race CHAR(2),
deceased DATE,
patid CHAR(10),
pattype CHAR(2),
accttype CHAR(1),
acct_ctgy CHAR(2),
rateno CHAR(4),
resptype SMALLINT,
confidential CHAR(1),
email VARCHAR(50)
)
The target system is multiple tables.
Address is it's own table
Phone Numbers is it's own table
Race, Gender, Sex have lookup tables and use GUID's in the Person table
But there also is a patient table
The patient table has fields like
PersonUid
externalID - this would equal the personkey from our target system
MedicalRecordNumber - this would equal our patid from the target system
So I've written a Data flow task that does Address and Phone numbers
I've done another Dataflow task that does the person
But now how do I take the PersonUID from this target Person table and also insert it into the Patient table with the 2 columns from this source person table without using another Data Flow task that would require a lookup.
There's over 160,000 rows to be inserted into the person table and don't want to do an additional lookup based on lastname, firstname, and ssn.
Is there someway to insert into the target Person table and use the generated PersonGuid as input into the Patient table for these other fields?
Joe
June 27, 2009 at 9:09 am
That's quite a long post and I did not read it in detail. But I think I get the gist.
The way I would do this is to maintain your ExternalID field in the PERSON table.
Then perform INSERTS to your PATIENT table leaving the PersonID field blank. At the end of the INSERTs, run a single query to retrieve all of the missing IDs from the PERSON table, matching on your ExternalID field.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 27, 2009 at 10:28 am
The only thing in common between this person and patient table is the PersonUid
The externalID and medicalRecordnumber are in the patient table
The name, ssn, addressGuid, Genderguid, MaritialStausGuid are all in the Peron table
So I'm pulling the data from the source system as one SQL. Majority of it goes into the Person table, except for these 2 columns which will go into the Patient table along with this newly created PersonGuid.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply