DTS Transform/Package Help...
-
Hello, all! I am trying to put together a fairly basic load and transform process and am, for some reason, just having a complete mental block on the best way to do it (my DTS books present too many options, perhaps, and definitely not enough detail). Anyway, if anyone can give me a quick idea of the best way to accomplish my tasks (and maybe a pointer to where I can find some decent VBScript code that might help), I would much appreciate it.
- Data is in a text file, delimited by a '~' (don't ask). It is mainframe data, so the date formats are all sorts of wrong and times are in a separate column from the dates. The text file will have the same name and be overwritten with new data on a periodic basis (this is an ongoing battle, MF group wants to use date based filenames, using Julian dates!)...it will reside on a PC and be ftped from the MF (PC does have Enterprise Manager on it).
- I was going to use a multi-step process, where I load the data into a blank 'staging' table (purged at the end of this whole process) all in text fields, then do the transform and append the repaired data into a final table with correct data types in a separate step (overwriting any duplicates that are in there, based on the primary key, as these updates would be newer data). Probably use Bulk Insert for this initial load (though I currently have a simple copy column step now).
- At this point, I have managed to figure out the VBScript for concatenating the date & time columns and rearranging the dates into the correct format so it will import into a 'datetime' datatype column.
- The problem is that some of the dates, being manually entered by humans, are invalid and the whole process pukes when it hits one...what I want to do is put those rows into an exception table or file and continue on with the load. The data are records that are updated periodically, so I want to overwrite the rows in the final data table based on the primary key (we are also flirting with the idea of replicating the rows into another table with no key, to provide a kind of history).
I guess the main questions are: How best to handle exceptions? How best to overwrite existing rows in the final destination table? Is the process I've kind of outlined here anything close to being decent?
Thank you all in advance for your help!
Chris
-
This was removed by the editor as SPAM
-
Im having a similar problem trying to parse a | delimited .asc file with " around "text".
When I change to single quotes it works, but then I have quotes around all of my text.
No idea why this happens and have no idea how to fix.
-
Can you set the around text to none?
-
In response to the first item in the thread, which I responded to first, but it isn't showing on the thread, but the second response to the delimiting question did...
I have two ideas for you.
1) import the data into a validation table, setting the field type of the date field to varchar. Then, in a stored procedure, do a case statement on the date field and accept the data as a date or either correct the date's format or exclude it from the append statement.
2) alternatively, in the vbscript, go through the data line by line, using Open <filename> for input as #1, go through each input until you get to the date column then validate it and change it as necessary there, and doing either a file out or a recordset append, which ever turns out to be faster. Let me know if you need some sample code to get you started.
-
Thank you for replying...
The conversion of the dates into the appropriate format does work, as I am using VBScript to rearrange the dates and they are accepted into the second table as datetime.
I would love to see some sample code, however, as most of the rest of this thing is all up in the air...if you can send me some, I'd really appreciate it!
Thanks, again...
Chris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply