October 15, 2009 at 3:35 pm
Is their a trick to importing a fixed field with file using BCP or Bulk insert. I got the process to read the file but it only gives me one column back and they all are null.
Do not know what I am doing incorrect. All the examples I could find seem to import information fine.
October 15, 2009 at 3:52 pm
October 15, 2009 at 4:07 pm
OK thanks
October 15, 2009 at 4:32 pm
By the way, I was not trying to be short. Format files are what you want to use. Read up on them and give using them a stab. Post back to this thread with questions or problems. If you are able to get it working without additional help, post back so we know you were taken care of.
Thanks,
John
October 20, 2009 at 9:35 am
Hi.
Maybe you can refer to the url below:
October 20, 2009 at 12:52 pm
The problem still exists and it is all because it has unicode characters and Sql Server does not know how to deal with them. I found some code to see what the the funny character was at the end of each line and it translates to a line feed, but SQL can not read it from bulk insert. I tried replacing that funny unicode character with a pipe and then Bulk insert worked. The problem is that these files are very large the smaller file has 200,000 records in it and when I tried the replace function on the character it was going to take 10 plus minutes and the process has to run daily. (not an answer).
I am looking into SSIS, and I created a package for one of the two large files and I think that will work great. The other file has a long text field defined as NVarchar(Max), in the conversion process it failed to do the import because of a truncation error. Even if I tell the import to ignore the truncation error it still fails. If you could grant assistance on my SSIS problem that would be good.
October 23, 2009 at 9:27 am
SSIS is very data type dependant. When importing from flat files, you always want to ensure that the source data types match your destination data types, particularly for string types. In the case of flat files, this usually means using a Data Conversion transformation.
If your destination column is a nvarchar(max), you'll want to convert the source column to a DT_NTEXT.
October 23, 2009 at 11:00 am
The truncation occurs because data that you are trying to fit into a column are longer than this column. Check mappings in the SSIS, especially data types. Check what is the data type of the target column in your destination table. If you set component to ignore the failure you'll end up with NULLs in the column. Probably not what you want.
Regards
Piotr
...and your only reply is slàinte mhath
October 26, 2009 at 4:02 pm
I appreciate the support and the ntext solution worked.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply