>Here is what is going on. We have a system on one machine in which several ASCII files are downloaded nightly. At the time of this writing, I upload the ASCII files into foxpro data tables so other systems/reports/queries/ etc. can run in which the source server cannot produce.
>
>We are switching to VB.NET with SQL Server as our DataBase. All data conversion takes place at night unattended. Two examples of the data manipulation is as follows:
>
>1. Date fields. The source file uses a number that has to be converted to a true Date Value. 1 = 01/01/1968, 2 = 01/02/1968, etc.
>
>3. Numeric Fields. The source file leaves out all decimal points. Some numbers must be divided by 10, some 100, all the way up to 100000000.
>
>My current Foxpro application runs very fast. Converting the data using VB.NET posting to SQL server has been disappointingly slow. So, I'm looking for some sample code that has proven to be very fast when posting data.
>
>Thanks for any code suggestions.

Terry,

SQL DTS is the way to go. Custom data manipulations can be done in the "Transformation" step of the package. This includes shifting implied decimal points and creating a datetime from a value that is the number of days from a certain date. DTS uses ActiveX script that is very similar to VB Script to do custom data massaging.

To get started, use the import wizard in Enterprise Manager to import the text file into the database. Don't bother with datetime conversions or decimal points at first. Store those fields in varchar columns. The only concern is to get the wizard to create a complete DTS package that we can tailor later on. At the end of the wizard there is an option to save it as a DTS package. Save it as a local package and run it to get a feel for how it works.

Now call up the DTS package in Enterprise Manager by browsing the Data Transformation Services/Local Packages folder. When it is open, look for the Text file connection (it has a large text file icon). The silvery-black arrow leading away from the text connection to the database connection contains the transformation that needs editing. Right-click on the arrow and go into the properties.

In the properties, switch to the Transformations tab. Find the transformation that need editing by locating the line connecting the field in the text file to the column in the table for the datetime field. Click the line once to select the individual transformation in the dropdownlist. Delete the transformation (you will replace it with a different one).

Now create a new transformation by clicking New. Choose ActiveX script.
Choose the correct source and destination columns in the Source and Destination tabs. After choosing the source and destination, an ActiveX function is created that performs a straight copy. Go back to the General tab and click properties to get into the code editor.

Function Main()
	DTSDestination("myCol001") = DTSSource("Col001")
	Main = DTSTransformStat_OK
End Function

Something like the above code should be in the editor. You can insert any sort of string-handling or datetime calculations into this code using VB script functions and methods. There is a DateAdd function and a String.SubString method. Use & (amperstand) to concatenate two strings. Just make sure that the code assigns a value to DTSDestination("myCol01") and that the function returns DTSTransformStat_OK.

You can repeat this for any number of transformations.

Function Main()
        Dim tmpDate as Datetime
        tmpDate = DateAdd("d",Date(1968,1,1),DTSSource("Col001"))
	DTSDestination("myCol001") = tmpDate
	Main = DTSTransformStat_OK
End Function ' Not tested!!!!!

Finally, change the data type of the destination columns in the table to datetime and money and try out the new package.

DTS has been around for a long time and it is not developer friendly when it comes to designing the package (a package is their term for a self-contained applet that moves large amounts of data). But for a simple data load - which is what you have - it works well enough. And it performs very quickly.