November 17, 2009 at 5:07 pm
Trying to load excel data to empty destination table using ssis.
data extracted from table has exactly similar data types as the destination table
STEP-1
tried loading excel data to destination table using excel source and ole db in ssis
ERROR- unicode and non unicode string data type error
{
ADDITIONAL INFORMATION:
Error at Data Flow Task [OLE DB Destination [9]]: Column "First_Name" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [9]]: Column "Last_Name" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [OLE DB Destination [9]]: Column "E_Mail" cannot convert between unicode and non-unicode string data types.
Error at Data Flow Task [SSIS.Pipeline]: "component "OLE DB Destination" (9)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
}
STEP-2
used data conversion between excel file source and ole db destination
ERROR- properties does not match-and many more errors
{
[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
[OLE DB Destination [9]] Warning: Truncation may occur due to inserting data from data flow column "Copy of First_Name" with a length of 255 to database column "First_Name" with a length of 20.
[OLE DB Destination [9]] Warning: Truncation may occur due to inserting data from data flow column "Copy of Last_Name" with a length of 255 to database column "Last_Name" with a length of 20.
SIMILAR ERROR MESSAGE TO OTHER COLUMNS
}[highlight=#ffff11][/highlight]
step-3 tried to change the excel file source "show advanced editor"/input and output properties
still dint work out.
can any one give me detailed process how to laod excel data to any table using SSIS.
If you guys have any links please provide me.
November 18, 2009 at 8:12 am
You should be successful with the data conversion transform by converting the column from unicode to non-unicode.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2009 at 6:45 pm
I TRIED BUT STILL I AM GETTING THIS ERROR
STEP-2
used data conversion between excel file source and ole db destination
ERROR- properties does not match-and many more errors
{
[Connection manager "Excel Connection Manager"] Error: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
[OLE DB Destination [9]] Warning: Truncation may occur due to inserting data from data flow column "Copy of First_Name" with a length of 255 to database column "First_Name" with a length of 20.
[OLE DB Destination [9]] Warning: Truncation may occur due to inserting data from data flow column "Copy of Last_Name" with a length of 255 to database column "Last_Name" with a length of 20.
SIMILAR ERROR MESSAGE TO OTHER COLUMNS
DO YOU THINK I HAVE PROB WITH 64-BIT VERSION
Thanks,
February 4, 2014 at 3:52 pm
I've just spent two days fighting something similar importing a text file.
I found that adding a data conversion component did work, but turned a simple import of 1.6 million rows into an overnight job.
The way I got around it was to right click on the source and choose the advanced editor. On the rightmost tab you can select the the precise input type and size you want, DT_STR or DT_WSTR. Aligning those advanced options in the source and destination did the trick for me.
One caveat. When I changed from DT_STR to DT_WSTR it reset the lengths to 50 so I had to laboriously set them all back to what I wanted. Horrendous though this was it did turn the import back into a 2 minute job.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply