3

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

  • 1
    Can you include the error message you are getting? Are both the selected column and the foreach's variable of datetime type? – EzLo Apr 11 '19 at 9:26
  • @EzLo I have included the error message and yes it's set to datetime type for the foreach variable – Rachel Apr 11 '19 at 9:43
3

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
  • What property expression do I need to select in the ForEach for the expression ? – Rachel Apr 15 '19 at 8:47
  • @Rachel The expression should be inside whichever Task you are using inside the ForEach container when actually using this column. You won't be able to patch this on the ForEach settings. – EzLo Apr 15 '19 at 9:03
  • it doesn't work for me,I have set the Expression YEAR( @[User::dSubmitted_Date] ) == 1900 ? NULL(DT_DATE) : @[User::dSubmitted_Date] ,it doesn't change the value from 1900 to Null – Rachel Apr 15 '19 at 9:40
  • @Rachel You will also have to query the value with the ISNULL against the database and also review what the expression is actually replacing or updating (is it a new column on a DataFlow Task?). You can edit your question to include this data. – EzLo Apr 15 '19 at 9:51
  • 1
    @Rachel I've edited the answer for an update statement. – EzLo Apr 15 '19 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.