March 12, 2014 at 1:33 pm
Hello,
I have been given a CSV file which consist of about 10 rows (nothing major),
One of the columns is a Geography datatype which will basically be a road segment of google maps (latitude longitude etc)
I'm having a small problem trying to insert all ten rows into my table using the file import wizard it keeps saying column 6 (which is the Geography column) is an invalid DataType below is a snipped of the data im trying to import
3000,O16ATA57EK1AW,M6,NULL,291,NULL,0xE610000001040400000010516F7926774B40008C10CE079D07C0DD2C51291C774B4000FD9F7BF19907C002BC051214774B40006E2F698C9607C07F3BE4940F774B40001A4C63129407C001000000010000000001000000FFFFFFFF0000000002,1,113,0,0,2012-11-23 21:01:24.347,4,1,2012-04-19 00:00:00.000,2012-11-23 21:01:24.347,4,NULL,NULL,NULL,0
3000,O16ARRGPEKKTW,M6,NULL,1249,NULL,0xE610000001040B000000FE8FE4200E774B40006E34E0426807C01223C7BD0F774B4000801FDC2C6907C0A58C28B511774B4000920A28B76A07C00095CB2413774B4000A4F5BBAD6C07C0A6F8BFAD13774B400050C139D96D07C0A3F86CB513774B4000FC8C6FA66F07C0A8277A040D774B40008AE56EEA8007C0D7A3703D0A774B4000A696ADF58507C0429D31D908774B4000CBF3780C8C07C0B9A9E6F209774B4000F21F8E1D8F07C0F75D37F90A774B40001A4C5B2D9007C001000000010000000001000000FFFFFFFF0000000002,1,113,0,0,2012-11-23 21:07:01.460,4,1,2012-04-19 00:00:00.000,2012-11-23 21:04:03.793,4,NULL,NULL,NULL,0
split by the delimiter "," the geog column starts with 0x which im sure you new anyway being SQL Experts.
below is the error message i get
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 6" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Can any one help me or explain why I can import this data?
March 12, 2014 at 3:12 pm
Hi
Sorry to say this is going to be quite tricky.
Given that you are only dealing with 10 rows you could try the following:
1. Import the file into a staging table, setting the geography column as a text stream (DT_TEXT) for source and varchar(max) for target.
2. Create dynamic insert statements from the staging table to your target table.
SELECT
'INSERT INTO Target (..., GeogColumn, ...) VALUES (...,CAST(' + GeogText + 'as Geography), ...)'
FROM Staging
This should work as long as you Geographies aren't to large and I would not suggest doing this for big imports.
You may want to look at the way it is exported rather than trying to import this format.
The best way to export it to a CSV would be to convert the Geography to OGC text while exporting, GeogColumn.ToString(). When importing put this string into a varchar(max) and afterwards update the GeogColumn with Geography::STGeomFromText(ImpVarChar, Your SRID here)
Hope this helps
March 12, 2014 at 3:19 pm
I see, well I did come across an interesting post regarding the mappings which is here[/url] he's explaining the exact problem I'm having + he gave a solution, now I modified the mappings file i.e added the following to it
<!-- geometry -->
<dtm:DataTypeMapping >
<dtm:SourceDataType>
<dtm:DataTypeName>geometry</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
and
<!-- geography -->
<dtm:DataTypeMapping >
<dtm:SourceDataType>
<dtm:DataTypeName>geography</dtm:DataTypeName>
</dtm:SourceDataType>
<dtm:DestinationDataType>
<dtm:SimpleType>
<dtm:DataTypeName>DT_IMAGE</dtm:DataTypeName>
</dtm:SimpleType>
</dtm:DestinationDataType>
</dtm:DataTypeMapping>
bare in mind I'm only trying to import one record at the moment to see if I can get this working but sadly I get the following error
- Executing (Error)
Messages
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Column 6" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
Error 0xc020902a: Data Flow Task 1: The "output column "Column 6" (34)" failed because truncation occurred, and the truncation row disposition on "output column "Column 6" (34)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)
Error 0xc0202092: Data Flow Task 1: An error occurred while processing file "C:\Users\Scott\Desktop\test.txt" on data row 1.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - test_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
I've just been informed he will be sending me a csv file which will contain 1.5m + records which I will need to import as well
March 12, 2014 at 4:56 pm
I've come across a similar article here, but they both appear to be dealing with database to database.
You have a binary representation in a text file. When you try and load this as a DT_IMAGE you will get a DT_IMAGE is not supported, try DT_TEXT or DT_NTEXT.
Importing this as DT_TEXT into a varbinary, image or geography will end up with a truncation error.
If you import it as a varchar then try casting it as a binary/geography will cause it to change and fail.
As I mentioned before, if you can get the supplier to convert the geography to OGC Well Known Text while exporting, this will make the import process easier to manage.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply