Bulk Insert Control Flow Task(SSIS) complete without loading the source file, but no errors too

Question

I try to load a csv file whose content is below, into a SQL Server 2012 database table NYC_Business_Establishment_DataSet using the "Bulk Insert Task" in SSIS 2012 control flow. The control flow task complete without any error, but I cant see this record in the target table. Please advice.

CSV File Content

DBA,Establishment Street,Establishment Zip,Establishment Borough,Business Sector,Establishment Category,Type of Cuisine,Number Of Employees,Actual Opening Date
Palermo Salumeria,33-35 Francis Lewis Blvd,11358,Queens,,,,,
Foragers City Grocers,300 West 22nd Street,10011,Manhattan,,,,,
Cultural Xchange,35 Lafayette Ave,11217,Brooklyn,,,,3,

Target Table USE [DB] GO

/****** Object:  Table [dbo].[NYC_Business_Establishment_DataSet]    Script Date: 5/19/2017 3:31:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[NYC_Business_Establishment_DataSet](
    [DBA] [varchar](1000) NULL,
    [Establishment Street] [varchar](1000) NULL,
    [Establishment Zip] [int] NULL,
    [Establishment Borough] [varchar](100) NULL,
    [Business Sector] [varchar](1000) NULL,
    [Establishment Category] [varchar](1000) NULL,
    [Type of Cuisine] [varchar](100) NULL,
    [Number Of Employees] [int] NULL,
    [Actual Opening Date] [date] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Thanks

add more details of CSV. with what you want into final output ? give link of CSV File — Mr. Bhosale, May 19 '17 at 10:36

score 0 · Accepted Answer · answered May 19 '17 at 16:57

0

There is a problem with input data file. For he last two columns [Number Of Employees],[Actual Opening Date], the data in the file was empty string and not null. So it is getting failed.

I am able to load the file using Data Flow Task. Added Derived column transformation to cast the strings to NULLs.

TRIM([Number Of Employees]) == "" ? NULL(DT_I4)  : (DT_I4)[Number Of Employees]
TRIM([Actual Opening Date]) == "" ? NULL(DT_DBDATE) : (DT_DBDATE)[Actual Opening Date]

answered May 19 '17 at 16:57

Srinivasa Rao

172
2
11

Hi Srinivasa, Thanks for your reply. Can you please advice how did you find that the last 2 columns had an empty string instead of null? I cant make out from the file. – Harinath Arasu May 22 '17 at 08:37
when it was failing with BCP, i tried loading it with DataFlow. Getting invalid character error in the column, So guessed it should be problem with empty sting. Once i added Derived column transformation the i am able to load the data successfully. – Srinivasa Rao May 22 '17 at 13:31
Wow intelligent, thanks for the tip. Will try once and close this question. – Harinath Arasu May 22 '17 at 13:43

score 0 · Answer 2 · answered May 25 '17 at 01:51

Selecting the checkbox for "Retain null values from the source as null values in the data flow" in the data flow resolved this issue.

The fields in the data file is consistent and issue occurs only when the target fields are integer and date.

I suppose the csv file when it has empty value, then it should be inserted as NULL.

Please let me know if anyone things otherwise.

Bulk Insert Control Flow Task(SSIS) complete without loading the source file, but no errors too

2 Answers2