July 10, 2003 at 8:34 am
I am using a DTS job to import data from a fixed-length ASCII file to a SQL Server 2000 DB. The default "Copy Column" transformation simply takes blanks and inserts them as spaces. What is the best way to insert NULLs instead of those spaces? Also, some of the data doesn't take up the whole length of the column, so I would like those trimmed of trailing spaces. I see that I can do a "Trim String" transformation, but how can I do that in conjunction with also checking for NULLs.
Ideally, I'd like all empty columns to be NULL and all data that doesn't take up the whole length of the column size to be trimmed. I am importing 184 columns and up to a million records if that makes a difference.
This is my first post here, so be gentle. 🙂
Terry
July 11, 2003 at 7:26 am
In DTS each column transformation can be either copy column or ActiveX script. If you change each column transformation to ActiveX you can then test the source and set the destination. eg
Function Main()
if rtrim(DTSSource("columnname"))="" then
DTSDestination("columnname") = null
else
DTSDestination("columnname") = rtrim(DTSSource("columnname"))
end if
Main = DTSTransformStat_OK
End Function
Far away is close at hand in the images of elsewhere.
Anon.
July 11, 2003 at 8:52 am
David,
Thanks for your reply. I dug into it yesterday and ending up writing the exact syntax you provided. Confirmation is always great.
My next question, is there an easy way to change the transformation for copy column to ActiveX? The only way I see is to delete the copy column and add the ActiveX. I have to do this for 184 fields, yikes!
Terry
July 11, 2003 at 9:00 am
Hi Terry,
If you delete all the transformations, select all the columns (source & dest), select ActiveX and new, then you should get a single script with one line per column copy. Edit as necessary. Still long winded but at least it's only one script.
Far away is close at hand in the images of elsewhere.
Anon.
July 11, 2003 at 9:05 am
Awesome, I'll give it a try. I definitely think that'll save some time. Thanks for you help.
Terry
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply