locked
Generate primary key for destination table RRS feed

  • Question

  • I'm trying to get a data flow running but the execution fails because the target table doesn't receive any primary key from the source table. Is it possible to flag the target column as a primary key and let SSIS create and increment this key automatically? 

    Thanks!

    Monday, March 29, 2010 9:05 PM

Answers

  • You can populate your Links table as a first step in your package sequence. The table structure should be somthing like

    LinkID INT IDENTITY (1,1) PRIMARY KEY,

    LinkURL NVARCHAR(<give some size here>),

    LinkDescription NVARCHAR (<give some size here>),

    ... and so on.

    You'll need to use UNPIVOT transform to convert Columns (LinkURL1, LinkURL2, LinkDescription1) to rows for your Links table. Since LinkID is marked as IDENTITY, you won't have to supply a value to this column while populating the Links table. relational engine will automatically generate it for you.

    As next step in the package, you'll need to populate the Listings table, where LinkID will be a FOREIGN KEY, linked to LinkID column of Links table. You can use the LookUp transform to get the value of LinkID from Links table and populate it in Listings table. Alternately, you can write SQL statement / stored procedure using JOINs to get this value.

    Hope this helps.

     

    Cheers!!

    Muqadder.

    Tuesday, March 30, 2010 6:36 AM

All replies

  • You need to provide much more information.

    What is your "primary key" - and how come your source system "has it", but it's not being sent to your destination?  How do you think it should be populated?


    Todd McDermid's Blog
    Monday, March 29, 2010 9:24 PM
  • You need to provide much more information.

    What is your "primary key" - and how come your source system "has it", but it's not being sent to your destination?  How do you think it should be populated?


    Todd McDermid's Blog

    Well, my source table (postings) is unnormalized including linkURL1, LinkURL2, LinkDescription1 and LinkDescription2 columns . In my target tables I've got another table named postings but another table called links too. What i want to accomplish is that I can transform the source table (postings) to my new postings table but separate the link information to new links table, which includes a primary key.

    Does this help?

    Thanks again!

    Tuesday, March 30, 2010 12:33 AM
  • Still I am not sure what is the primary key and how should it be generated for the Links table? Going by the subject line of the thread "Generate Primary Key", have you tried using identity column as Primary Key inside the destination table?


    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, March 30, 2010 1:51 AM
  • I have to agree with Nitesh - I can't understand what it is you want to do - although I understand a little buit more.

    Can you provide some sample "input" and how you would like that transformed into your output?


    Todd McDermid's Blog
    Tuesday, March 30, 2010 2:43 AM
  • Assuming you are wanting to create a primary key in your table "postings" but also pass this key to your "links" table as you denormalise it so you can continue to link the two, there's a couple of options:

    1) Use a stored proc to handle each row. Use an identity column on the postings table. As part of the SP capture the identity generated by the insert in the postings table and use it in your links table

    2) Use a script to assign a id to each row in the postings table. Use this Id as you denormalise the links component.

    Cheers, James


    James Beresford @ http://www.bimonkey.com SSIS / MSBI Consultant in Sydney, Australia
    • Proposed as answer by BI Monkey Tuesday, March 30, 2010 3:56 AM
    Tuesday, March 30, 2010 3:56 AM
  • You can populate your Links table as a first step in your package sequence. The table structure should be somthing like

    LinkID INT IDENTITY (1,1) PRIMARY KEY,

    LinkURL NVARCHAR(<give some size here>),

    LinkDescription NVARCHAR (<give some size here>),

    ... and so on.

    You'll need to use UNPIVOT transform to convert Columns (LinkURL1, LinkURL2, LinkDescription1) to rows for your Links table. Since LinkID is marked as IDENTITY, you won't have to supply a value to this column while populating the Links table. relational engine will automatically generate it for you.

    As next step in the package, you'll need to populate the Listings table, where LinkID will be a FOREIGN KEY, linked to LinkID column of Links table. You can use the LookUp transform to get the value of LinkID from Links table and populate it in Listings table. Alternately, you can write SQL statement / stored procedure using JOINs to get this value.

    Hope this helps.

     

    Cheers!!

    Muqadder.

    Tuesday, March 30, 2010 6:36 AM
  • You can populate your Links table as a first step in your package sequence. The table structure should be somthing like

    LinkID INT IDENTITY (1,1) PRIMARY KEY,

    LinkURL NVARCHAR(<give some size here>),

    LinkDescription NVARCHAR (<give some size here>),

    ... and so on.

    You'll need to use UNPIVOT transform to convert Columns (LinkURL1, LinkURL2, LinkDescription1) to rows for your Links table. Since LinkID is marked as IDENTITY, you won't have to supply a value to this column while populating the Links table. relational engine will automatically generate it for you.

    As next step in the package, you'll need to populate the Listings table, where LinkID will be a FOREIGN KEY, linked to LinkID column of Links table. You can use the LookUp transform to get the value of LinkID from Links table and populate it in Listings table. Alternately, you can write SQL statement / stored procedure using JOINs to get this value.

    Hope this helps.

     

    Cheers!!

    Muqadder.

    Tuesday, March 30, 2010 6:36 AM
  • Thanks! That's exactly what I wanted! :) 

    It should be possible to add some kind of visualization here.

    Tuesday, March 30, 2010 10:34 AM