Sql-server – SSIS : Null value for date keeps failing

sql serverssisvisual studio

I have a sequence container and in that sequence container I have select that is passing through data into a foreach loop container. The problem arises with one of the columns that's a date datatype. When there is a date in that column it works fine, but when it's a null it fails. Is there anyway around this issue with null values?

enter image description here

This is the column

![enter image description here

This is the error message below :
enter image description here

Below is the sql statement where I change the date from null to a date
SELECT
cast(ISNULL([Submitted_Date],'01/01/1900') as datetime) as [Submitted_Date]
FROM [dbo].[Application_Tbl]
WHERE
Application_Form_Id IN (12345)
processed = 0

Best Answer

Unfortunately, SSIS variables can't hold NULL values. NULL is allowed in data flow's columns but not on variables, this is a limitation of the underlying COM objects used by SSIS.

You will have to work-around this by supplying a default or sentinel value on your query, and later on check on this value to correctly assign a NULL. You can change your query to supply a non-null value of the correct type, for example:

SELECT 
    T.Column1,
    T.Column2,
    DateColumn = ISNULL(T.DateColumn, '1900-01-01')
FROM 
    YourTable AS T

And then whenever you are referencing this DateColumn inside your ForEach, make an expression to check against this value and replace with NULL.

YEAR( @[User::DateColumn]  ) == 1900 ?  NULL(DT_DATE) : @[User::DateColumn]

If you are issuing an UPDATE via OLE Command statement with the ForEach's variables then your current update should be something like:

UPDATE T SET 
    col1 = ?, 
    col2 = ? 
FROM
    yourTableToUpdate T
WHERE
    primaryKeyID = ?

Or maybe an SP call:

EXEC dbo.UpdateSomeTable
    @ID = ?,
    @Col1 = ?,
    @Col2 = ?

You can use plain SQL to replace the value back to the NULL. I'd also recommend assigning the values to variables first then using the variables in any place you want inside the SQL. This way it's easier to see the proper order of the parameter assignation and you can also repeat them without trouble:

DECLARE @col1 DATETIME = ?
DECLARE @col2 VARCHAR(100) = ?
DECLARE @primaryKeyID INT = ?

SET @col1 = CASE WHEN YEAR(@col1) <> 1900 THEN @col1 END

UPDATE T SET 
    col1 = @col1,
    col2 = @col2 
FROM
    yourTableToUpdate T
WHERE
    primaryKeyID = @primaryKeyID

Or:

DECLARE @col1 DATETIME = ?
DECLARE @col2 VARCHAR(100) = ?
DECLARE @primaryKeyID INT = ?

SET @col1 = CASE WHEN YEAR(@col1) <> 1900 THEN @col1 END

EXEC dbo.UpdateSomeTable
    @ID = @primaryKeyID,
    @Col1 = @col1,
    @Col2 = @col2
Related Question