SSIS: Error converting to date

Question

SSIS: Error converting to date

river1

SSC-Insane

Points: 24302
More actions
December 4, 2017 at 3:51 am

#341053

Hello:
I have a variable that uses the following colamnd:
(DT_DBTIMESTAMP2, 0) ((DT_STR, 10, 1252) (SUBSTRING(LEFT(RIGHT(@[$Package::FilePath], 12), 8), 1, 4) + "-" + SUBSTRING(LEFT(RIGHT(@[$Package::FilePath], 12), 8), 5, 2) +"-" + SUBSTRING(LEFT(RIGHT(@[$Package::FilePath], 12), 8), 7, 2)))
I get the error: attempting to convert dt_wstr to DBtimestamp2 error.
Can you please help me understand why?
Thank you

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

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

river1 SSC-Insane Points: 24302 More actions · Answer 1

Also, if I change to this: (DT_DATE) instead of Timestamp2, then it evaluates to : 04.12.2017 00:00:00

Previously I was executing the package as (DT_DATE) instead of using (DT_DBTIMESTAMP2, 0) which runs well in my computer but in the server I get the same error as above. This is why I am trying to see if with (DT_DBTIMESTAMP2, 0) I will be able to execute it.

Can you please help?

Thom A SSC Guru Points: 99126 More actions · Answer 2

Without knowing the value of @[$Package::FilePath] all we can do is guess.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

river1 SSC-Insane Points: 24302 More actions · Answer 3

river1

SSC-Insane

Points: 24302

December 4, 2017 at 5:44 am

#1970484

Sorry. It's : Multifunds_Export_20171204.txt

DimUser Ten Centuries Points: 1043 More actions · Answer 4

I'd imagine it's to do with the length of your DT_STR conversion.
I believe the string format needs to match the data type you want to convert to?
10 characters gives you the date format, which is why DT_DATE works.
DT_DBTIMESTAMP2 needs hours. minutes, seconds and fractional seconds, which your string doesn't currently have.

river1 SSC-Insane Points: 24302 More actions · Answer 5

DimPerson - Monday, December 4, 2017 6:17 AM
I'd imagine it's to do with the length of your DT_STR conversion.
I believe the string format needs to match the data type you want to convert to?
10 characters gives you the date format, which is why DT_DATE works.
DT_DBTIMESTAMP2 needs hours. minutes, seconds and fractional seconds, which your string doesn't currently have.

I don't know. From my computer (when I run the package locally, it works) only when I deploy to the SSISDB and run it from there it fails....

I anot sure it is related with that.

Even If I take the timestamp 2 and use just the Dt_Date, still it runs from my computer but not from the SSISDB