April 8, 2008 at 2:18 pm
My source table TableA primary key field Customer_Number is char(9) and my target table TableB primary key Customer_Number is char(10); it appears within the Lookup transformation that there are no matches but will fail during inserts due to duplicate records. TableB is exact copy of TableA but has the increase of the Customer_Number.
Thanks in advance for any help on this...
April 8, 2008 at 2:23 pm
When you use the char or nchar data type the string is right padded with spaces so 'Customer1' (Char(9)) and 'Customer1 ' (Char(10)) are different. Thus you need to either convert your source data to char(10) to get the right padding (Convert(char(10), column) or Right Trim the target data (RTRIM(column)).
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 8, 2008 at 2:58 pm
ok, I used the Derived Column transformation; selected Source Customer_Number, created new derived name New_Customer_Number. Changed Data Type to DT_STR, len 10. Still does not work on lookup with new column.
The Derived transformaton has no Convert string functions to select from.
Where are you using the expression (Convert(char(10),column) ??
April 8, 2008 at 3:06 pm
I would put the Conversion in the Select for the source. You did not specify the type of datasource so I gave SQL Server syntax your source's conversion function may be different.
Select Convert(char(10), customer_number) as customer_number, ... from source_table
I don't think the DT_STR pads the result which is why the Derived Column did not work, but I have not verified that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 9, 2008 at 6:28 am
DT_STR does not pad extra spaces.
SSIS is pretty sensitive to data types. You could trim the extra space off in the lookup source
SELECT RTRIM(MyKeyField), MyValue
I would also recommend upper-casing or lower-casing both sides - the lookup control is a case-sensitive match regardless of your database settings.
April 10, 2008 at 6:29 am
The convert on the select from source worked. Thanks again for the help.
May 8, 2008 at 2:40 pm
Need more help...now the package runs ok the first time as the target table is empty and the records are not found and performs all inserts. The second time, the lookup/conditional split shows some records being inserted and some staged to be used in an update statement. I would think the second time around, all records should be updated. I am using a Data Source reader for source table which is coming from an iSeries via a .net odbc provider.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply