none
SSIS : Import from CSV file to database table

    Question

  • Dear Experts,

    SSIS falt file to daatbase table import is only importing 9899 (1 header + 9898) rows though the file has 19000+ rows and giving a success message. Is there a limitation in importing flat file data to table only 9899 rows at once?? I have never seen this before..and why its showing as success where as only half of the data is imported?

    ISSUE BACKGROUND

    I am using SQL Server 2005 Sp4 (very recently applied SP4). I am importing data from Acive directory in a CSV file and then importing that data to SQL table and using it for reportig purpose. the import job runs once a day in the midnight.

    Since past 18 months I am managing this and have been facing issues in data import..the import fails many times with very strange error and takes lots of sleeples nigfht to fix it.

    Recently, it failed because the column delimiter for a particular column was not available. This error is very common and it sometimes works and sometimes it never gets fixed and I have drop that column from importing.

    I only came to know about the failure, when I found that data is missing from my table, saying this I mean to say though the package was importing only a part of the data and was failing to import the rest of the data, it was regstering as success in the job history...strange...

    Now, to fix the error, I dropped that particular column from importing and rerun the package. its only importing 9898 rows and giving a success message though my flat file has 19000+ rows. Why??? What might be the cause and why exactly 9898 rows???


    Thanks


    • Edited by MSSQL-DB Saturday, June 16, 2012 10:40 PM edit
    Saturday, June 16, 2012 10:33 PM

All replies

  • Sorry cannot test it right now ( do not have such 'big' text file) But perhaps as alternative 

    http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Sunday, June 17, 2012 4:21 AM
  • Thanks Uri,

    The problem here is my data is in the below format

    Col1                           Col2    Col3            Col4

    "abdc,scde,dhdjd",    ytytyt,  5464546,  "gdhdgd,hhdhdhd,gdgdgd"

    They are comman separated, as well as comma is used with in valued on one column also. So how to deal with this situation.

    Thanks,


    Thanks

    Sunday, June 17, 2012 7:18 AM
  • This format is asking for trouble...

    Would it be possible to change the column separator to a semicolon or a character that never appears in your columns when you export the file. I am pretty sure that would fix the issue..

    Thx

    Ludwig

    Sunday, June 17, 2012 9:22 AM
  • Thanks Ludwig,

    yes, that will resolve the problem but as I have told earlier, I am importing the data from Active directory. So I cant change the structure of the source data. As I want it to hapen automatically, so I think I have to write some script. I got a reference link below where this kind of issue is discussed and resolved. But this did not help me much, as I have Visual studio 2005 and the project code taht is available there is in VS2008 or more. So i was unable to open. Also, there is a solution at teh end using SQL scripts, but it did not work for me..somewhere..something was missing...so again no use..i ma also not good at coding that I will fix the error., :(

    http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes

    Also, I found another useful link below..tried that..it also did not work..it is a new tool, and I have npo time to play and learn new things when my production system has some issues...so it failed for me..i spent 4-5 hours with no luck.

    http://blog.sqlauthority.com/2011/11/02/sql-server-import-csv-into-database-transferring-file-content-into-a-database-table-using-csvexpress/

    So finally, back to basics. Again played with the same SSIS package..tried several ways..wasted the whole 3 days weekend..and got it working..

    What I did: I can say nothing..I choose not to import lot of columns which I generally never use for my reports..and it worked..

    Any how it is resolved for me.

    I would like to see more easiest solution from the experts..and so Iam keeping this thread open.

    Regards,


    Thanks

    Sunday, June 17, 2012 12:25 PM
  • SSIS from SQL 2012 now supports embedded qualifiers.
    Sunday, June 17, 2012 10:51 PM
  • first you will have to read all the fileds in one filed in a DFT by using a FFS_Connection object

    then use a script component and the mentioned code

    ' Microsoft SQL Server Integration Services Script Component
    ' Write scripts using Microsoft Visual Basic 2008.
    ' ScriptMain is the entry point class of the script.
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
    Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
    <Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _
    <CLSCompliant(False)> _
    Public Class ScriptMain
        Inherits UserComponent
        Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
            '
            ' Add your code here
            '
            Dim strRow As String
            Dim FirstIndex As Integer
            Dim LastIndex As Integer
            Dim strLen As Integer
            Dim strTemp As String
            Dim strTemp2 As String
            strRow = Row.Line.ToString
            strLen = strRow.Length
            strRow = Replace(strRow, ",", "|")
            FirstIndex = strRow.IndexOf("""", 0)
            If Len(strRow) > 0 And FirstIndex > -1 Then
                LastIndex = strRow.IndexOf("""", FirstIndex + 1)
                While FirstIndex <> -1 And LastIndex <> -1
                    strTemp = ""
                    strTemp2 = ""
                    'MsgBox("START ===>>>" & strRow & "--- FirstIndex " & FirstIndex.ToString & " --------- " & "LastIndex" & LastIndex.ToString)
                    If FirstIndex > 0 Then
                        strTemp = Left(strRow, FirstIndex)                      ' If the string doesn't start with a "
                        strTemp2 = Right(strRow, strLen - strTemp.Length)       ' only select after the FirstIndex Or after the fields processed
                    Else
                        strTemp2 = strRow.ToString                              ' If string starts with a "
                    End If
                    strTemp2 = Left(strTemp2, strTemp2.IndexOf("""", 1) + 1)    ' get string up to the next "
                    strTemp2 = strTemp2.Replace("|", ",")                       ' replace , with |
                    strTemp = strTemp & strTemp2                                ' Concat the 
                    If strLen > LastIndex Then                                  ' Concat the rest of the string after the LastIndex
                        strTemp = strTemp & Right(strRow, strLen - LastIndex - 1)
                    End If
                    strRow = strTemp.ToString                                   ' save change on each loop field change
                    'MsgBox(strRow & " --- strRow")
                    FirstIndex = strRow.IndexOf("""", LastIndex + 1)            ' Find next " field
                    LastIndex = strRow.IndexOf("""", FirstIndex + 1)            ' Find next " field
                    'MsgBox("FirstIndex" & FirstIndex.ToString & " --------- " & "LastIndex" & LastIndex.ToString)
                End While
                'Row.Line = strRow.ToString
            End If
            Row.Line = strRow.ToString
        End Sub
    End Class

    this will convert the , in between the fields to | (Pipleline) so that you can split the fields in the next object , and that will be another script component that gets the obe fileds as input and give a multiple fields as output

    good luck

    I even have the package sample if you like


    Sincerely Nik -- MCITP , MCTS -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Monday, June 18, 2012 2:16 AM
  • Hi Nik,

    The same issue is apearing again and hence I would like to understand the above mentioned way. I really dont understand what you have mentioned. Will you please clarify more on the below.

    "first you will have to read all the fileds in one filed in a DFT by using a FFS_Connection object

    then use a script component and the mentioned code"

     

    Thanks,


    Thanks

    Tuesday, December 04, 2012 6:46 PM
  • Will you please clarify more on the below.

    "first you will have to read all the fileds in one filed in a DFT by using a FFS_Connection object

    then use a script component and the mentioned code"

    read

    http://www.sql-server-performance.com/2007/import-text-files-ssis/

    you will have to read each record as one feild , the above linbe has 3 pages read it all

    good luck


    Sincerely Nik -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

    Tuesday, December 04, 2012 7:26 PM
  • Thanks a lot Nik. I am reading this article and will comeback to you for the next steps.

    Regards,


    Thanks

    Tuesday, December 04, 2012 7:42 PM