January 16, 2006 at 8:25 am
Hi experts,
I want to load a text file into a SQL Server 2000 table by DTS.
One column in the text file is social security number, which is in a format of xxx-xx-xxxx.
How to load this column as a format xxxxxxxxx (get rid of the "-")???
Thank you.
January 16, 2006 at 10:40 am
2 choices.
If you have a transform data task, then for the column in question use a VBScript transform and use the VBScript Replace() function to strip the '-'.
Alternatively, load it as is, and once in the SQL table, run a T-SQL UPDATE that uses the T-SQL Replace() function.
January 17, 2006 at 4:47 pm
Just a follow-up to this - if you have a lot of data to work with, you'll probably be better off loading to a staging table where you can scrub out the '-'s or updating the database afterwards for the new data. ActiveX transforms are known for being slower than the native transforms (but very powerful).
Mostly depends on your environment.
-Pete
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply