September 17, 2002 at 7:38 am
I have a DTS package that takes a text file and inserts it into a table. The problem that is occurring is that some of the fields in the table are of data type datetime and when the package runs, there are records that have ######## as the datetime. How can I run through the data file prior to scrub out all these ########
September 17, 2002 at 7:49 am
Is the series of hashes in the text file itself?
If so there is a workaround within the transformation iteself.
Open up the DTS and double click on the transformation arrow between the the data source and the destination. Move to the transformations tab. Move to the selected date column and double click on the transformation arrow. You can now tweak the way in which data is put into the destination column. You can use VBScript for this. Search out hashes, format date correctly etc.
Clive Strong
September 17, 2002 at 8:59 am
But won't that in fact adjust the way all the data is being entered into the table? For instance if I know that field A could possibly be either the Date or ###### then if I set the value to NULL then it would then set all of them to NULL regardless if there are records that actually have a datetime data. Here is he Active script as an example:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
IF IsDate(DTSDestination("PrecertEndDate")) = False THEN
DTSDestination("PrecertEndDate") = NULL
ELSE
DTSDestination("PrecertEndDate") = DTSSource("Col013")
END IF
Main = DTSTransformStat_OK
End Function
The problem is that it looks at the first record being entered and determines what the rest of the values would be regardless if there is a true datetime value set for any of the records being inserted.
September 17, 2002 at 9:56 am
I am not sure about it checking only the first value in the columna and assigning a value in the transformation for all records, but shouldn't your script be:
IF NOT IsDate(DTSSource("Col013")) THEN
DTSDestination("PrecertEndDate") = NULL
ELSE
DTSDestination("PrecertEndDate") = DTSSource("Col013")
END IF
Michael Weiss
Michael Weiss
September 17, 2002 at 9:58 am
Sorry...I didn't think that through...of course it is assigning a null for all fields if you think about it...it is not taking just the first value in col013 the way your code is written...you are checking the source column value BEFORE any transformation has been done...naturally it is going to assign a null value to each record because there is no value there in the first place thus your IsDate test fails...try the code the way I wrote it and see what happens...
hth,
Michael
Michael Weiss
Michael Weiss
September 17, 2002 at 10:00 am
I must need more coffee! My apologies...I meant your code is checking the destination column before any transformation has occured, not the source column...I am going for coffee now...lol
Michael Weiss
Michael Weiss
September 17, 2002 at 10:16 am
Still no go. There’s got to be a way to Loop through each record before it inserts it into the table. I was looking for a way to do this using the FSO prior to running the transformation. There must be a way to search and replace save and then us the new file as the source file!!!!
September 17, 2002 at 12:17 pm
What about setting up a staging table? bring the data in from the file to the staging table, clean it there via sql update statements, then load it into the target table....
An alternative is probably to use the data driven query task object...
hth,
Michael
Michael Weiss
Michael Weiss
September 20, 2002 at 2:15 am
I did think about doing that. That way I could set the two fields that are giving me the problems to varchar and then I could transform the data back into my source table and convert it using SQL. However I had our VB guy take a look at it and he created a script for me that searches for exactly ~######~ and replaces it with ~~ This was easy since the data file is the same every month and we know that the ~ location’s would be the same.
As far as the missing date was concerned we also knew that the position of this field was always at the 18th ~ over so we search for anything between these two ~'s and whenever there is white space we simply replace it from this ~ ~ to this ~~. After this script was created in VB we then imbedded the final exe on the front end of the DTS package. The script actually creates a new file for us and places it in the correct loading directory. The nice part about it is that the file isn't very big, 6000 plus records and the exe takes approximately 2 sec's to run.
Thanks for everyones help. If any would like to see the vb code I can post that as well.
Edited by - kbrady on 09/20/2002 02:16:22 AM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply