I have two SSIS packages that import from the same flat file into the same
SQL 2005 table. I have one flat file connection (to a pipe delimited file)
and one OLE DB connection (to a SQL 2005 Database). Both packages use these
same two Connection Managers. The SQL table allows NULL values for all
fields. The flat file has "empty values" (i.e., |""|""| ) for certain
columns. There are several files like this (example below) with over 130
columns and several thousand rows for each file. It is not feasible to
provide the actual file so I am providing a small set as an example for
testing.

filePipe.txt
C1Empty|C2Empty|C3Int|C4Char|C5Bit|C6Empty|C7Decim al|C8Decimal|C9Int|C10Varchar|C11Space|C12Empty
CREATE TABLE tempTestImport
(
c1Empty varchar(10) NULL,
c2Empty varchar(10) NULL,
c3Int int NULL,
c4Char char(4) NULL,
c5Bit bit NULL,
c6Empty char(4) NULL,
c7Decimal decimal(4,2) NULL,
c8Decimal decimal(4,2) NULL,
c9Int int NULL,
c10Varchar varchar(10) NULL,
c11Space varchar(10) NULL,
c12Empty varchar(10) NULL
)
INSERT INTO tempTestImport
VALUES
('','',1,'Char',0,'',0.00,0.00,1,'Varchar',' ','')
INSERT INTO tempTestImport
VALUES
('a','b',1,'Char',0,'c',0.00,0.00,1,'Varchar',' ','d')
SELECT * FROM tempTestImport WHERE c1Empty IS NULL
--No NULL Records Exist (as expected)
--Now test this using bulk insert of pipe delimited file.
TRUNCATE TABLE tempTestImport

Though I have not used the example file snipet that I'm providing, I'm
confident you will be able to reproduce this behavior with any delimited
file that has blank values in the rows. Make sure your SQL table allows for
NULL values in the columns.

The first package uses the Data Flow Task with the "Keep nulls" property of
the OLE DB Destination Editor unchecked. The columns in the source and
destination are identically named thus the mapping is automatically assigned
and is mapped based on ordinal position (which is equivalent to the mapping
using Bulk Insert). When this task is executed no null values are inserted
into the SQL table for the "empty values" from the flat file. Empty string
values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property
for the task (shown in the Properties pane when the task in selected in the
Control Flow window) set to "False". When the task is executed NULL values
are inserted into the SQL table for the "empty values" from the flat file.


So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk
Insert Task NULL is inserted (i.e., nothing is inserted, the field is
skipped, the value for the record is omitted).

I want to have the exact same behavior on my data in the Bulk Insert Task as
I do with the Data Flow Task.

Using the Bulk Insert Task, what must I do to have the Empty String values
inserted into the SQL table where there is an "empty value" in the flat
file? Why & how does this occur automatically in the Data Flow Task?

From a SQL Profile Trace comparison of the two methods I do not see where
the syntax of the insert command nor the statements for the preceeding
captured steps has dictated this change in the behavior of the inserted ""
value for the recordset. Please help me understand what is going on here
and how to accomplish this using the Bulk Insert Task.

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

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 (AT) microsoft (DOT) com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.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/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Charles, thank you very much. I will patiently await a response regarding
the behavior in the SSIS Data Flow Task component. I was trying to avoid
having to manually modify dozens of tables each with hundreds of columns to
add the default " for each column that we expect to be NULL from the source.
I wanted to use the SSIS Bulk Insert Task for my work and achieve the same
behavior as I get with the Data Flow Task but you have correctly shown that
this is not possible.

I did not design this process or the application that depends on the data.
If so, I would simply deal with the NULL values from the flat file souce as
NULL values in the table. But this is not how the application currently
handles the data and will "bomb" on NULL values, it wants to see a blank
field instead of a NULL field. Anyway, I must spend time working on writing
the defaults for each table. So I must go to work now.

""Charles Wang [MSFT]"" <changliw (AT) online (DOT) microsoft.com> wrote