Solved

DTS Import datetime field problem from Flat File SQL2000

Posted on 2006-06-25
13
866 Views
Last Modified: 2013-11-30
I am trying to import large flat files into my Table using DTS.

The import always failed due to the Date fields error:

TransformCopy 'DirectCopyXForm' conversion error: Conversion invalid for datatypes on column pair 27 (source column 'Col027' (DBTYPE_STR), destination column 'last_modified' (DBTYPE_DBTIMESTAMP))..... and so on for the other date fields that are in my table.  

My current solution, is to convert those fields to varchar and then import the file, then reconvert them back to datetime.  It works, but there has to be a better way, a right way to fix this problem??

Any suggestions??
0
Comment
Question by:hu8mypho
  • 7
  • 5
13 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16980412
>>Any suggestions??<<
Without knowing the date format and/or sample data we can only speculate.
0
 
LVL 1

Author Comment

by:hu8mypho
ID: 16980578
Here is a sample of the date data that is being imported:

Nov 10 2005  2:57:42:000PM :  I format the field in the destination table as datetime but DTS will not work.  

If I convert to varchar then import it works.  Then afterwards, I have to convert back to datetime.

I hope this is enough info.  I can post more if needed.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16980630
If you can lose the milliseconds than you use some ActiveX function like this:

Function GetDate (Byval Dat)
Dim Dat

Dat = Left (Dat, 20) & Mid(Dat, 25, 2)                   'Result: MMM dd yyyy hh:nn:ss?M

If IsDate(Dat) Then
  GetDate = CDate(Dat)
Else
   GetDate = Null
End If

You can then call it as follows:

DTSDestination("YourDateColumn") = GetDate(DTSSource("Col001"))
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16980635
What is happening is that the Date variant in VBScript does not support milliseconds.  If the milliseconds are important to you than you will have to import the table as a varchar and then convert to a datetime using T-SQL functions.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16980647
In order to convert from a string with milliseconds to a datetime using T-SQL you would use something like this:

CONVERT(datetime, 'Nov 10 2005  2:57:42:123PM', 109)
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16980755
There is a transform of type 'DateTime String' in DTS. Have a play with that.


Alternatively, I am successfully importing datetime data in that format using BULK INSERT so you may wish to try using that instead of DTS.

Sample usage:

BULK INSERT YourTable
FROM 'D:\YourFile.TXT'
WITH (
FIELDTERMINATOR = ',',
TABLOCK
)

The count and datatype of fields need to exaclty match though.
0
 
LVL 1

Author Comment

by:hu8mypho
ID: 16988673
I'm going to try the activeX function, do I include it inside of the main function in the transformation script?

Alternatively, I have never used a bulk insert before, can you please specify?  
I have around 30 fields of which 5 are datetime formats, thanks for the help.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16988960
>>do I include it inside of the main function in the transformation script?<<
It would be separate of the Main function in the same ActiveX Script Task as in:

Function Main()
      DTSDestination("YourDateColumn") = GetDate(DTSSource("Col001"))
      ' Rest of your columns go here
      Main = DTSTransformStat_OK
End Function

Function GetDate (Byval Dat)
Dim Dat

Dat = Left (Dat, 20) & Mid(Dat, 25, 2)                   'Result: MMM dd yyyy hh:nn:ss?M

If IsDate(Dat) Then
  GetDate = CDate(Dat)
Else
   GetDate = Null
End If

0
 
LVL 1

Author Comment

by:hu8mypho
ID: 16989119
sample:"
      DTSDestination("columnx") = DTSSource("Col001")
      Main = DTSTransformStat_OK
End Function

Function GetDate (Byval Dat)
Dim Dat

Dat = Left (Dat, 20) & Mid(Dat, 25, 2)
If IsDate(Dat) Then
  GetDate = CDate(Dat)
Else
   GetDate = Null
End If  "

Error: "Activex Scripting Transform 'AxScriptXform': Error parsing script = Error Code: 0 Error Source=Microsoft VBScript compilation error Error Description" Name redefined "

Error on Line indicates the : "Function GetDate (Byval Dat)" line
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 16989139
Absolutely, left an extra line and left off the final line.  
Please delete the line: Dim Dat
Add the line at the end: End Function

Your function should look like this:

Function Main()
     DTSDestination("YourDateColumn") = GetDate(DTSSource("Col001"))
     ' Rest of your columns go here
     Main = DTSTransformStat_OK
End Function

Function GetDate (Byval Dat)

Dat = Left (Dat, 20) & Mid(Dat, 25, 2)                   'Result: MMM dd yyyy hh:nn:ss?M

If IsDate(Dat) Then
  GetDate = CDate(Dat)
Else
   GetDate = Null
End If

End Function
0
 
LVL 1

Author Comment

by:hu8mypho
ID: 16989246
Sweet Thanks, It's importing...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16989259
Make sure to check after the import finalizes that the dates are converted correctly.  If the function GetDate does not understand the date than it will convert to Null.
0
 
LVL 1

Author Comment

by:hu8mypho
ID: 16989674
Awesome, thank you its working, I'm kind of new to MS SQL .  
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 40
cannot connect to sqlserver 8 25
create an aggregate function 9 31
Increment column based of a FK 8 20
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question