none
Load data with foreign key

    Question

  • Hi All,

    My pkg have a Data Flow with two connections Source= FoxPro and Destination =sql server.

    Destination table is customers with foreign key address_id of addresses table.

    In addresses table I have the customer_id as temp field.

    How can I get the foreign key of address_id at the time of loading the data from source customer table.

    Derived Column can use a variable to set the value of a column, or can I set the value using a select statement?

    I hope this is clear, any ideas?

    Thanks!

    Sunday, November 17, 2013 6:14 PM

Answers

  • One of the columns is not what you expect.  Instead of connecting your component to the Lookup, connect it to a Row Count component.  Then double-click on the connector to view the path metadata.  That should give you an understanding of where the problem may be.  If the metadata looks correct, then the lookup is not returning the metadata you expect for the lookup column.



    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    • Marked as answer by JFBconsul Tuesday, November 19, 2013 10:01 PM
    Monday, November 18, 2013 11:00 PM

All replies

  • I think you need to use lookup task to do the lookup with addresses table and get the addressid back to the data flow. Then this can be mapped to the corresponding field in customers table.
    Sunday, November 17, 2013 6:21 PM
  • Thanks for your reply and help.

    I am getting an error at the time of join:
    ------------------------------
    Cannot map the input column, 'id', to the lookup column, 'TempCustomerID', because the data types do not match.
    ------------------------------

    Source column "id" is four-byte signed integer [DT_I4]

    Join column "TempCustomerID" is int.

    What is the issue here?

    Best,

    Monday, November 18, 2013 10:06 PM
  • The data type of id is not matching with the data type of column TempCustomerID. 

    Go to your table which has TempCustomerID and click on Columns and then you will see the datatype in front of column. 

    Check the data type for ID as well. If conversion required. Then you can use Data Conversion Tack or Derived column to convert them to same data type.

    thank you

    Aamir


    http://sqlage.blogspot.com/

    Monday, November 18, 2013 10:23 PM
  • According to this link:

    http://technet.microsoft.com/en-us/library/ms141036.aspx

    DT_I4 = int in sql server

    The source data is visual fox pro.

    Any ideas?

    Monday, November 18, 2013 10:37 PM
  • I would look at the advanced properties of the FoxPro source component (right-click, show advanced properties).  What does the output for the "id" column show as its data type?  (Look at the input/output columns tab)


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    Monday, November 18, 2013 10:44 PM
  • Thanks for you reply and help.

    Like I said, id = four-byte signed integer [DT_I4]

    Monday, November 18, 2013 10:50 PM
  • One of the columns is not what you expect.  Instead of connecting your component to the Lookup, connect it to a Row Count component.  Then double-click on the connector to view the path metadata.  That should give you an understanding of where the problem may be.  If the metadata looks correct, then the lookup is not returning the metadata you expect for the lookup column.



    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    • Marked as answer by JFBconsul Tuesday, November 19, 2013 10:01 PM
    Monday, November 18, 2013 11:00 PM
  • Ok, when I connected with row count, the id = DT_SRT.

    How can i convert this to INT? and do the Lookup?

    Tuesday, November 19, 2013 9:30 PM
  • I got it, I used Data conversion component.

    Thanks for you reply and help Phil.

    Tuesday, November 19, 2013 10:01 PM