November 10, 2011 at 4:56 am
Hello ,
I am trying to insert few data into SQL Db through SSIS, the problem that I am running into is refrencing a column
I have inputs trough text file and Data is Pipe Delimited
order of data in a row is
Loantypeid,productid ,name, basetypeid
where Loantypeid is Primary Key and basetypeid is reference to Loantypeid.
taking example.
0001|098|active |0000
0002|098|balance |0000
0003|098|Fullbalance|0001
As you can see 3rd Record has Basetypeid as 0001 this means this type is similar to 1st record.
If I try inserting this Data in a virgin table ,its work fine.
But if these data is being inserted into a table already having the values and where first record occupies the primary id based on the current identity of that table , the refrencing fails.
for example if a table has Current Ident as 058 the data inserted will look like
58|098|active |0000
59|098|balance |0000
60|098|Fullbalance|0001
You will notice that the basetypeid still is remaining 0001 though that should have changed to 58 since it is referenced to first record of input file.
how can I overcome this challenge . Any ideas
Please let me know if my explaination is unclear
Thanks!
November 10, 2011 at 6:33 am
What are the exact columns of the text file?
If LoanTypeID is part of the source, how come it changes when you have already data inserted in the table? Your model is broken somewhere. Normally you'd do a lookup on the table to retrieve the LoanTypeID for that specific BaseTypeID, and insert the result. Not let it depend on the order it was inserted on.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply