blank dates

Question

Post reply

blank dates

jessica-314018

SSC-Addicted

Points: 470
More actions
April 17, 2008 at 10:17 am

#154290

Hi,
I want to be able to insert a blank date into a table but my forEach loop throws an error

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How 'bout posting the error so we can help you?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

jessica-314018 SSC-Addicted Points: 470 More actions · Answer 2

Hi,

Apologies, I should have been more clear.

My process is as follow:

I have a step which gets data from a DB2 database and populates a 'Recordset Desination', then I use the 'For Each Loop' and populate a SQL table. At the For Each level, I get an error

'Error: The type of the value being assigned to variable "User::enddate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.'

I noticed if i create a 'Derived column' and use the isnull function for this date, I don't get the error. I do want to populate the SQL table with nulls if there is no date.

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 3

Hi Jessica,

I'm not sure what you're doing in the ForEach loop but if you're using a SQL task, then you can use a variable as the SQL command. Then what you can do is make use of SQL server's NULLIF() function.

So in the dataflow for your recordset you would derive EndDate column as something like "ISNULL(EndDate) ? "1900-01-01" : EndDate" and then evaluate your SQL statement variable with an expression - something like "INSERT dbo.TableName(EndDate) VALUES ( NULLIF('" + (DT_WSTR,20)@EndDate + "','1900-01-01'))"

I think this is what you're after, but might have missed what you're trying to do...

HTH

Kindest Regards,

Frank Bazan

jessica-314018 SSC-Addicted Points: 470 More actions · Answer 4

Hi Frank,

That is exactly what I'm trying to do. In my derived column however, I

ISNULL( ENDDATE) ? "1900-01-01" : ENDDATE

I get the error:

The data types "DT_WSTR" and "DT_DBTIMESTAMP" are incompaitble for the conditional operator.

It is not liking the string, must I do some sort of casting in the derived column?

thanks

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 5

You're right - it needs a typecast (DT_DBTIME)"1900-01-01" should do it.

PS: Just editing to add one other point - make sure that your sql statement uses the same dateformat as the one coming from your derived column. i.e. NULLIF('01/01/1900','1900-01-01 00:00') might not return a NULL. You can see the actual statement by adding a breakpoint on your SQL task and checking the locals window to view the content of the SQL statement variable.

HTH

Kindest Regards,

Frank Bazan

SSIS\SSRS\SSAS SSCertifiable Points: 6038 More actions · Answer 6

I am not sure exactly i understood everything here.

But, few days back i used to have the same problem with null columns that was a dbtimestamp. My case was i had a flat file where columns are uneven. so a script component handles this part. now table destination at sql was smalldatetime, aso any flat file column that was empty used to throw error. So, used a derived column and added following expression...----------------------

Col3 == "" || Col3 == "0" || ISNULL(Col3) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)Col3

-------------------------------------------

Now this works anything that is empty string will be NULL

One thing for sure, smalldatatime data types acts weired in SQL 2005

I hope this might help you,

Thanks,

Frank Bazan SSCertifiable Points: 5378 More actions · Answer 7

I think Jessica's issue is that you cannot assign a NULL value to SSIS variable. So what we are doing is coalescing in the dataflow to a known value that won't ever be in the source, and then using a SQL NULLIF() function testing for that value and turning it back into a NULL if it's found before inserting into the table.

Kindest Regards,

Frank Bazan

jessica-314018 SSC-Addicted Points: 470 More actions · Answer 8

Thanks for the suggestion all, the NULLIF worked perfectly