2

I have an SSIS package which transfers data from SAS to SQL Server. I'm creating a derived column but cannot get the REPLACENULL feature to work. I am receiving the error

"Invalid character value for cast specification"

which I am sure is because of NULL values in the source. Here is my current derived column expression:

REPLACENULL(DATEADD("d",(DT_I8)AuthEndDate,(DT_DATE)"1960-01-01"),0)

The REPLACENULL function isn't working here. Any way that this can be done? I am using SSIS 2008. Thanks.

Hadi
  • 36,233
  • 13
  • 65
  • 124
jerry
  • 129
  • 1
  • 3
  • 10
  • First off, `REPLACENULL()` needs to be around the source column of `AuthEndDate` (not the entire expression), but I'm also pretty sure you cannot convert a date to a bigint (assuming AuthEndDate is in fact a date). What are you trying to do here exactly? – Dave C Jan 09 '18 at 17:49
  • Tried that and still getting the error. The reason I'm converting the date as is is because the data is coming from SAS. It transfers perfectly when I try it on a column without NULL. The DataType for the column is [DT_STR]. – jerry Jan 09 '18 at 17:52
  • OK and if AuthEndDate is in fact NULL, what value do you want downstream? – Dave C Jan 09 '18 at 17:56
  • I would like it to just be blank or a 0 – jerry Jan 09 '18 at 19:07

3 Answers3

1

Since you want blank/0 to flow downstream you need an if, not a replacenull()

Please note blank/0 in SQL Server writes as 1900-01-01, blank/0 are not valid date values, so it defaults to this. The only other option is NULL.

This should work if the destination is a datetime column:

ISNULL(AuthEndDate) ? (DT_DATE)0 : DATEADD("d",(DT_I8)AuthEndDate,(DT_DATE)"1960-01-01")

If you opt to write NULL instead of 1900-01-01, you can do this:

ISNULL(AuthEndDate) ? NULL(DT_DATE) : DATEADD("d",(DT_I8)AuthEndDate,(DT_DATE)"1960-01-01")
Dave C
  • 7,272
  • 1
  • 19
  • 30
1

You must use one of the following expressions:

DATEADD("d",(DT_I8)REPLACENULL([AuthEndDate],0),(DT_DATE)"1960-01-01")

OR

ISNULL([AuthEndDate]) ? (DT_DATE)"1960-01-01" : DATEADD("d",(DT_I8)AuthEndDate,(DT_DATE)"1960-01-01")

Note that you cannot use a logic that can return multiple datatypes, both cases must return on datatype (DT_DATE)

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

If the source is returning NULLs, then try a REPLACENULL at AuthEndDate rather than after the calculations. The statement you have will try to do calculations with NULLs which is never good.

So...

REPLACENULL(DATEADD("d",REPLACENULL((DT_I8)AuthEndDate,0),(DT_DATE)"1960-01-01"),0)

Edit - replacenull and then apply conversion:

DATEADD("d",(DT_I8)REPLACENULL(AuthEndDate,0),(DT_DATE)"1960-01-01")

You won't need the first REPLACENULL anymore since we now handle it before the calculations.

Therefore:

DATEADD("d",REPLACENULL((DT_I8)AuthEndDate,0),(DT_DATE)"1960-01-01")

Edit: You can change the 0 to whatever you want the NULL to become, 0 was a placeholder.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Kevin Nguyen
  • 11
  • 3