July 1, 2009 at 2:23 am
Hi
I have a Excel file and i have loaded the excel file data into table1 which for the validation purose and after the validation i need to load that data into one more SQL table say table2 and the insertion of records is the records which are not present in the table2 so i need to look up on table2 and if the records are going to error rows in the look up i am considering those records are as new rows and inserting into table2 and its working fine
For the second run i am keeping same file and running this time it should not go to error output since the same records exists in the table2 and i m doing look up on table2 maching each and every cloumn which i need to match.
Is there any property to set such that it cant be duplicate the rows??
I appriciate for the help.. Regards.
Reddy
July 1, 2009 at 6:07 am
Hi,
Not sure why you would need to run the same package twice, but in answer to your question you can copy records using the multicast.
HTH
Kindest Regards,
Frank Bazan
July 1, 2009 at 6:14 am
I was just re reading your post and I think I misunderstood what you were asking...
Are you able to elaborate some more on what you're trying to achieve? Are you saying that the reference dataset has duplicate values in it?
Kindest Regards,
Frank Bazan
July 1, 2009 at 7:30 am
Ok Let me elobrate the things...
Here is my flat file which i ill get daily...suppose i have got the flat file today with 10 records and i have loaded the data into one table say table1 and i wanna do some validation from the reference table..after the validation i am loading the data into the one more table say table 2 but note that i m using the look up to load into table2.Now i can get the same file 2marrow with only 11 records where one record is added here and all 10 records are same.so i need to load only one record which is coming new.
For this approch i m using look up on table2 and loading the missing rows into table2 now the problem even i m matching all the rows in the lookup its redirecting the rows to error output and inserting 11 records once again.
Just want to know any property need to set for the Look up such that it can only load the missing rows into my table2
Thanks In Advance
July 1, 2009 at 11:31 am
If there are columns in table 2 that are string datatypes (char or varchar), there are some things to consider when trying to get the lookup to match.
If there are char columns, you must ensure that the string values fed into the lookup task from the new file are padded with the appropriate amount of whitespace. For example, there's a column String1 char(4) in table2. Any value from the new file that is shorter than 4 characters will need to be padded until it is. 'abc' 'abc '.
Likewise, if there are varchar columns in table 2, you must make sure you trim whitespace from the values fed into the lookup, as the values stored in those columns in table 2 will not have any extra whitespace on either end.
Hope this helps!
July 1, 2009 at 2:37 pm
Jonathan is right. Lookup works differently for empty space. I had these issue. Use TRIM function to trim both left n right when you do lookup. Use in both source and lookup tbl.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply