Seconds vs DateTime SSIS Question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Seconds vs DateTime SSIS Question

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 10:23:54

Hi Guys,

Quick question, my source has seconds, (Sample data below)

ID,Seconds
1,60
2,120
3,80

and my destination field where I want to map is sql table and field has data type datetime. I am getting error truncation error through SSIS.

Please advise.

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 10:30:44

Please post the query you are using that causes the error and the complete error message

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 11:30:47

I am using SSIS, here is my Expression in Derived Column

(DT_WSTR,4)YEAR(GETDATE()) + ":" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + ":" + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + ":00:00" + ":" + My Time Field

Error:- Truncation Error...

Please let me know, if you need more info.

Thank You.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 11:53:24

what is the defined length of the derived column?

also, what is "My Time Field"

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 12:20:26

In Derived Column Value is String and define length is 20
My Time Field is the time i am receiving in my source file

ID,Seconds (My Time Field)
1,60
2,120
3,80

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 12:37:13

Since you don't have any type casting for the seconds column, I assume it is a string, right?

I simulated your set up with an OLEDB source transformation with a SQL statement:


select 1 as ID, cast('60' as varchar(3)) as seconds
union all
select 2,'120' 
union all
select 3,'80'

Then copied and pasted your DC transform and added the seconds column on the end. Then I ran my test package successfully.

So there must be something different about yours. e.g. what are the datatypes of the input?

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 13:22:57

are you destination table has datetime data types?

Yes It is string, I am getting when I am trying to concatenating with different date (i.e default date). Do I need to concatenating with different date or just map this field to destination file and it will insert by default something 1900 ?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 13:51:06

OIC, I thought your problem was with the DC transform. Now I see what the issues are. First off your transform is not generating the correct format. you should have:


(DT_WSTR,4)YEAR(GETDATE()) + "-" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + "-" + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + " " + "00:00" + ":" + seconds

with hyphens between year-month-day then a space then the hh:mm:ss

Second, the seconds cannot be more than 59 (at least, not on my wristwatch!) and all sample rows are > 59 seconds. Maybe a simpler DC will do it:


DATEADD("ss", (DT_I4) [seconds],GETDATE())

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 19:52:20

gbritton, Thanks for your help, I am getting the same error, Just to let you know, My destination field data type is datetime.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 20:24:09

Post your new derived column formula

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 22:56:03

Here is my Expression...
(DT_WSTR,4)YEAR(GETDATE()) + "-" + RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2) + "-" + RIGHT("0" + (DT_WSTR,2)DAY(GETDATE()),2) + " " + "00:00" + ":" + seconds

FYI, Seconds is my source field and source data type is string and Destination field is Datetime

Am i missing something?

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-26 : 23:50:31

time
2015-03-26 00:00:60
2015-03-26 00:00:30

Here is the data I am seeing after I used in Expression through Data Preview, However I am getting error in Destination. Please guide me what I doing wrong....

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-27 : 01:06:51

I think I got it where is the problem is, My source has
60,30,120,49 seconds that's why i am getting error. How can i do if there is 60 sec change it to 1 minute ?

tooba
Posting Yak Master

224 Posts

Posted - 2015-03-27 : 01:46:28

This SQL is good what I am looking..
convert(varchar(8),dateadd(s,TIME,0),108)

Note:- Time is my Source File

Could you please help me, how I can convert above t-sql code in SSIS Expression?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 08:33:02

See my previous post. in your DC transform:

DATEADD("ss", (DT_I4) [seconds],GETDATE())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources