Bug in DTS Designer?
Jonathan Levine
I'm trying to import a Unicode (UTF-16) file. The file has pretty long
lines with tab delimiters between columns and dos-style line termination
(CR-LF). It has 100 rows.
If I do the following:
1. Open the DTS Package editor (in Enterprise Manager),
2. Drag out a Text File (Source) object,
3. Set the File name to my UTF-16 file
4. Click Properties
5. Select Delimited, Unicode, {CR}{LF} Row delimiter, no text qualifier
6. Click next
then I OBSERVE:
1. An error message that says "Format error detected in rows 2, 12, 14, 15,
17, 20, 24, 25, 26, 27, 34, 37, 39, 41, 42, 54, 55, 65, 84, 90
2. The "Specify Column Delimiter" is set to Tab (this is correct). Lines 2,
12, 14, 15, 17, 20, 24, 25, 26, 27, 34, 37, 39, 41, 42, 54, 55, 65, 84, 90
and 101 - 200 are blank.
If I then hook this up (via Transform Data Task) and import, it appears to
import correctly.
If I go back to Text File Connection Properties/Column delimiter, I get the
same error.
WEIRDNESS: If I use DTS Wizard to Import the same file and save the package,
then go in and edit the package created by DTS Wizard, I can edit the Text
File object with no problem (that is, I can go to Text File Connection
Properties/Column Delimiter and I get no error).
Has anyone else seen this? Known bug?
Thanks,
Jonathan
http://kerblog.com/earlyedition/archive/2004/09/21/175.aspx
Mike Anderson
data can be dirty causing this error.
>.
>
Peter A. Schott
That fixed some DTS problems for me.
-Pete
Jonathan Levine
> Have you tired looking at the file in hex? Sometimes the
> data can be dirty causing this error.
>
Thanks for the hint, Mike.
I don't think the data is dirty, because it imports cleanly and because I
don't get the same error when editing a package created by DTS Wizard. It
seems to be a problem only when creating the connection with the DTS Package
Editor.
Regards,
Jonathan
Jonathan Levine
"Peter A. Schott" wrote:
> Have you applied the post-SP3 hotfix that would take you up to version 818?
> That fixed some DTS problems for me.
>
Thanks for the pointer. Do you remember which hotfix? I'm already at
version 818.
Regards,
Jonathan
Peter A. Schott
but nothing officially available that I'm aware of.
Did you verify that your file is correct? I had issues with NUL entries in a
UNIX file I was trying to import. I used a program called "Notepad2" from
www.flos-freeware.ch to view the file and see all of the details.
Had another issue (different file) where I was getting CRCRLF as a row
delimiter or intermittent CR and CRLF. That caused some issues as well. I'd
recommend checking out the file in more detail to verify that everything's
correct. Notepad doesn't quite display everything. This may give you another
hint.
-Pete
Jonathan Levine
Thanks for your reply.
"Peter A. Schott" wrote:
> Did you verify that your file is correct? I had issues with NUL entries in a
> UNIX file I was trying to import. I used a program called "Notepad2" from
> www.flos-freeware.ch to view the file and see all of the details.
The file is definitely correct. It imports fine when I run the package.
The only problem in the UI for the Text File object.
-- Jonathan
Peter A. Schott
know that the hotfix fixed some DTS bugs. If that doesn't help, I'm afraid
I'll have to direct this to the MS people or MS support.
-Pete
Mingqing Cheng [MSFT]
Thanks for your post.
Based on my scope, this issue is similar to one known issue of us. However
the troubleshooting steps might be so complex that not suitable to do it in
the newsgroup. I would like to recommand you open a case with our Microsoft
Product Support, it will be a free case if it is confirmed to be an known
issue.
Unfortuantely, based on my test, it was not fixed in sp3a or 818, which is
to say no hotfix available now. Additionaly, I have tested it in SQL 2005
(Yukon) Beta 1 and found it was fixed. So if this has much business impact
for you, you can contact Microsoft Product Support directly to discuss
additional support options you may have available, by contacting us at
1-(800)936-5800 or by choosing one of the options listed at
http://support.microsoft.com/default.aspx?scid=sz;en-us;top.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---------------------------------------------------------------
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Jonathan Levine
Thanks very much for your reply. I will think about contacting PSS.
Regards,
Jonathan
Mingqing Cheng [MSFT]
Thanks for your prompt updates letting me know the status of this issue.
Thank you for your patience and understanding.
Tea C
of characters in one of the columns in the tab-delimited source data file.
ERROR:
I have set-up a Tranform Data Task with source pointing to a tab delimited
text file. When I use the "Preview" button on Source Tab of the Transform
Data Task properties page, I receive an error, "Format error detected in rows
32, 144.".
OBSERVATION:
After troubleshooting the problem, I have determined that this error only
occurs with a column in the source file that has exactly 255 or 256
charaters (that I have mapped to ntext column in the destination (SQL Server
table)). If I alter the data value in the "offending" column in rows 32 and
144, by deleting or adding a character or more to the source file's column,
the error goes away.
Interestingly, the task, when executed with "execute step" or the entire DTS
package itself, works just fine. It's the problem with the "preview" button
that sent me on a wild goose chase. I am happy the task itself executes just
fine.
Tea C