Convert GETDATE() to DATETIMEOFFSET(0) within the Data Flow

Question

Convert GETDATE() to DATETIMEOFFSET(0) within the Data Flow

aarionsql

SSCrazy

Points: 2392
More actions
March 18, 2015 at 9:46 am

#150871

Hi All,
If any one has any ideas on converting GETDATE() to a DATETIMEOFFSET(0) within the Data Flow.
I am getting truncation / conversion errors when I am trying to convert the GETDATE() function within the 'Derived Column' or 'Data Conversion' task.
Example:
In a 'Derived Column' task I tried: (DT_DBTIMESTAMPOFFSET,0)GETDATE().
I would be interested in finding outany method of doing this in the Data Flow, without doing this in SQL?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246986 More actions · Answer 1

aarionsql (3/18/2015)
Hi All,
If any one has any ideas on converting GETDATE() to a DATETIMEOFFSET(0) within the Data Flow.
I am getting truncation / conversion errors when I am trying to convert the GETDATE() function within the 'Derived Column' or 'Data Conversion' task.
Example:
In a 'Derived Column' task I tried: (DT_DBTIMESTAMPOFFSET,0)GETDATE().
I would be interested in finding outany method of doing this in the Data Flow, without doing this in SQL?
Thank you

Out of interest, can you provide a bit of background explaining why you want to do this?

Do you really want to recalculate it for every row in your dataflow?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

aarionsql SSCrazy Points: 2392 More actions · Answer 2

Hi Phil,

I am building a SCD 2 package and there is date field for current records and expired records where I have traditionally used a GETDATE() funtion to populate them in a 'Derived Column' task.

However, the destination is DATETIMEOFFSET(0). So I am trying to convert the GETDATE() or GETUTCDATE() function to a DATETIMEOFFSET(0) and getting the truncation errors.

Thank you

Phil Parkin SSC Guru Points: 246986 More actions · Answer 3

aarionsql (3/18/2015)
Hi Phil,
I am building a SCD 2 package and there is date field for current records and expired records where I have traditionally used a GETDATE() funtion to populate them in a 'Derived Column' task.
However, the destination is DATETIMEOFFSET(0). So I am trying to convert the GETDATE() or GETUTCDATE() function to a DATETIMEOFFSET(0) and getting the truncation errors.
Thank you

Regarding the implementation of SCDs in SSIS, you may like to read this[/url] article. There are different ways of doing it and some are very slow.

Regarding your question, what fun! Here is how to get rid of that truncation error.

Open the advanced edit dialog for your derived column. Open Input and Output properties and select your derived column in the tree view (Output columns). Find the TruncationRowDisposition property and set it to Ignore failure, then try again.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

aarionsql SSCrazy Points: 2392 More actions · Answer 4

Thank you for the suggestion. That does not work as the package fails when it tries to INSERT the record

aarionsql SSCrazy Points: 2392 More actions · Answer 5

I have implemented the following code in the data flow task:

(DT_DBTIMESTAMPOFFSET,0)DATEADD("ss",-DATEPART("Ms",GETDATE()),GETDATE())

And appled the Error on truncation to Ignore.

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

(Edit)

SQL SERVER:

SELECT CAST(GETDATE() AS DATETIMEOFFSET(0));

SSIS:

(DT_DBTIMESTAMPOFFSET,0)DATEADD("ss",-DATEPART("ss",GETDATE()),GETDATE())

Both of the above seem to give the same result but SSIS still gives truncation error messages and will fail the package.

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

Not the best solution but it works.

I iwll try and check for a better solution.

Phil Parkin SSC Guru Points: 246986 More actions · Answer 6

aarionsql (3/18/2015)
Thank you for the suggestion. That does not work as the package fails when it tries to INSERT the record

I've read your posts and I don't understand 100% what has happened:-)

Do you now have a working solution?

In future, if you are getting warnings or errors, please post them here. Otherwise it's very difficult to offer constructive help when all that you are telling us is that an insert 'fails'.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel