SCD TRANSFORMATION HELP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SCD TRANSFORMATION HELP

Author

Topic

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-15 : 22:29:57

Hi everyone,

Here is the situation and I need advice from you guys. Every month we receive a file from our client to update any information if they change. Here is the example.

Client_ID, Address,Phone

121,223 Main Street, 451-869

145,458 Jonson Ave, 284-897

121,224 MILLER AVE, 623-3125

Here is the information that I am getting from client File

Client_ID,ADDRESS,PHONE

121,223 MAIN STREET,123-123

145,458 JONSON AVE,284-897

121,224 MILLER AVE, 623-3125

Here Client_ID “121’ he changed his phone number and Client_id # ‘121’ has two houses different address and different phone number, I am using SCD Transformation in SSIS to accomplish this , Here is result that I am getting

CLIENT_ID, ADDRESS,PHONE

121,224 MILLER AVE, 623-3125

145,458 JONSON AVE,284-897

121,224 MILLER AVE,623-3125

Please guide me how I can solve this problem, I really appreciate your help.

Here is the sample tables for test if some one want to test,

Create table Source_File
(
ID VARCHAR(10),
ADDR VARCHAR(30),
PH VARCHAR(15)
)
GO

INSERT INTO Source_File
VALUES ('121','223 MAIN STREET','123-123')
INSERT INTO Source_File
VALUES ('145','458 JONSON AVE','284-897')
INSERT INTO Source_File
VALUES ('121','224 MILLER AVE','623-3125')
GO

Create table Target_File
(
ID VARCHAR(10),
ADDR VARCHAR(30),
PH VARCHAR(15)
)
GO

INSERT INTO Target_File
VALUES ('121','223 MAIN STREET','458-698')
INSERT INTO Target_File
VALUES ('145','458 JONSON AVE','284-897')
INSERT INTO Target_File
VALUE ('121','224 MILLER AVE','623-3125')
GO

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-18 : 19:19:00

is this a homework or real life situation because I smell a bad design here. do you have a client table? also do you have an address table? are the phone numbers attached to client or address? what phone types are they ? with what you have there is no way of differentiating the rows. if you use client_id then you will most certainly update the wrong row. consider redesigning your approach.

If you don't have the passion to help people, you have no passion

Sonu619
Posting Yak Master

202 Posts

Posted - 2011-04-18 : 20:02:00

What do you think is a home work or real life situation? and this is sample data because i can't paste 70k rows, so this is just a sample data, i don't think this is bad design, its depend on business, Let me ask you one question because i didn't use a lot SCD Transformation, I can use two Business Key in one SCD Transformation? Here is the answers of the questions that you asked
a) Yes i have client table (Source file)
b) All information in one source file
c) phone types? if you talking about data type , varchar(15)

Please feel free to let me know if you have better approach to solve this problem. Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources