By Rachel


2019-04-11 09:22:52 8 Comments

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 comments

@EzLo 2019-04-11 10:44:41

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

@Rachel 2019-04-15 08:47:23

What property expression do I need to select in the ForEach for the expression ?

@EzLo 2019-04-15 09:03:23

@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.

@Rachel 2019-04-15 09:40:11

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

@EzLo 2019-04-15 09:51:30

@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.

@Rachel 2019-04-15 13:23:12

It's an update statement,it updates the Submitted_Date column but at the moment update statement is setting the value to 01-01-1900 instead of null, so the expression is not changing anything at the moment. Also I added sql in the question where I do the select.

@EzLo 2019-04-15 13:34:42

@Rachel I've edited the answer for an update statement.

Related Questions

Sponsored Content

1 Answered Questions

Inserting to a table when everything is parameterized

1 Answered Questions

1 Answered Questions

[SOLVED] Arithmetic overflow on SELECT query

  • 2018-08-28 13:53:17
  • stubs
  • 99 View
  • 9 Score
  • 1 Answer
  • Tags:   sql-server

0 Answered Questions

Threadsafe/non-clobbering foreach file container for SSIS?

1 Answered Questions

[SOLVED] Investigating errors from strange query

1 Answered Questions

3 Answered Questions

[SOLVED] Force SQL Server to parse "DDMMYYYY"

1 Answered Questions

[SOLVED] SSIS - insert system date variable as a execute sql task parameter

3 Answered Questions

Sponsored Content