DTS To SSIS Conversion

  • I am tying to convert a n DTS package to SSIS. I have used pragmatic works to convert so far but it does not convert Visual Basic.

    The code looks like

    Function Main()

    If Left(DTSSource("Col001"),6) = "STOCK" Or Left(DTSSource("Col001"), 6) = "PROJCT" Then

    DTSDestination("Column_Out") = Mid(DTSSource("Col001", 7, 25)

    DTSDestination("Column_Out_2") = Mid(DTSSource("Col001", 60, 25)

    Main = DTSTransformationStat_OK

    Else

    Main = DTSTransformationStat_SkipRow

    End if

    End Function

    With a little bit of research I was able to do this

    Public Overrides Sub NightlyInput_ProcessInputRow(ByVal Row As NightlyInputBuffer)

    Dim strRow As String

    strRow = Row.toString

    If (Left(strRow, 6) = "STOCK") Or (Left(strRow, 6) = "PROJCT") Then

    NightlyOutBuffer.ColumnOut = Mid(strRow, 7, 25)

    NightlyOutBuffer.ColumnOut2 = Mid(strRow, 60, 25)

    end if

    End Sub

    I don't seem to be getting any output from the code.

    Thanks for you help.

  • Hi James, inside the buffer code there, you want to say something like:

    Row.ColumnName = "Your New Value or code here".

    You don't necessarily have to reference the buffer name when you're setting a column's value. The Pragmatic Works tools can create new .net code but only in the control flow.

  • Brian,

    Thanks for the response. I had just figured it out literally seconds before your response.

    I actually had three issues:

    First:

    Public Overrides Sub NightlyInput_ProcessInputRow(ByVal Row As NightlyInputBuffer)

    Dim strRow As String

    strRow = Row.InputColumn()

    If (Left(strRow, 6) = "STOCK ") Or (Left(strRow, 6) = "PROJCT") Then

    NightlyOutputBuffer.AddRow()

    NightlyOutputBuffer.MRPPROJECT = Mid(strRow, 7, 25)

    NightlyOutputBuffer.MATLID = Mid(strRow, 60, 25)

    End If

    End Sub

    1. Row.toString() apparently returns the address of this buffer and not the actual data.

    2. It seems from my google searching that a row needs to be added to the OutputBuffer, otherwise you may be overwriting your initial data.

    3. The length of my input row was only 25 when it need to be in the excess of 200.

    I'm a programmer of eigth years, but was until recently a linux/java programmer.

    Tell Dustin I said hey.

    Jim

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply