June 30, 2010 at 9:39 am
ole db source and oledb destination,for data convertion errors i just added derived colum transformation in between.but still getting the error OLEDB Provider using The OLEDB Adapter cannot convert between DT_DBTimestamp to DT_I4.PLZ HELP ME WITH THIS.
Thanks
June 30, 2010 at 12:15 pm
I do not think that you can use the SSIS type conversion functions or the Data Conversion component to convert these 2 types. If you are storing a datetime as an integer in your destination, what format will it be, for example is it 20100630?
June 30, 2010 at 12:33 pm
Souce formate is 2010-07-01 00:00:00.000 and destination formate is 20080701
June 30, 2010 at 12:47 pm
Try this:
(year(yourDatetime) * 10000)+
(month(yourDatetime) * 100)+
Day(yourDatetime)
see http://www.bidn.com/blogs/MikeDavis/ssis/236/convert-date-to-string-or-integer-in-ssis-expressions
June 30, 2010 at 1:06 pm
i have somany records in this table.do i need to manually change all.how can use this expression in deriverd column
June 30, 2010 at 1:24 pm
1 - give the derived column a new name
2 - use this expression:
(YEAR(your_Datetime_field) * 10000) + (MONTH(your_Datetime_field) * 100) + DAY(your_Datetime_field)
3 set the data type = four-byte signed integer [DT_I4]
4 map this new column to your destination
June 30, 2010 at 3:03 pm
I tried to use this expression.i have other question.in my source table policy number column is nvarchar(80) and in my destination table that is varchar(6),what i need to use here.it giving me truncation error.
Thanks
July 1, 2010 at 3:01 am
You have to options to reduce the column length from nvarchar(80) to nvarchar(6):
* Include a data conversion component. Select your column and set the length to 6.
* Or, go to the advanced editing screen of your source, go to output columns, select your columnd and then set the appropriate length in the properties window.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 1, 2010 at 10:56 am
When i changed in advance editor of Source i m getting error as [OLE DB Source [1]] Error: The "output column "id" (6493)" failed because truncation occurred, and the truncation row disposition on "output column "id" (6493)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
Thanks
July 1, 2010 at 2:22 pm
That simply means that the column with ID 6493 contains data that is larger than the length you specified.
You can either solve the issue by making sure that your source data is compliant to the constraints you set up, or you can configure the source to ignore the error.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 4, 2016 at 8:24 am
[OLE DB Command [66]] Error: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_DBTIMESTAMP" and "DT_I4"
May 4, 2016 at 12:20 pm
It's a matter of breaking it down into component parts.
You need to get the year, month, and day out of the date value.
Then you need to combine those into a single value.
Finally you need to convert it to an integer (since trying to concatenate the output of a DATEPART results in math).
The additional complication is that month and day outputs do not have the leading zeros you want.
Add a Derived Column transformation and try this expression. Of course replace the YourDateField with your field name.
(DT_I4)((DT_WSTR,4)YEAR(YourDateField) + (LEN((DT_WSTR,2)MONTH(YourDateField)) == 1 ? "0" + (DT_WSTR,2)MONTH(YourDateField) : (DT_WSTR,2)MONTH(YourDateField)) + (LEN((DT_WSTR,2)MONTH(YourDateField)) == 1 ? "0" + (DT_WSTR,2)DAY(YourDateField) : (DT_WSTR,2)DAY(YourDateField)))
Edit - Just realized this was threadomancy on the previous post but it's still valid as a solution to a general problem.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply