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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Seconds vs DateTime SSIS Question
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

224 Posts

Posted - 03/26/2015 :  10:23:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/26/2015 :  10:30:44  Show Profile  Reply with Quote
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 - 03/26/2015 :  11:30:47  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/26/2015 :  11:53:24  Show Profile  Reply with Quote
what is the defined length of the derived column?

also, what is "My Time Field"

Edited by - gbritton on 03/26/2015 11:57:09
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/26/2015 :  12:20:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/26/2015 :  12:37:13  Show Profile  Reply with Quote
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 - 03/26/2015 :  13:22:57  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/26/2015 :  13:51:06  Show Profile  Reply with Quote
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 - 03/26/2015 :  19:52:20  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/26/2015 :  20:24:09  Show Profile  Reply with Quote
Post your new derived column formula
Go to Top of Page

tooba
Posting Yak Master

224 Posts

Posted - 03/26/2015 :  22:56:03  Show Profile  Reply with Quote
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 - 03/26/2015 :  23:50:31  Show Profile  Reply with Quote
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 - 03/27/2015 :  01:06:51  Show Profile  Reply with Quote
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 - 03/27/2015 :  01:46:28  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 03/27/2015 :  08:33:02  Show Profile  Reply with Quote
See my previous post. in your DC transform:

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000