June 27, 2005 at 4:44 am
I have being trying to import a fixed width text file into a table that is set up with datetime, int, smallint format fields, however when I try to import the text file into the table I get converstion invalid for datatype error messages this happened on dates so I changed them back to varchar and planned to re ccast them later but now it is happening n all the smallint and I cant see where I am going wrong
if anyone can help or has any advice i would be greatful
June 27, 2005 at 9:21 am
Check that:
a - all the small int values are integer values
b - the small int values are small enough for that data type
June 28, 2005 at 9:35 am
Hi,
Well darnit, this has happened before. I type a long reply answering every question you will ever have; complete with code snippits, URLs and animation. But I click the "Post Reply" button and it goes into another world.
Briefly, I was going to say that getting input from text files has in my experience always been problematic. You can hardly ever trust the creator of the data to send you clean data. Since they are sending all character data, they can happily send you the character "a" instead of the character "1".
The tried and true method is to create a staging table where all the columns are CHAR. Move the text file data there first and you can use SQL tasks to do things like isnumeri(X) and isdate(X) to make sure the data they are sending you is sure nuff what you are expecting before you try to shove it into a typed table. I even check data headed for smalldatetimes and datetimes to make sure they fit into the SQLServer date range.
where( (isdate(XXXXXX) <> 1 OR
XXXXXX > '20790606' OR
XXXXXX < '19000101' )
Hope this makes it through this time.
Teague
June 28, 2005 at 9:44 am
Woops,
Maybe if I re-read my ramblings before posting them....
Actually the isdate() verifies something as being able to be converted to a date. The range only applies if you are headed for a Smalldatetime field. Datetime has a wider range.
teague
June 29, 2005 at 2:15 pm
Thanks for the advice I have had some luck havent got round to putting in the stage table I am however having the date trouble seems that when i do the transform data task if I take what is a date in the text file and use a datetime value on the transform to put it into the datetime column in the table it falls over with error msgs around "data overflow error 6" I was hoping I could find a way to over come this or use activeX script to move the value from the text file to the table datetime column with out creating the staging table between the text file and final table
if you do have anymore ideas if I can do this please let me know
once again thanks for your help
stewart
June 29, 2005 at 2:30 pm
'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************
I change the transformation task into an active-x task.
This is where is do my final fiddling with the data.
Like converting to decimal or changing blanks to NULL
or formatting my dates so they will go into sqlserver datetime
or smalldatetime columns.
Function Main()
DTSDestination("A") = CDbl(DTSSource("A") )
if DTSSource("B") <> " " then
DTSDestination("B") = DTSSource("B")
else
DTSDestination("B") = NULL
end if
DTSDestination("certadt") = DTSSource("ce_certadt")
DTSDestination("transid") = CDbl(DTSSource("transid"))
'formatting a date so SQLServer will recognize it as a data
In this case the date came in as a string in this format "yyyymmdd"
I could probably find a better way to accomplish the formatting but
This way was very simple and straightforward
DTSDestination("DateInQuestion") =
mid(DTSSource("DateInQuestion "),1,4) & "/" &
mid(DTSSource("DateInQuestion "),5,2) & "/" &
mid(DTSSource("DateInQuestion "),7,2) & " " &
mid(DTSSource("DateInQuestion "),9,2) & ":" &
mid(DTSSource("DateInQuestion "),11,2) & ":" &
mid(DTSSource("DateInQuestion "),13,2)
Main = DTSTransformStat_OK
End Function
Teague
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply