August 13, 2004 at 8:12 am
I have created a fixed-length text source and a SQL OLE DB destination in a DTS package. When I try to run it I have a field 5 digits that is suppose to be an integer but the 5 digits have leading zero's. It is a quantity field
I get a string to integer conversion error due to the leading zero's.
any ideas on how to fix this? I will be creating a ActiveX script to dynamicaly pass the file name to the text source part of the DTS.
Thanks,
Jon
August 16, 2004 at 6:32 am
One straight forward approach is to load your data into a temporary table in SQL server, where the column in question is set up as varchar. Then move the data into your final destination table and do any transformations needed, such as convert(int, TroubleSomeField).
eg:
INSERT INTO final_table (key_field, TroublesomeField)
SELECT key_Field, convert(int, TroublesomeField) as TroublesomeField
FROM staging_table st
where not exists
(SELECT 'dummy value' from final_table ft where ft.key_field = st.key_field)
This approach lets you do validation of the data against your existing tables before adding the new records.
Regards
Peter
August 23, 2004 at 6:44 am
That is exactly what I ended up doing. I made a temp table as a holding tank for the data, then do all my transforming to the necessary tables.
Thanks for the response!
Jon
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply