SSIS Destination table record
I am migrating data from a dataabase that stores information for about three years. This causes the primary key to occur more than once for the different years for each employee. What transformation can be used to take care of this please or could I use the where clause on source OLE DB? If yes, how is that done please. Thanks!
January 26th, 2011 6:18pm

In your Source adapter, you could write T-SQL Command and include a WHERE Clause to filer the data: SELECT <field(s)> FROM <table> WHERE Year = 2010. But why not just take ALL years and have YEAR as part of a composite key in your destination table?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 8:46pm

Is source database and destination database linked? if yes, then you can use this structure: select * from firstdatabasename.schemaname.tablename where firstdatabasename.schemaname.tablename.id not in ( select seconddatabasename.schemaname.tablename.id from seconddatabasename.schemaname.tablename ) if they aren't linked, you can use Lookup transform to find out rows which doesn't exists in destination. also you can configure error output to redirect Bad rows ( such as rows which cause primary violation constraint ) to another destination. http://www.rad.pasfu.com
January 27th, 2011 1:56am

I used the where clause but it's still violating primary key constraint. The thing is for each year, this field will show 12 times for the 12 months of the year. The Cost_Center_Code is the primary key and it's the column that is being violated. Below is the code I used: SELECT DISTINCT CC_Year, CC_Month, Cost_Centre_Code, Cost_Centre_Descr, CC_PCtr_Code, CC_ILEC_Flag, CC_Divt_Flag, CC_Mobility_Flag, CC_Company_Code FROM dbo.Mstr_CostCentre_Data where CC_Year > = 2010
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2011 11:32am

Violation of a Primary Key Constraint is NOT an SSIS issue. It would happen if you tried it from ANY application, even connected directly to the table via SQL Server Management Studio. You need to figure out the level of detail of your data, both source and destination. They are either obviously at different Key levels, or THERE IS NO PK on your source table.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
January 27th, 2011 11:37am

The primary key on source is same as destination's primary key. Do you think slowly changing dimension can handle this kind of situation? If yes, how best can I tweak it to work please. Thanks
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2011 12:34pm

Then if you are getting Primary Key Constraint errors, those records from the Source table ALREADY exist in the destination. Either TRUNCATE the destination table first, or set the Destination Adapter on the Data Flow in SSIS as follows: For Data Access Mode, switch it from "Table or view fast load" to "Table or view". Then add a Row Count to the Data Flow and connect the red arrow from the Destination Adapter to the Row count. Assign a variable in the Row Count and try running your package again. You will find that a bunch of records get rejected and sent down the red path.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
January 27th, 2011 12:53pm

Do you need a unique record for each year or can you update the destination? Consider moving the data to a staging table then using MERGE to update old stuff and insert new.
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2011 2:24pm

Hi Todd, yea, the duplicated rows were redirected to a different location so that worked. But what of a situation where you still want those redirected rows to to be in the same location, that is for everything to be in the same table or location? Is there some sort of sql code that can be used or like I asked earlier, can the slowly changing dimension transformation do this trick? if yes, do you know how it can be tweaked for it to work right? I know how to run the wizard and get everything going but don't really know what to do to make the repeated data to be recognized and still get loaded Thanks for helping me with this!
January 27th, 2011 2:30pm

In a year, each month has a unique record but each year holds 12 or less records for the same field of primary key but there might have been some updates on the other fields in the table. Even if I try staging area, the primary key constraint will still be violated because it's seeing all these years and months holding different records for the same primary key field. I asked earlier if Slowly Changing Dimension can do this? If yes how well can it be used?
Free Windows Admin Tool Kit Click here and download it now
January 27th, 2011 3:36pm

Hi could you please tell me how this can be achieved? I'm still very new in the world of SSIS. I've been told that I need to use the CC_Month and CC_Year fields present in the source table to determine the Effective_Dates and Expired_Dates of the destination table. Do you know how that is done? Thanks!
January 31st, 2011 10:59am

Thanks bb12321 for your input. I want to keep all the rows coming from the source table and would also want to keep that column still as the primary key. I thought Slowly Changing Dimension is supposed to take care of updated records even with repeated values for primary key. Correct me if I'm worng please. Thanks!
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2011 1:50pm

primary key can't have repeated value An SCD can be used in a few ways - have a look at http://en.wikipedia.org/wiki/Slowly_changing_dimension for details. One way to use this SCD is to only hold the most recent row of the CC data and older ones can be held in a different table. Have a read of the wikipedia article and see if any match your requirements
January 31st, 2011 2:11pm

If there are multiple rows with the same cost centre in them, which row do you want to use? The one from December or do you want to have them all in the same table? If you want them all in the same table, you will have to change the primary key if I understand your problem correctly
Free Windows Admin Tool Kit Click here and download it now
January 31st, 2011 2:17pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics