September 28, 2012 at 12:50 am
Hi,
I wanted to take the back up of a single table and put that table on other server. So i followed the following step first of all
Now i got the create script for the table. I run this script on destination server.
Now i got back to the source server and through export wizard exported this table in excel format and then through import fromat tried inserting this in destination server but this gave me error as
Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "ItemId" with a length of 255 to database column "ItemId" with a length of 50.
(SQL Server Import and Export Wizard)
Error 0xc0202049: Data Flow Task 1: Failure inserting into the read-only column "ID".
(SQL Server Import and Export Wizard)
Error 0xc0202045: Data Flow Task 1: Column metadata validation failed.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task 1: "component "Destination - TAT" (35)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task 1: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task 1: There were errors during task validation.
(SQL Server Import and Export Wizard)
.
Now how is it possible to change the datatype of excel from text to the one specified in the above error message
September 28, 2012 at 12:58 am
The problem with exporting to excel is that the text is assumed to be varchar/nvarchar 255, as excel doenst hold the original field lengths.
Why not just cut out the middle man (excel) and Export it directly to the destination database?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 28, 2012 at 1:03 am
Jason-299789 (9/28/2012)
The problem with exporting to excel is that the text is assumed to be varchar/nvarchar 255, as excel doenst hold the original field lengths.Why not just cut out the middle man (excel) and Export it directly to the destination database?
Hi Jason i tried that also but the destination server does not have right to connect remotely. Any other suggestion. I even tried converting it into flat file and then importing it but that is also not working.
September 28, 2012 at 1:15 am
Ok, you will need to go through and redefine the Excel column widths and datatypes to match the destination table.
this is done when you are on the Select Source Tables & Views part of the import wizard, in the bottom right theres an Edit Mappings option so you can alter the datatype and Size to match the destination.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
September 28, 2012 at 1:25 am
Jason-299789 (9/28/2012)
Ok, you will need to go through and redefine the Excel column widths and datatypes to match the destination table.this is done when you are on the Select Source Tables & Views part of the import wizard, in the bottom right theres an Edit Mappings option so you can alter the datatype and Size to match the destination.
On the Select Source Tables & View screen i am not able to edit the datatype. i.e there is no drop down showed to me. Any idea why this is?
September 28, 2012 at 1:38 am
No idea, did you click on the edit mapping button, which should bring up a list of the source columns, you will need to select the data type for the drop down to appear, and just click on the size then alter it.
without seeing your screen I cant say why you are unable to edit the fields.
If that fails, you could convert the Excel to a CSV file (Save as MS-dos CSV) and then import from that, except that you will need to set up the fields when you define the connection.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply