December 14, 2012 at 3:36 am
hi
I have two different servers, but both have same set of DB and Table Schema.
Daily basis Source server Table have updated and new records. i need move only except cases from src to dest table.
here, table has 3 fields defined belod. In slowly Slowly Changing Dimension in SSIS i cloud not set PK_ID as buisness. pk_id is not display in key selection window and i tried both UserId_FK and UserGroupId_FK as buisnesskey but there no field in Slowly Changing Dimension columns window.
Column_name Type Key
PK_ID int Identity(1,1) and Primary key
UserId_FK varchar (Not an unique value)
UserGroupId_FK int (Not an unique value)
Here how i can set as a PK_ID as Buisnesskey and use Changing Attribute
Regards
T Kumar
December 14, 2012 at 5:13 am
What exactly are you trying to do?
Also the SCD will not recognise Primary keys on the desitination table as Business Keys as they are not guaranteed to be identical, where as Business Keys are meant to be unique to that row of data.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 14, 2012 at 5:19 am
Jason-299789 (12/14/2012)
What exactly are you trying to do?Also the SCD will not recognise Primary keys on the desitination table as Business Keys as they are not guaranteed to be identical, where as Business Keys are meant to be unique to that row of data.
Are you saying a primary key is not guaranteed to be unique?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2012 at 6:55 am
Koen Verbeeck (12/14/2012)
Jason-299789 (12/14/2012)
What exactly are you trying to do?Also the SCD will not recognise Primary keys on the desitination table as Business Keys as they are not guaranteed to be identical, where as Business Keys are meant to be unique to that row of data.
Are you saying a primary key is not guaranteed to be unique?
No, but with this statement in the OP I have two different servers, but both have same set of DB and Table Schema.
This then leads to the assumption has PK_ID column on both databases is an Identity, and as such you cannot place a Business Key on an IDENTITY on the destination table column as by its nature it is not unique to the row of data it represents, as you have little control over how this is inserted.
If you had a Surrogate Key with an Identity and the sources PK such as
Create Table dest(
Surrogate_Key Int Identity(1,1) Primary Key
,Pk_Id Int NOT NULL
,UserId Int NOT NULL
)
Then this would be ok.
The SCD task for this is the wrong way to go, as the business Key in the destination table cannot be an Identity column, and it makes no sense to include it as such, especially if you are doing an SCD Type 2. which then invalidates the Pk_ID as being unique on that table.
The best way to do this is with a Lookup to the Destination table and then route missing keys down to an insert and matched keys into an OLEDB Update
But again you have to turn off the Identity to do the Insert so that the destination table doesnt apply a different key to the Source.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply