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
Go to Top of Page

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.
Go to Top of Page

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"
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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 ?
Go to Top of Page

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())
Go to Top of Page

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.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-26 : 20:24:09
Post your new derived column formula
Go to Top of Page

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?
Go to Top of Page

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....
Go to Top of Page

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 ?
Go to Top of Page

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?
Go to Top of Page

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())

Go to Top of Page
   

- Advertisement -