Hi Ulysses,
Sorry for letting you wait a long time. I just got the confirmation from
the product team. The NULLs you saw in BULK INSERT is expected since the
default value is NULL. SSIS Data Flows have its own engine for transferring
and manipulating data and they do not use SQL Server for that. You cannot
simply draw a parallel between BULK INSERT and Flat File Source. BULK
INSERT loads flat files into defined SQL Server table. Flat File Source
pumps data into SSIS Data Flow buffers to be used by its downstream
components. The source does not know how the data it produces is going to
be used or where it is going to end up at (it could be table, it could be
Excel sheet, another file or something else). There is no T-SQL associated
with data flows. You may notice T-SQL statements issued by specific
components while reading or inserting data but they do not describe the
entire flow.

SSIS Data Flows do not have concept of default values so empty strings are
going to be loaded as they are. There is a property on the Flat File source
that will allow you to convert empty string into NULLs. The property name
is RetainNulls.

So regarding your concern, we can conclude that indeed it is not possible
for SSIS Bulk Insert Task to perform the same behavior as you saw in your
Data Flow Task. They are essentially different. As a workaround, you can
continue using your Data Flow Task or specifying empty string as the
default values for your tables columns.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

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.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Ulysses,
Sorry for letting you wait a long time. I just got the confirmation from
the product team. The NULLs you saw in BULK INSERT is expected since the
default value is NULL. SSIS Data Flows have its own engine for transferring
and manipulating data and they do not use SQL Server for that. You cannot
simply draw a parallel between BULK INSERT and Flat File Source. BULK
INSERT loads flat files into defined SQL Server table. Flat File Source
pumps data into SSIS Data Flow buffers to be used by its downstream
components. The source does not know how the data it produces is going to
be used or where it is going to end up at (it could be table, it could be
Excel sheet, another file or something else). There is no T-SQL associated
with data flows. You may notice T-SQL statements issued by specific
components while reading or inserting data but they do not describe the
entire flow.

SSIS Data Flows do not have concept of default values so empty strings are
going to be loaded as they are. There is a property on the Flat File source
that will allow you to convert empty string into NULLs. The property name
is RetainNulls.

So regarding your concern, we can conclude that indeed it is not possible
for SSIS Bulk Insert Task to perform the same behavior as you saw in your
Data Flow Task. They are essentially different. As a workaround, you can
continue using your Data Flow Task or specifying empty string as the
default values for your tables columns.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

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.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Ulysses,
Sorry for letting you wait a long time. I just got the confirmation from
the product team. The NULLs you saw in BULK INSERT is expected since the
default value is NULL. SSIS Data Flows have its own engine for transferring
and manipulating data and they do not use SQL Server for that. You cannot
simply draw a parallel between BULK INSERT and Flat File Source. BULK
INSERT loads flat files into defined SQL Server table. Flat File Source
pumps data into SSIS Data Flow buffers to be used by its downstream
components. The source does not know how the data it produces is going to
be used or where it is going to end up at (it could be table, it could be
Excel sheet, another file or something else). There is no T-SQL associated
with data flows. You may notice T-SQL statements issued by specific
components while reading or inserting data but they do not describe the
entire flow.

SSIS Data Flows do not have concept of default values so empty strings are
going to be loaded as they are. There is a property on the Flat File source
that will allow you to convert empty string into NULLs. The property name
is RetainNulls.

So regarding your concern, we can conclude that indeed it is not possible
for SSIS Bulk Insert Task to perform the same behavior as you saw in your
Data Flow Task. They are essentially different. As a workaround, you can
continue using your Data Flow Task or specifying empty string as the
default values for your tables columns.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

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.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Ulysses,
Sorry for letting you wait a long time. I just got the confirmation from
the product team. The NULLs you saw in BULK INSERT is expected since the
default value is NULL. SSIS Data Flows have its own engine for transferring
and manipulating data and they do not use SQL Server for that. You cannot
simply draw a parallel between BULK INSERT and Flat File Source. BULK
INSERT loads flat files into defined SQL Server table. Flat File Source
pumps data into SSIS Data Flow buffers to be used by its downstream
components. The source does not know how the data it produces is going to
be used or where it is going to end up at (it could be table, it could be
Excel sheet, another file or something else). There is no T-SQL associated
with data flows. You may notice T-SQL statements issued by specific
components while reading or inserting data but they do not describe the
entire flow.

SSIS Data Flows do not have concept of default values so empty strings are
going to be loaded as they are. There is a property on the Flat File source
that will allow you to convert empty string into NULLs. The property name
is RetainNulls.

So regarding your concern, we can conclude that indeed it is not possible
for SSIS Bulk Insert Task to perform the same behavior as you saw in your
Data Flow Task. They are essentially different. As a workaround, you can
continue using your Data Flow Task or specifying empty string as the
default values for your tables columns.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

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.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Hi Ulysses,
Sorry for letting you wait a long time. I just got the confirmation from
the product team. The NULLs you saw in BULK INSERT is expected since the
default value is NULL. SSIS Data Flows have its own engine for transferring
and manipulating data and they do not use SQL Server for that. You cannot
simply draw a parallel between BULK INSERT and Flat File Source. BULK
INSERT loads flat files into defined SQL Server table. Flat File Source
pumps data into SSIS Data Flow buffers to be used by its downstream
components. The source does not know how the data it produces is going to
be used or where it is going to end up at (it could be table, it could be
Excel sheet, another file or something else). There is no T-SQL associated
with data flows. You may notice T-SQL statements issued by specific
components while reading or inserting data but they do not describe the
entire flow.

SSIS Data Flows do not have concept of default values so empty strings are
going to be loaded as they are. There is a property on the Flat File source
that will allow you to convert empty string into NULLs. The property name
is RetainNulls.

So regarding your concern, we can conclude that indeed it is not possible
for SSIS Bulk Insert Task to perform the same behavior as you saw in your
Data Flow Task. They are essentially different. As a workaround, you can
continue using your Data Flow Task or specifying empty string as the
default values for your tables columns.

If you have any other questions or concerns, please feel free to let me
know. Have a nice day!

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.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======