none
need help with BCP file importing flat file skipping some characters RRS feed

  • Question

  • I have a file with 1402 character row length including CF and LF. After much reading I seem to have come upon syntax for a non XML format file, but what I made doesn't work. Two large sections of the row being read must be ignored.

    When run I get the following:

    Msg 4865, Level 16, State 1, Line 1
    Cannot bulk load because the maximum number of errors (10) was exceeded.
    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    If I recreate the table without the primary key [GUID] and [CreatedDate], when run I get the following:

    Msg 8152, Level 16, State 13, Line 1
    String or binary data would be truncated.
    The statement has been terminated.

    TABLE DEFINITION

    CREATE TABLE [zLocal].[SQLPROD1_HFS_Recipients_V2] (
    [GUID]                       uniqueidentifier  DEFAULT(NEWID()) PRIMARY KEY CLUSTERED,
    [ID_NUM]                     nvarchar(9),
    [SOC_SEC_NUM]                nvarchar(9),
    [LAST_NAME]                  nvarchar(14),
    [FST_NAME]                   nvarchar(9),
    [MI_NAME]                    nvarchar(1),
    [RACE_CD]                    nvarchar(1),
    [SEX_CD]                     nvarchar(1),
    [BRTH_DTE]                   nvarchar(8),
    [CLOSE_CODE]                 nvarchar(1),
    [DUAL_MEDICAID_MEDICARE_IND] nvarchar(1),
    [BEG_DTE1]                   nvarchar(8),
    [END_DTE1]                   nvarchar(8),
    [BEG_DTE2]                   nvarchar(8),
    [END_DTE2]                   nvarchar(8),
    [BEG_DTE3]                   nvarchar(8),
    [END_DTE3]                   nvarchar(8),
    [BEG_DTE4]                   nvarchar(8),
    [END_DTE4]                   nvarchar(8),
    [BEG_DTE5]                   nvarchar(8),
    [END_DTE5]                   nvarchar(8),
    [FAC_CODE1]                  nvarchar(6),
    [FAC_CODE2]                  nvarchar(6),
    [FAC_CODE3]                  nvarchar(6),
    [FAC_CODE4]                  nvarchar(6),
    [FAC_CODE5]                  nvarchar(6),
    [CreatedDate]                [datetime] DEFAULT (getdate()))
    GO

    FORMAT FILE

    10.0
    27
    01      SQLCHAR             0       09      ""      01     ID_NUM                                                     SQL_Latin1_General_CP1_CI_AS
    02      SQLCHAR             0       09      ""      02     SOC_SEC_NUM                                                SQL_Latin1_General_CP1_CI_AS
    03      SQLCHAR             0       14      ""      03     LAST_NAME                                                  SQL_Latin1_General_CP1_CI_AS
    04      SQLCHAR             0       09      ""      04     FST_NAME                                                   SQL_Latin1_General_CP1_CI_AS
    05      SQLCHAR             0       01      ""      05     MI_NAME                                                    SQL_Latin1_General_CP1_CI_AS
    06      SQLCHAR             0       01      ""      06     RACE_CD                                                    SQL_Latin1_General_CP1_CI_AS
    07      SQLCHAR             0       01      ""      07     SEX_CD                                                     SQL_Latin1_General_CP1_CI_AS
    08      SQLCHAR             0       08      ""      08     BRTH_DTE                                                   SQL_Latin1_General_CP1_CI_AS
    09      SQLCHAR             0     1140      ""      00     none                                                       SQL_Latin1_General_CP1_CI_AS
    10      SQLCHAR             0       01      ""      10     CLOSE_CODE                                                 SQL_Latin1_General_CP1_CI_AS
    11      SQLCHAR             0       01      ""      11     DUAL_MEDICAID_MEDICARE_IND                                 SQL_Latin1_General_CP1_CI_AS
    12      SQLCHAR             0       08      ""      12     BEG_DTE1                                                   SQL_Latin1_General_CP1_CI_AS
    13      SQLCHAR             0       08      ""      13     END_DTE1                                                   SQL_Latin1_General_CP1_CI_AS
    14      SQLCHAR             0       08      ""      14     BEG_DTE2                                                   SQL_Latin1_General_CP1_CI_AS
    15      SQLCHAR             0       08      ""      15     END_DTE2                                                   SQL_Latin1_General_CP1_CI_AS
    16      SQLCHAR             0       08      ""      16     BEG_DTE3                                                   SQL_Latin1_General_CP1_CI_AS
    17      SQLCHAR             0       08      ""      17     END_DTE3                                                   SQL_Latin1_General_CP1_CI_AS
    18      SQLCHAR             0       08      ""      18     BEG_DTE4                                                   SQL_Latin1_General_CP1_CI_AS
    19      SQLCHAR             0       08      ""      19     END_DTE4                                                   SQL_Latin1_General_CP1_CI_AS
    20      SQLCHAR             0       08      ""      20     BEG_DTE5                                                   SQL_Latin1_General_CP1_CI_AS
    21      SQLCHAR             0       08      ""      21     END_DTE5                                                   SQL_Latin1_General_CP1_CI_AS
    22      SQLCHAR             0       06      ""      22     FAC_CODE1                                                  SQL_Latin1_General_CP1_CI_AS
    23      SQLCHAR             0       06      ""      23     FAC_CODE2                                                  SQL_Latin1_General_CP1_CI_AS
    24      SQLCHAR             0       06      ""      24     FAC_CODE3                                                  SQL_Latin1_General_CP1_CI_AS
    25      SQLCHAR             0       06      ""      25     FAC_CODE4                                                  SQL_Latin1_General_CP1_CI_AS
    26      SQLCHAR             0       06      ""      26     FAC_CODE5                                                  SQL_Latin1_General_CP1_CI_AS
    27      SQLCHAR             0       98      "\r\n"  00     none                                                       SQL_Latin1_General_CP1_CI_AS



    • Edited by dmcdivitt Friday, November 15, 2019 3:18 PM
    Thursday, November 14, 2019 4:46 PM

Answers

  • I finally found a good Microsoft page describing the format file. The database column name doesn't matter, but the database column number does matter. With mine I needed to begin with 2 as the first database column since I want to skip [GUID]. The following works:

    10.0
    27
    01      SQLCHAR             0       09      ""      02     ID_NUM                                                     SQL_Latin1_General_CP1_CI_AS
    02      SQLCHAR             0       09      ""      03     SOC_SEC_NUM                                                SQL_Latin1_General_CP1_CI_AS
    03      SQLCHAR             0       14      ""      04     LAST_NAME                                                  SQL_Latin1_General_CP1_CI_AS
    04      SQLCHAR             0       09      ""      05     FST_NAME                                                   SQL_Latin1_General_CP1_CI_AS
    05      SQLCHAR             0       01      ""      06     MI_NAME                                                    SQL_Latin1_General_CP1_CI_AS
    06      SQLCHAR             0       01      ""      07     RACE_CD                                                    SQL_Latin1_General_CP1_CI_AS
    07      SQLCHAR             0       01      ""      08     SEX_CD                                                     SQL_Latin1_General_CP1_CI_AS
    08      SQLCHAR             0       08      ""      09     BRTH_DTE                                                   SQL_Latin1_General_CP1_CI_AS
    09      SQLCHAR             0     1140      ""      00     none                                                       SQL_Latin1_General_CP1_CI_AS
    10      SQLCHAR             0       01      ""      10     CLOSE_CODE                                                 SQL_Latin1_General_CP1_CI_AS
    11      SQLCHAR             0       01      ""      11     DUAL_MEDICAID_MEDICARE_IND                                 SQL_Latin1_General_CP1_CI_AS
    12      SQLCHAR             0       08      ""      12     BEG_DTE1                                                   SQL_Latin1_General_CP1_CI_AS
    13      SQLCHAR             0       08      ""      13     END_DTE1                                                   SQL_Latin1_General_CP1_CI_AS
    14      SQLCHAR             0       08      ""      14     BEG_DTE2                                                   SQL_Latin1_General_CP1_CI_AS
    15      SQLCHAR             0       08      ""      15     END_DTE2                                                   SQL_Latin1_General_CP1_CI_AS
    16      SQLCHAR             0       08      ""      16     BEG_DTE3                                                   SQL_Latin1_General_CP1_CI_AS
    17      SQLCHAR             0       08      ""      17     END_DTE3                                                   SQL_Latin1_General_CP1_CI_AS
    18      SQLCHAR             0       08      ""      18     BEG_DTE4                                                   SQL_Latin1_General_CP1_CI_AS
    19      SQLCHAR             0       08      ""      19     END_DTE4                                                   SQL_Latin1_General_CP1_CI_AS
    20      SQLCHAR             0       08      ""      20     BEG_DTE5                                                   SQL_Latin1_General_CP1_CI_AS
    21      SQLCHAR             0       08      ""      21     END_DTE5                                                   SQL_Latin1_General_CP1_CI_AS
    22      SQLCHAR             0       06      ""      22     FAC_CODE1                                                  SQL_Latin1_General_CP1_CI_AS
    23      SQLCHAR             0       06      ""      23     FAC_CODE2                                                  SQL_Latin1_General_CP1_CI_AS
    24      SQLCHAR             0       06      ""      24     FAC_CODE3                                                  SQL_Latin1_General_CP1_CI_AS
    25      SQLCHAR             0       06      ""      25     FAC_CODE4                                                  SQL_Latin1_General_CP1_CI_AS
    26      SQLCHAR             0       06      ""      26     FAC_CODE5                                                  SQL_Latin1_General_CP1_CI_AS
    27      SQLCHAR             0       98      "\r\n"  00     none                                                       SQL_Latin1_General_CP1_CI_AS
    

    • Marked as answer by dmcdivitt Friday, November 15, 2019 3:58 PM
    Friday, November 15, 2019 3:58 PM

All replies

  • Hi dmcdvitt,

    As an alternative, you can try to use SSIS. It would be much easier.

    SSIS has Flat File Source Adapter for your scenario.



    Thursday, November 14, 2019 7:04 PM
  • Thanks, for the suggestion but we are replacing SSIS packages due to difficulty of maintaining them. Because we want users to run jobs we developed a client-based tool providing that. If possible we will not be using any SSIS packages for the system.
    • Edited by dmcdivitt Thursday, November 14, 2019 8:13 PM
    Thursday, November 14, 2019 8:12 PM
  • I finally found a good Microsoft page describing the format file. The database column name doesn't matter, but the database column number does matter. With mine I needed to begin with 2 as the first database column since I want to skip [GUID]. The following works:

    10.0
    27
    01      SQLCHAR             0       09      ""      02     ID_NUM                                                     SQL_Latin1_General_CP1_CI_AS
    02      SQLCHAR             0       09      ""      03     SOC_SEC_NUM                                                SQL_Latin1_General_CP1_CI_AS
    03      SQLCHAR             0       14      ""      04     LAST_NAME                                                  SQL_Latin1_General_CP1_CI_AS
    04      SQLCHAR             0       09      ""      05     FST_NAME                                                   SQL_Latin1_General_CP1_CI_AS
    05      SQLCHAR             0       01      ""      06     MI_NAME                                                    SQL_Latin1_General_CP1_CI_AS
    06      SQLCHAR             0       01      ""      07     RACE_CD                                                    SQL_Latin1_General_CP1_CI_AS
    07      SQLCHAR             0       01      ""      08     SEX_CD                                                     SQL_Latin1_General_CP1_CI_AS
    08      SQLCHAR             0       08      ""      09     BRTH_DTE                                                   SQL_Latin1_General_CP1_CI_AS
    09      SQLCHAR             0     1140      ""      00     none                                                       SQL_Latin1_General_CP1_CI_AS
    10      SQLCHAR             0       01      ""      10     CLOSE_CODE                                                 SQL_Latin1_General_CP1_CI_AS
    11      SQLCHAR             0       01      ""      11     DUAL_MEDICAID_MEDICARE_IND                                 SQL_Latin1_General_CP1_CI_AS
    12      SQLCHAR             0       08      ""      12     BEG_DTE1                                                   SQL_Latin1_General_CP1_CI_AS
    13      SQLCHAR             0       08      ""      13     END_DTE1                                                   SQL_Latin1_General_CP1_CI_AS
    14      SQLCHAR             0       08      ""      14     BEG_DTE2                                                   SQL_Latin1_General_CP1_CI_AS
    15      SQLCHAR             0       08      ""      15     END_DTE2                                                   SQL_Latin1_General_CP1_CI_AS
    16      SQLCHAR             0       08      ""      16     BEG_DTE3                                                   SQL_Latin1_General_CP1_CI_AS
    17      SQLCHAR             0       08      ""      17     END_DTE3                                                   SQL_Latin1_General_CP1_CI_AS
    18      SQLCHAR             0       08      ""      18     BEG_DTE4                                                   SQL_Latin1_General_CP1_CI_AS
    19      SQLCHAR             0       08      ""      19     END_DTE4                                                   SQL_Latin1_General_CP1_CI_AS
    20      SQLCHAR             0       08      ""      20     BEG_DTE5                                                   SQL_Latin1_General_CP1_CI_AS
    21      SQLCHAR             0       08      ""      21     END_DTE5                                                   SQL_Latin1_General_CP1_CI_AS
    22      SQLCHAR             0       06      ""      22     FAC_CODE1                                                  SQL_Latin1_General_CP1_CI_AS
    23      SQLCHAR             0       06      ""      23     FAC_CODE2                                                  SQL_Latin1_General_CP1_CI_AS
    24      SQLCHAR             0       06      ""      24     FAC_CODE3                                                  SQL_Latin1_General_CP1_CI_AS
    25      SQLCHAR             0       06      ""      25     FAC_CODE4                                                  SQL_Latin1_General_CP1_CI_AS
    26      SQLCHAR             0       06      ""      26     FAC_CODE5                                                  SQL_Latin1_General_CP1_CI_AS
    27      SQLCHAR             0       98      "\r\n"  00     none                                                       SQL_Latin1_General_CP1_CI_AS
    

    • Marked as answer by dmcdivitt Friday, November 15, 2019 3:58 PM
    Friday, November 15, 2019 3:58 PM
  • Thanks, for the suggestion but we are replacing SSIS packages due to difficulty of maintaining them. Because we want users to run jobs we developed a client-based tool providing that. If possible we will not be using any SSIS packages for the system.

    Hi dmcdvitt,

    It is possible to launch SSIS packages as follows:

    • SSMS
    • SQL Server Agent
    • via a stored procedure (SP) call from anywhere
    • .Net code (c#, VB.NET)
    • Command prompt
    • Batch file (*.bat, *cmd)
    • Azure Cloud via SSIS Integrated Run-time (IR)
    • Etc.

    It it all documented  here: Run Integration Services (SSIS) Packages


    Friday, November 15, 2019 4:17 PM
  • I found the following works excellent, using the same format file. Instead of creating the table in advance with a primary key to manage, this creates the table and loads 5.5 million records in less than five minutes.

    SELECT * INTO zLocal.SQLPROD1_HFS_Recipients_V2 FROM OPENROWSET(
      BULK 'c:\hfs_import\'medicaid_recipients',
      FORMATFILE = 'c:\hfs_import\hfs_import.bcp'
      ) AS flatfile
    

    Friday, November 15, 2019 5:36 PM