Help in DTS
-
Hoping someone can either help me or point me in the right place:
Just a little history:
I need to create a DTS package that bulk loads data from csv files into a SQL 2000 database. The files are downloaded from an FTP site and placed in a Windows directory. Each file name has the format XXX_WFR_###.csv. The numbers will always be a different sequence, but the characters in the front of the filename will always be one of 7 different character sets. Multiple files with the same characters can be in the directory (ie. BAN_WFR_6610.csv, BAN_WFR_6611.csv, etc).
OK, here's the fun part:
So far I have an Active X script that loops through the directory, takes a set of files with the same character set, and bulk loads them into tables. It'll then send an email based on success or failure. In addition to the failure message, the bad file gets moved off to another folder so it can be analyzed as to why it didn't load. There are a couple of issues though that I don't know how to deal with. The date comes to me in the form 44:41.0; how do I convert that to a date/time SQL can recognize? Also, certain coulmns in the csv file need to map to corresponding columns in our database (not the same names, etc.). How do I do that in DTS? I've only mapped column for column before.
Looking forward to help,
Jennifer
-
Hi,
In DTS designer, add a "Transform Data Task" object, giving the src as your csv file. After specifying the destination table, got to Transformations, add a new ActiveXScript, click properties, and add somthing like:
Function Main()
DTSDestination("dest_col_name") = DTSSource("src_col_name") ' or = DTSSource("col00x")
Main = DTSTransformStat_OK
End Function
...with your 'date', you'll need to do a Cast or Convert function, but you need to know what format/data type the source is in, first.
Hope this helps,
rich -
Thanks for the help Rich. I'll give that a try.
Jennifer
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply