Solved

SSIS Script Component

Posted on 2010-11-30
7
538 Views
Last Modified: 2013-11-10
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
0
Comment
Question by:DevoinIT
  • 4
  • 3
7 Comments
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34239324
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.
0
 

Author Comment

by:DevoinIT
ID: 34239374
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.
0
 

Author Comment

by:DevoinIT
ID: 34239387
So far I have tried the Derived Column Transformation and the Script Component Transformation and the dates and null are having a problem.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 22

Expert Comment

by:8080_Diver
ID: 34239428
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

0
 

Author Comment

by:DevoinIT
ID: 34239479
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.
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 250 total points
ID: 34239986
Using a staging table will possibly present other overall improvements because you won't be pulling nearly as much into memory before processing and moving to the other database/table.
0
 

Author Closing Comment

by:DevoinIT
ID: 34346063
I did not want to take this route but I did not havea choice in order to get the work Out. Thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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 …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now