Import error from Excel, Alpha vs Numeric 1

[TheDrider]

I am losing data on import if a column contains both numeric and alpha-numeric data.

I have an AN field that usually has only numbers, but sometimes contains alpha characters. The database field is a varchar, and the excel column has been tried as both General and Text.

The import seems to only import one type of data and ignores the other. I tested with 5 sample rows. If I had more alpha data, it turned the numeric fields NULL, and vice versa.

Here is the VB-base DTS code for this column:

' EEIC Column
Public Sub oCustomTask1_Trans_Sub3(ByVal oCustomTask1 As Object)

        Dim oTransformation As DTS.Transformation2
        Dim oTransProps As DTS.Properties
        Dim oColumn As DTS.Column
        Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
                oTransformation.Name = "DTSTransformation__3"
                oTransformation.TransformFlags = 63
                oTransformation.ForceSourceBlobsBuffered = 0
                oTransformation.ForceBlobsInMemory = False
                oTransformation.InMemoryBlobSize = 1048576
                oTransformation.TransformPhases = 4
                
                Set oColumn = oTransformation.SourceColumns.New("EEIC", 1)
                        oColumn.Name = "EEIC"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 102
                        oColumn.size = 255
                        oColumn.DataType = 130
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.SourceColumns.Add oColumn
                Set oColumn = Nothing

                Set oColumn = oTransformation.DestinationColumns.New("EEIC_TX", 1)
                        oColumn.Name = "EEIC_TX"
                        oColumn.Ordinal = 1
                        oColumn.Flags = 104
                        oColumn.size = 5
                        oColumn.DataType = 129
                        oColumn.Precision = 0
                        oColumn.NumericScale = 0
                        oColumn.Nullable = True
                        
                oTransformation.DestinationColumns.Add oColumn
                Set oColumn = Nothing

        Set oTransProps = oTransformation.TransformServerProperties

                
        Set oTransProps = Nothing

        oCustomTask1.Transformations.Add oTransformation
        Set oTransformation = Nothing

End Sub

Sure, I could save it to csv first, but I also import remarks in some situations, so I'd like a better solution if I can find one.

Thanks for any insight you can offer.

 

[yksvaan]

You probably need IMEX setting, see JayKusch's solution on
thread961-603986

Same issue covered in

http://www.sqldts.com/default.aspx?254

Cheers


[blue]Backup system is as good as the latest recovery[/blue]