• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

SSIS Data Flow Task from FIle to Database

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:
TEST,TEST1,TEST2

The record will be:
TEST
TEST1
TEST2

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

"TEST","TEST , TEST","TEST3"

The record will be:
TEST
TEST, TEST
TEST3

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.

Thanks


' 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 ""
        Else
            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
        Try
            Dim vars As IDTSVariables90 = Nothing
            Me.VariableDispenser.LockForRead(varName)
            Me.VariableDispenser.GetVariables(vars)
            Try
                result = vars(varName).Value
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            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)
        Try
            Dim vars As IDTSVariables90 = Nothing
            Me.VariableDispenser.LockForWrite(varName)
            Me.VariableDispenser.GetVariables(vars)
            Try
                vars(varName).Value = varValue
            Catch ex As Exception
                Throw ex
            Finally
                vars.Unlock()
            End Try
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
 
End Class

Open in new window

0
dewacorp_alliances
Asked:
dewacorp_alliances
1 Solution
 
PedroCGDCommented:
0
 
dewacorp_alliancesAuthor Commented:
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, TEST
and
TEST 2




0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now