RE: Do Not Keep NULLS - SSIS Bulk Insert Task - Insert Empty Strings Instead of NULLS

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi Ulysses,
I understand that you would like to have BULK INSERT task insert empty
string values to your database table instead of NULL. Your Data Flow Task
with Flat File Connection worked as you expected.
If I have misunderstood, please let me know.

Thank you for your detailed description so that I can easily reproduced
your issue. I tried both the BULK INSERT statement and bcp command (eg.
C:\>bcp GT.dbo.tempTestImport in "F:\TempData\test.txt" -S CharlesSQL2K5 -T
-c -t "|" -F 2), however they both inserted NULL.

From my research, this is by design for BULK INSERT and bcp if you did not
specify a default value for those table columns. From SQL BOL, we can find
the following description:
--------------------------------------------
KEEPNULLS
Specifies that empty columns should retain a null value during the bulk
load operation, instead of having any default values for the columns
inserted.
-------------------------------------------

As you can see, the KEEPNULLS option is used for eliminating the default
values impacting the inserted values. If you do not specify this option,
SQL Server will use the default values for those empty fields. If a default
value is not specified for a column and if the column is allowed to be
NULL, SQL Server will use NULL for the empty field.
You may also want to read this article:
Keeping Nulls or Using Default Values During Bulk Import
http://msdn.microsoft.com/en-us/library/ms187887.aspx

To work around this issue, you can set empty string as the default value
for those cxEmpty columns in your table, for example:
CREATE TABLE tempTestImport
(
c1Empty varchar(10) default '',
c2Empty varchar(10) default '',
c3Int int NULL,
c4Char char(4) NULL,
c5Bit bit NULL,
c6Empty char(4) default '',
c7Decimal decimal(4,2) NULL,
c8Decimal decimal(4,2) NULL,
c9Int int NULL,
c10Varchar varchar(10) NULL,
c11Space varchar(10) NULL,
c12Empty varchar(10) default ''
)

However the question is whether you want to save unknown value in the
field. If so, I recommend that you retain the NULL column and use UPDATE to
change NULL values to empty string.

I am not sure what the underlying implementation Data Flow Task is, however
from my investigation, I believe that it is essentially different from BULK
INSERT/bcp and that the output from Flat File Source has been handled
within Flat File Source component not in SQL Server because I can find the
option "Retain null values from the sources as null values in the DATA
FLOW" if I double click the Flat File Source in Data Flow Task. Apparently
Flat File Source component does something so that the submitted data to SQL
Server can keep empty string values or null values. The interesting
question is why we could not see the expected T-SQL statements in SQL
Profiler. This is also a question for me.

Now I am trying to consult our product team to see what happened here and
will let you know the response as soon as possible.

If you have any other questions or concerns, please feel free to let me
know.


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================







.

Relevant Pages