?
Solved

SSIS Data Flow Task from FIle to Database

Posted on 2009-04-23
2
Medium Priority
?
773 Views
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:
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
Comment
Question by:dewacorp_alliances
2 Comments
 
LVL 22

Accepted Solution

by:
PedroCGD earned 1000 total points
ID: 24223528
0
 

Author Comment

by:dewacorp_alliances
ID: 24256912
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

840 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