asked on

SSIS Script Component

Hello
I have been trying to get this VB.net code tp work, but I get the follwoing error.

[OLE DB Destination [205]] Error: There was an error with input column "bdat" (277) on input "OLE DB Destination Input" (218). The column status returned was: "The value could not be converted because of a potential loss of data.".

I have six dates that need to work, some will have Null values. I have tried everything I know. ethier If I get it to work it will not process all the way, giving thie error also. Buffer is full can not load this column.

Here is my Code:

.For i As Integer = 0 To arr.Length - 1
With Output0Buffer
.AddRow()
.pcontrol = Row.pcontrol
.Recordid = Row.recordid
.hnum = Row.hnum
.bdat = Row.bdat - It will blow here
.adat = Row.adat
.prpd = Row.prpd
.ddat = Row.ddat
.stperiodf = Row.stperiodf
.stperiodt = Row.stperiodt

.zip = Row.zip
strSex = convtSex(Row.sex)
.sex = strSex
strLvdays = Row.lvdays.ToString.Trim

If strLvdays = "" Then
intlvday = 0
.lvdays = intlvday
Else
intlvday = CInt(strLvdays)
.lvdays = intlvday

End If

.race = Row.race.ToString()
.ethn = Row.ethn.ToString()
.adms = Row.adms.ToString()
.admt = Row.admt.ToString()
.pttype = Row.pttype.ToString()
.dxp = Row.dxp.ToString()
.dxa = Row.dxa.ToString()
.dxe1 = Row.dxe1.ToString()
.pina = Row.pina.ToString()
.pinb = Row.pinb.ToString()
.pinc = Row.pinc.ToString()
strprp = Row.prp.ToString()
If Len(Trim(strprp)) < 5 Then
.prpicd1 = strprp
Else
.prpcpt = strprp
End If
.prp = Row.prp
.prpmod1 = Row.prpmod1
.prpmod2 = Row.prpmod2
.prpmod3 = Row.prpmod3
.prpmod4 = Row.prpmod4
.ptstatus = Row.ptstatus
.sopid = Row.sopid
.soptype = Row.soptype
.provid = Row.provid
.tc = Row.tc
.billtype = Row.billtype
.mrn = Row.mrn
.ccode1 = Row.ccode1
.ccode2 = Row.ccode2
.ccode3 = Row.ccode3
.certnum = Row.certnum
.ecid = Row.ecid
.dxppoa = Row.DXPPOA
.DXRV1 = Row.DXRV1
.DXRV2 = Row.DXRV2
.DXRV3 = Row.DXRV3
'.quarteryear = strYr
'.quarternum = strQtr
ipControlCount += 1
End With
Next

8080_Diver

This looks a heck of a lot like a question you previously posted entitled "Derived column SSIS data conversion".

Reposting the same question is generally considered a violation of the rules of EE.

DevoinIT

ASKER

Hello 808Driver

This was the first step I was trying to get to work, but it will have problems with dates and buffer column. I am using a Script Compnent transformation on this process.

They are simlar, but a different process. I would like to get both of them them to work.

DevoinIT

ASKER

So far I have tried the Derived Column Transformation and the Script Component Transformation and the dates and null are having a problem.

8080_Diver

That is part of why I tend to use staging tables. In a SQL statement to accomplish the inserts, you can set up a CASE statement something like the attached to handle the NULL's.

CASE WHEN datecolumn1 IS NULL 
     THEN NULL
     ELSE CONVERT(DATEIMTE . . . ) 
END AS datecoumn1

Open in new window

DevoinIT

ASKER

I will try this stagging area and let you know. Just to let you know this will process about 1 million records into a staging area then move the data into another Server. I will see how long this process will take.

ASKER CERTIFIED SOLUTION

8080_Diver

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DevoinIT

ASKER

I did not want to take this route but I did not havea choice in order to get the work Out. Thanks