none
SSIS - Unable to insert NULL for Blank fields in BULK INSERT

    Question

  • I m trying to load a .csv file into database using bulk insert.

    the file looks something like this

    A_301,B_301

    ,B_302

    A_303,B_303

     

    In the table the first column is a NON-Null field so the BULK INSERT task must fail when a NULL value is encountered however its inserting it successfully and using the default value specified for that column which is blank

    i tried using the option of BULK INSERT

    -Keep NULLS(to keep null value for blank columns)

    -Enable identity insert(to ignore default value if any for that column)

     

    Monday, December 08, 2008 6:15 AM

Answers

  • If the column in your destination table allows null, you should see the NULL gets inserted.
    • Marked as answer by Tony Tang_YJ Thursday, January 08, 2009 4:05 AM
    Tuesday, December 23, 2008 1:06 AM
    Answerer

All replies

  • Do you mean you have specified some default value for the first column?

    Monday, December 08, 2008 8:42 AM
  • Hello Zizy.

    I am not completely sure which exact question you are asking - the answer depends on how you bulk insert. Both SSIS and SQL Server have ways to do bulk inserts.

    I believe you are talking about SQL Servers BULK INSERT (ie. a T-SQL statement), and I think you might want different behavior per column (retain the NULL in any column except the first (identity) one)?

    If this is the case, your question might be better asked in the SQL Server Database Engine forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1).

    I believe the answer might be in using a format file (http://msdn.microsoft.com/en-us/library/ms179250.aspx), but I might be wrong.

    If you were using an SSIS bulk insert task (ie. you're using BIDS to author a .dtsx package), this is the right forum. In that case, you might want to try setting RetainNulls (http://msdn.microsoft.com/en-us/library/ms135923.aspx) on the flat file source you use to import the data.


    Cheers,
       - Roland


    Monday, December 08, 2008 9:28 AM
  • I havent specified any default value its picking up the blank value which is by default created for the column in SQL Server table

    Monday, December 08, 2008 9:39 AM
  • Im using SSIS Bulk Insert Task

    I have a table with two columns

    The first Column is a NON-NULL field

    My question is that when i leave the first column blank it should have failed the Bulk Insert Task instead its inserting the row successfully

    I have already check marked the following options of BULK INSERT

    -Keep NULLS

     

     

    .csv file contents

     

    A_301,B_301

    ,B_302

    A_303,B_303

     

    In the destination table all the 3 rows are inserted successfully

    Monday, December 08, 2008 9:45 AM
  •  zizy wrote:

    I m trying to load a .csv file into database using bulk insert.

    the file looks something like this

    A_301,B_301

    ,B_302

    A_303,B_303

     

    In the table the first column is a NON-Null field so the BULK INSERT task must fail when a NULL value is encountered however its inserting it successfully and using the default value specified for that column which is blank

    i tried using the option of BULK INSERT

    -Keep NULLS(to keep null value for blank columns)

    -Enable identity insert(to ignore default value if any for that column)

     

     

    Make sure that "check constraints" is also enabled in your bulk insert task along with "keep nulls."

    Monday, December 08, 2008 3:52 PM
    Moderator
  • Check constraint is also enabled.Dont understand why is it not failing and inserting the row successfully with empty string

    Tuesday, December 09, 2008 3:19 AM
  • when i changed the design for the column 1 to have NULL values and loaded the file using BULK INSERT it inserted NULL corresponding to that column

    However when i change the design for column 1 to not alllow NULL values . It again loads the row successfully but this time with an empty string but i expect BULK INSERT to fail

     

    1 A_307 B_407
    2 NULL B_408
    3 A_309 B_409

     

    1 A_307 B_407
    2 B_408
    3 A_309 B_409

    Tuesday, December 09, 2008 3:44 AM
  • Hello Zizy -

    Thank you for the clarification. When executing the bulk insert task in SSIS, it issues an actual bulk insert statement to the server. You can verify by generating and examining a log.

    Here's what gets executed in this case:

    BULK INSERT [Example].[dbo].[Table_1] FROM 'Example.csv' WITH(CHECK_CONSTRAINTS,CODEPAGE='RAW',DATAFILETYPE='char',FIELDTERMINATOR=',',ROWTERMINATOR='\n',KEEPNULLS)

    Running this bulk insert query from an sql shell results in exactly the same behavior: SQL server will happily import the file and insert blank strings in the non-null column instead of the NULL default value.

    I have no explanation on why BULK INSERT in T-SQL would behave this way or how to configure it so that it doesn't.
    You might want to redirect your question to the database and t-sql forums on http://social.msdn.microsoft.com/forums/en-US/sqldatabaseengine/threads/.

    If you are looking for a practical solution using SSIS, you could use a data flow task and a conditional split or derived column (to either filter out NULL values for the first column, or ensure an actual attempt to write 'null' to the db).

    Cheers,
      - Roland



    Monday, December 15, 2008 11:54 AM
  • Hi Zizy,

    I am facing a similar problem of not being able to insert NULL for Blank fields in BULK INSERT. Have you figured out something. Please let me know if you have a solution.

    Thanks,
    Amar
    Thursday, December 18, 2008 1:46 PM
  • If the column in your destination table allows null, you should see the NULL gets inserted.
    • Marked as answer by Tony Tang_YJ Thursday, January 08, 2009 4:05 AM
    Tuesday, December 23, 2008 1:06 AM
    Answerer
  • hi zizy ;

     

    i have same problem ? if you got the solution then pls let me know......

     i cant use use "data Flow Task" .. i have to use "Bulk insert" ....

     

    hiral

    Thursday, April 28, 2011 3:20 PM