February 15, 2006 at 10:04 am
I'm stumped on this bug with my DTS transformation. It's a relatively simple transformatin, but it is giving me strange results depending on my input source.
A quick summary of the transformation:
1. Source is an input CSV text file in UNICODE format, fields separated by commas, rows delimited by CR/LF, text qualified with double quotes, no first row with column names.
2. Transformation is a pretty simple VBScript as follows:
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
Function HandleNull(a)
If (IsNumeric(a)) Then
HandleNull = a
ElseIf (IsNull(a)) Then
HandleNull = 0
Else
HandleNull = a 'is it character? This should cause it to bomb
End If
End Function
' Copy each source column to the destination column
Function Main()
If (DTSGlobalVariables("foundANSIFiles").Value = True) Then
DTSDestination("CurrencyCode") = DTSSource("Col013")
DTSDestination("Overdue91") = CCur(HandleNull(DTSSource("Col012")))
DTSDestination("Overdue61_90") = CCur(HandleNull(DTSSource("Col011")))
DTSDestination("Overdue31_60") = CCur(HandleNull(DTSSource("Col010")))
DTSDestination("Overdue1_30") = CCur(HandleNull(DTSSource("Col009")))
DTSDestination("CurrentAmount") = CCur(HandleNull(DTSSource("Col008")))
DTSDestination("TotalAmount") = CCur(HandleNull(DTSSource("Col007")))
DTSDestination("PaymentTerms") = DTSSource("Col006")
DTSDestination("VendorName") = DTSSource("Col005")
DTSDestination("VendorNumber") = DTSSource("Col004")
DTSDestination("Site") = DTSSource("Col003")
DTSDestination("Region") = DTSSource("Col002")
DTSDestination("DataDate") = CDate(DTSSource("Col001"))
End If
Main = DTSTransformStat_OK
End Function
3. Destination is a single table with the key defined as a combination of four columns. Nulls are not accepted in most of the fields. Character fields are defined as UNICODE (nchar).
To back up one step in my package, I have a VBScript step that concatenates all ANSI input files into a single UNICODE CSV file. Next, that same script concatenates all UNICODE input files and appends the result to the same UNICODE CSV file. This final UNICODE CSV file serves as my text file source for the transformation described above.
What's screwing things up is if I have no ANSI input files in step one and my UNICODE CSV file is produced from only UNICODE input files. This text file bombs on the transformation step with no data being detected (it tries to insert nulls into the table). The preview on Properties/Source/Preview shows the data just fine, but if I go to Properties/Transformations/Test, I see no data.
This led me to think my concatenation script had produced garbage from the UNICODE input data. But I'm not sure that this is the case. If there at least one record of ANSI data that is processed by the concatenation script, all the subsequently processed UNICODE data works just fine in the transformation step. I did another more direct test by creating a single line of ANSI data in a file and then a matching line of UNICODE data (same values for all fields, only in UNICODE format) in a second file. If I run the concatenation script on only the ANSI file I end up with a CSV file which I'll call "TEST1.CSV" for now. If I run the concatenation script on the single UNICODE input file I get "TEST2.CSV" file. TEST1.CSV goes through the transformation fine, TEST2.CSV file does not. But here's the stumper: TEST1.CSV and TEST2.CSV are an exact binary match (using a hex file comparison tool). It is EXACTLY the same data, yet the transformation does not work on the second file.
OK, that's a lot to write. If anyone would like to help me on this, I would greatly appreciate it. I can provide e-mailed samples, code, whatever if that aids in solving this.
Thanks.
February 15, 2006 at 11:25 am
I think step #1 for you should be to create a destination table with the same column names minus the primary keys, nulls allowed, and no other constraints.
Then see what gets populated into this table.
February 15, 2006 at 12:25 pm
OK, done. New testing Destination table (using same VBScript transformation code above) gets NULLs in every column when the file is processed. As before, if I do a preview of the source data when editing the transformation, I see each field with the correct value. Run the transformation, and everything is NULL.
February 15, 2006 at 12:31 pm
I know what it is...
Change this line:
If (DTSGlobalVariables("foundANSIFiles").Value = True) Then
To this line:
If (DTSGlobalVariables("foundANSIFiles").Value = -1) Then
February 15, 2006 at 12:47 pm
AAAAAHHHHHH!!!!!
Thanks so much. Actually, it wasn't the (-1) vs. (True) that was tripping me up, it was omitting the test of the value of global variable "foundUNICODEFiles" as an OR condition on the line you mentioned. I had totally ignored that line as I stared at the code over and over - it was actually a remnant of an earlier version where I was processing all the ANSI data separately.
Thanks again for pointing out the obvious to me. I guess the error message from the transformation wasn't real informative (since the bulk of my code was being skipped).
February 15, 2006 at 12:53 pm
Great! I'm glad that you worked it out.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply