load a comma delimited flat file into SQL server table using ssis script task

I want to load the data from a comma delimited table into a temp table on sql server. I am using this code and it is working great. But since it is a "," delimited file, if any field in the file contains ',' then this code is not working. as in the replace function that "," is also replace. Any help

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports System.IO Imports system.Data.OleDb Imports Microsoft.SqlServer.DTSRuntimeWrap

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
' 
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
    Dts.TaskResult = Dts.Results.Failure
    Dim strFilePath As String = Dts.Variables("FilePath").Value.ToString
    Dim strCurrentZipFile As String = Dts.Variables("CurrentZipFile").Value.ToString
    Dim strConn As String = IO.Path.GetFileNameWithoutExtension(Dts.Variables("FilePath").Value.ToString)
    Dim strFields() As String = Dts.Variables("FilePath").Value.ToString.Split(",".ToCharArray())

    'Dts.Connections.Item(strConn).ConnectionString = strFilePath
    Dts.Connections.Item("EmpInfo").ConnectionString = strFilePath
    Dts.Variables("CurrentRawFile").Value = IO.Path.GetFileName(strCurrentZipFile)
    ' MsgBox(Dts.Variables("CurrentRawFile").Value)
    Dts.TaskResult = Dts.Results.Success


    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
    ' 
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.


    Dim cm As Con开发者_如何学JAVAnectionManager

    Dim con As OleDbConnection
    Dim cmd As New OleDbCommand()
    ' myADONETConnection = DirectCast(TryCast(Dts.Connections("Polldata").AcquireConnection(Dts.Transaction), SqlConnection), SqlConnection)

    '  MsgBox(myADONETConnection.ConnectionString, "PollData")

    Dim line1 As String = ""
    'Reading file names one by one
    Dim SourceDirectory As String = Dts.Variables("FilePath").Value.ToString
    cm = Dts.Connections("Polldata")
    Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters90
    cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters90)
    con = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)


    cmd.Connection = con
    'MsgBox(Dts.Variables("FilePath").Value.ToString)
    ' TODO: Add your code here
    '  Dim fileEntries As IO.DirectoryInfo = New IO.DirectoryInfo(SourceDirectory)
    ' MsgBox(fileEntries)
    ' For Each fileName As String In fileEntries.GetFiles()
    ' do something with fileName
    ' MsgBox(fileName)
    Dim columname As String = ""


    'Reading first line of each file and assign to variable
    Dim file2 As New System.IO.StreamReader(Dts.Variables("FilePath").Value.ToString) '(fileName)

    'Dim filenameonly As String = (((fileName.Replace(SourceDirectory, "")).Replace(".txt", "")).Replace("\", ""))
    'Create a temporary table 
    line1 = (" IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].tmp_empinfo" & "') AND type in (N'U'))DROP TABLE [dbo].tmp_empinfo" & " Create Table dbo.tmp_empinfo" & "(" & file2.ReadLine().Replace(",", " VARCHAR(100),") & " VARCHAR(100))").Replace(".txt", "")

    file2.Close()

    ' MsgBox(line1.ToString())
    cmd.CommandText = line1
    cmd.ExecuteNonQuery()


    'MsgBox("TABLE IS CREATED")

    'Writing Data of File Into Table
    Dim counter As Integer = 0
    Dim line As String = ""

    Dim SourceFile As New System.IO.StreamReader(Dts.Variables("FilePath").Value.ToString) '(fileName)
    While (InlineAssignHelper(line, SourceFile.ReadLine())) IsNot Nothing

        If counter = 0 Then
            columname = line.ToString()
            ' MsgBox("INside IF")
        Else

            ' MsgBox("Inside ELSE")
            Dim query As String = "Insert into dbo.tmp_empinfo" & "(" & columname & "  VALUES('" & line.Replace(",", "','").Replace("""", "") & "')"

            'Dim query As String = "Insert into dbo.tmp_empinfo" & "(" & columname & "  VALUES(" & strFields.ToString & ")"

            ' Dim query As String = "BULK INSERT dbo.tmp_empinfo FROM '" & strFilePath & "' WITH " & " ( " & " FIELDTERMINATOR = '|', " & " ROWTERMINATOR = '\n' " & " )"


            MsgBox(query.ToString())

            cmd.CommandText = query
            cmd.ExecuteNonQuery()
        End If

"I want to load the data from a comma delimited table into a temp table on sql server". Are you saying you basically already have a column in a table in a database that contains the data in a comma separated list? For instance,

SELECT column_name FROM schema.table

outputs something like some_data, more_data, even_more_data, even,more_data? And your problem is that the text isn't quoted, so some of the rows end up having extra phantom columns when you try to load it in your destination?

If this is the problem, then I would recommend introducing quoted identifiers in your source data before it is loaded into your source table. Meaning, whatever process that imports the data into that table needs to be fixed so that you don't have to deal with this kind of problem. If that can't be done, then you will have to build logic into your script component or sql select statement to split it out appropriately. The only way to fix the problem at this point would be to fix the data.

Did I misunderstand your intent or does this answer your question?