SSIS Data Flow Task from FIle to Database

Posted on 2009-04-23
Last Modified: 2013-11-10
HI there

I've been using the code below to splitting the "," (Comma delimited) in the Script Tranformation Editor.

Now it works well if the data like this:

The record will be:

But now I found another data that I want to reuse this method and the data like this:


The record will be:

Cause as you can see the it has double quotes and whateer inside that is considered as a record in order for to use a comma in records.

How do I do this then?

I prefer to this method below if if's possible. I am thinking in the Tokenise methods instead of handling just "," perhaps sets to "","" and obviosly it as " in the beginning record and last record BUT i can always REPLACE this later on as apart of cleansing. BTW ... the reason we took this tokenise methods is due to each row it has potentially varias in the number of column some could be 10 or some could be 14 BUT we now the maximum is 14 for instance.


' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components


Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper


Public Class ScriptMain

    Inherits UserComponent


    Private _RowNumber As Integer = 1



    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)


        'Debugging the Script Component The Script component does not support the use of breakpoints. 

        'Therefore, you cannot step through your code and examine values as the package runs.

        'It can be monitored the execution of the Script component by using the following methods:

        '- Interrupt execution and display a modal message by using the MessageBox.Show method in the 

        '  System.Windows.Forms namespace. (Remove this code after debugging is complete.)

        '- Raise events for informational messages, warnings, and errors. The FireInformation, FireWarning, 

        '  and FireError methods display the event description in the Visual Studio Output window; however, 

        '  the FireProgress method, the Console.Write method, and Console.WriteLine method do not display 

        '  any information in the Output window. Messages from the FireProgress event appear on the Progress tab of 

        '  SSIS Designer. For more information, see Raising Events in the Script Component.

        '- Log events or user-defined messages to enabled logging providers. For more information, 

        '  see Logging in the Script Component.


        Dim DestinationFileLocation As String

        Dim FileName As String


        DestinationFileLocation = CType(ReadVariable("User::DestinationFileLocation"), String)


        'MessageBox.Show("DestinationFileLocation: " & DestinationFileLocation)


        FileName = GetFileName(DestinationFileLocation)


        'MessageBox.Show("FileName: " & FileName)


        Row.Column1 = Tokenise(Row.Column0, ",", 1)

        Row.Column2 = Tokenise(Row.Column0, ",", 2)

        Row.Column3 = Tokenise(Row.Column0, ",", 3)

        Row.Column4 = Tokenise(Row.Column0, ",", 4)

        Row.Column5 = Tokenise(Row.Column0, ",", 5)

        Row.Column6 = Tokenise(Row.Column0, ",", 6)

        Row.Column7 = Tokenise(Row.Column0, ",", 7)

        Row.Column8 = Tokenise(Row.Column0, ",", 8)

        Row.Column9 = Tokenise(Row.Column0, ",", 9)

        Row.Column10 = Tokenise(Row.Column0, ",", 10)

        Row.Column11 = Tokenise(Row.Column0, ",", 11)

        Row.Column12 = Tokenise(Row.Column0, ",", 12)

        Row.Column13 = Tokenise(Row.Column0, ",", 13)

        Row.Column14 = Tokenise(Row.Column0, ",", 14)


        Row.SourcePathName = DestinationFileLocation.ToUpper()

        Row.SourceFileName = FileName.ToUpper()

        Row.SourceRowID = _RowNumber


        _RowNumber += 1



    End Sub


    Private Function Tokenise(ByVal input As String, ByVal delimiter As String, ByVal token As Integer) As String


        Dim tokenArray As String()

        tokenArray = input.Split(delimiter.ToCharArray) 'Split the string by the delimiter

        If tokenArray.Length < token Then 'Protect against a request for a token that doesn't exist

            Return ""


            Return tokenArray(token - 1)

        End If

    End Function


    Private Function GetFileName(ByVal input As String) As String


        Dim tokenArray As String()

        Dim fileName As String

        tokenArray = input.Split("\".ToCharArray)


        fileName = tokenArray(tokenArray.Length - 1)


        If fileName = "" Then

            fileName = "UNKNOWN.TXT"

        End If


        Return fileName


    End Function


    Private Function ReadVariable(ByVal varName As String) As Object

        Dim result As Object


            Dim vars As IDTSVariables90 = Nothing




                result = vars(varName).Value

            Catch ex As Exception

                Throw ex



            End Try

        Catch ex As Exception

            Throw ex

        End Try

        Return result

    End Function


    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)


            Dim vars As IDTSVariables90 = Nothing




                vars(varName).Value = varValue

            Catch ex As Exception

                Throw ex



            End Try

        Catch ex As Exception

            Throw ex

        End Try

    End Sub


End Class

Open in new window

Question by:dewacorp_alliances
    LVL 22

    Accepted Solution


    Author Comment

    Hi Pedro

    Looking at your solution, I can see that you have a script BUT you also doing the REPLACE " with blank which I think that take the wrong approach though. Cause it may have the data has comma in between:

    "TEST, TEST", "TEST 2" which is records supposed to be:
    TEST 2


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Viewers will learn how the fundamental information of how to create a table.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    733 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

    18 Experts available now in Live!

    Get 1:1 Help Now