February 6, 2008 at 11:11 am
Table has 3 field
ID length char 5, AnotherID varchar length 14, AccYr varchar length 6144
AccYr is the field that I have the problem with.
See data below the ü is a char(252) and ý char(253)
Some if these files populate and some do not.
I created another table for just this field (thinking truncating due to size) this did not help.
Text file being imported using DTS
total length 3041
AccYr looks like this:
01.2008ü02.2008ü03.2008ü04.2008ü05.2008ü06.2008ü07.2008ü08.2008ü09.2008ü10.2008ü11.2008ü12.2008ü01.2007ü02.2007ü03.2007ü04.2007ü05.2007ü06.2007ü07.2007ü08.2007ü09.2007ü10.2007ü11.2007ü12.2007ü01.2006ü02.2006ü03.2006ü04.2006ü05.2006ü06.2006ü07.2006ü08.2006ü09.2006ü10.2006ü11.2006ü12.2006ý01.2008ü02.2008ü03.2008ü04.2008ü05.2008ü06.2008ü07.2008ü08.2008ü09.2008ü10.2008ü11.2008ü12.2008ü01.2007ü02.2007ü03.2007ü04.2007ü05.2007ü06.2007ü07.2007ü08.2007ü09.2007ü10.2007ü11.2007ü12.2007ü01.2006ü02.2006ü03.2006ü04.2006ü05.2006ü06.2006ü07.2006ü08.2006ü09.2006ü10.2006ü11.2006ü12.2006ý01.2007ü02.2007ü03.2007ü04.2007ü05.2007ü06.2007ü07.2007ü08.2007ü09.2007ü10.2007ü11.2007ü12.2007ü01.2006ü02.2006ü03.2006ü04.2006ü05.2006ü06.2006ü07.2006ü08.2006ü09.2006ü10.2006ü11.2006ü12.2006ü01.2005ü02.2005ü03.2005ü04.2005ü05.2005ü06.2005ü07.2005ü08.2005ü09.2005ü10.2005ü11.2005ü12.2005ý01.2008ü02.2008ü03.2008ü04.2008ü05.2008ü06.2008ü07.2008ü08.2008ü09.2008ü10.2008ü11.2008ü12.2008ü01.2007ü02.2007ü03.2007ü04.2007ü05.2007ü06.2007ü07.2007ü08.2007ü09.2007ü10.2007ü11.2007ü12.2007ü01.2006ü02.2006ü03.2006ü04.2006ü05.2006ü06.2006ü07.2006ü08.2006ü09.2006ü10.2006ü11.2006ü12.2006ý01.2008ü02.2008ü03.2008ü04.2008ü05.2008ü06.2008ü07.2008ü08.2008ü09.2008ü10.2008ü11.2008ü12.2008ü01.2007ü02.2007ü03.2007ü04.2007ü05.2007ü06.2007ü07.2007ü08.2007ü09.2007ü10.2007ü11.2007ü12.2007ü01.2006ü02.2006ü03.2006ü04.2006ü05.2006ü06.2006ü07.2006ü08.2006ü09.2006ü10.2006ü11.2006ü12.2006
February 6, 2008 at 1:18 pm
Did you check the collation on this column?
February 6, 2008 at 1:28 pm
Not sure where to look?
February 6, 2008 at 4:48 pm
Since the Copy Column transformation will truncate columns without notification it might be worth switching to an ActiveX Script transformation. That would allow you to get some additional information to troubleshoot with and allow you to scan the input for special characters that may be causing the problem.
For example, the following will log the row number and data if it exceeds a set character limit. The log is set to the Exception file specified on the Options tab of the Transform Data Task Properties. Setting the Max error count to something above 0 to would let it continue after a row exception occurs.
' Copy each source column to the destination column
Function Main()
If Len(DTSSource("Col001") ) > 500 then
Main = DTSTransformStat_ExceptionRow
Exit function
End If
DTSDestination("Col001") = DTSSource("Col001")
Main = DTSTransformStat_OK
End Function
February 7, 2008 at 8:26 am
Okay, I did this:
' Copy each source column to the destination column
DTSDestination("MAID") = DTSSource("Col001")
DTSDestination("Mem_ID") = DTSSource("Col003")
DTSDestination("Ac_Ded_Yr") = DTSSource("Col033")
Function Main()
If DTSDestination("Ac_Ded_Yr") <> DTSSource("Col033") Then
Main = DTSTransformStat_ExceptionRow
Exit function
End If
Main = DTSTransformStat_OK
End Function
The error=
Step Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Step Error Description:ActiveX Scripting Transform 'DTSTransformation__1': Error parsing script - Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: Type mismatch: 'DTSSource'
Error on Line 5
.
Step Error code: 80042042
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:30631
I changed field Ac_Ded_Yr from varchar(6000) to nvarchar(4000).
Either way the field is blank, Maid and Mem_Id populate.
If I edit the txt field and delete half of the data it loads.
I checked then length to see if it exceeded 6000, but no error.
Is there another trap I can do to see the error?
February 7, 2008 at 9:05 am
The error is occurring because you're assigning the DTSSource=DTSDestination outside of the Main function. Assign those inside Main.
Second, the way you have the If condition written it will always be false and never return the ExceptionRow. Change it so it checks something about the DTSSource, such as length, characters, etc. If it passes, then assign it to the DTSDestination.
Also, consider that NVARCHAR will require twice as much space as a VARCHAR to hold the same number of characters. Now your column is limited to 2000 characters by using NVARCHAR(4000).
February 7, 2008 at 9:14 am
Okay changed to:
Changed field back to varchar(6000)
Function Main()
DTSDestination("MAID") = DTSSource("Col001")
DTSDestination("Mem_ID") = DTSSource("Col003")
If Len( DTSSource("Col033")) > "6000" Then
Main = DTSTransformStat_ExceptionRow
Exit function
End If
DTSDestination("Ac_Ded_Yr") = DTSSource("Col033")
Main = DTSTransformStat_OK
End Function
AC_Ded_Yr is not populate and there's no error.
MAIDMem_ID Ac_Ded_Yr
82625100321429*02
February 7, 2008 at 9:49 am
Make sure you're checking the Exception log, not the package error log.
If it's within your company's privacy and security policies post some of all of the input file as an attachment.
For debugging, adding a MsgBox function inside or outside the If statement would tell you how long the column is being seen as.
...
If Len(DTSSource("Col001") ) > 6000 Then
MsgBox(Len(DTSSource("Col001") ) )
Main = DTSTransformStat_ExceptionRow
Exit function
End If
...
February 7, 2008 at 10:17 am
I've attached the txt file.
I added the msgbox.
Where do I find the exception log?
February 7, 2008 at 11:31 am
Debra,
Thanks for the posting the input file.
The Exception file is set on the Options tab of the Transform Task Properties dialog.
Are you getting the impression it's truncating because of results returned in Query Analyzer?
If so, in Query Analyzer check Tools\Options\Results and change the Maximum Characters per column setting.
February 7, 2008 at 11:38 am
Have you tried using Bulk Insert to do this instead? Specifying codepage='raw' seemed to bring in all characters you had: otherwise some seem to go to unprintable characters.
I did a cut and paste into mytext.txt, and ran this:
bulk insert t from 'c:\temp\mytext.txt' with (codepage='raw')
and the t table seems to have the right results.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 7, 2008 at 11:40 am
Thanks I've set up the exception file.
I'm not using Query Analyzer:unsure:
If I delete part of col033 the file loads.
February 7, 2008 at 11:46 am
Okay, I'll need a little help.
In DTS I created a BulkInsertTask, how do I specify columns 1,3 and 33?
February 7, 2008 at 6:51 pm
If you aren't using Query Analyzer what are you using to determine that column isn't being copied in full?
I ran a DTS package to import that file and all 1439 characters of Col003 txf'd; tested with a Copy Column and ActiveX Script transformation. That was using a Text File source and OLEDB destination.
If you save the package as a visual basic or structured storage file and post that it might provide some insight.
February 8, 2008 at 5:20 am
I use DTS to map and load the data in a table.
I guess I do use QA to view the data.
See attached file.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply