 | |
DTS Error Using Text File as Data Source
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
DTS Error Using Text File as Data Source -
07-16-2003
, 08:00 AM
#2
| |||
| |||
|
|
I need to find a way to get around a DTS error produced when I try using a certain text file as a data source. For a screenshot of the error, pls view the following link: http://www.danallen.com/299-ReleaseA...oblem/DTSError. |
Quote:
| Links to both good and bad data samples follow this explanation of the problem. The BAD data file contains a record that comes to me as one of 50,000 records. The problem with the data is that the name GERT is enclosed in two sets of quotation marks, leading to a DTS error when I try to use the file as a data source. STEPS TO REPRODUCE THE PROBLEM 1. Save the BAD data file to a locally-available drive. Link to bad data file below. 2. In Enterprise Manager, navigate to a node labeled "Data Transformation Services". 3. Right-click on Data Transformation Services. 4. Select New Package 5. Inside DTS Designer (which is what opens), in the Connection tool bar, click on the icon for Text File (source). 6. In the dialog box, next to the File name field, click on the box with 3 dots. 7. Navigate to the BAD data file. Select that file as the data source. Click Open. Click OK. Click Next. 8. Select Comma as the delimiter. Note error (screen shot provied by link below). 9. Repeat steps 1-8 for GOOD data. Note that no error occurs. The problem with this error is that it is disabling access to the text file by the Microsoft Data Transformation Services Flat File Rowset Provider, which is used by higher-level DTS utilities. So, my problem boils down to this: how do I clean a comma- delimited text file when there are embedded quotation marks in the data as given in the BAD data example below.... LINKS: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt Screen shot of error: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError.JPG |
#3
| |||
| |||
|
|
-----Original Message----- Looks like you may have a text delimiter embedded within your data and it is not followed by a column delimiter. What does the data look like? -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Dan Allen" <danallen (AT) danallen (DOT) com> wrote in message news:091901c34b9a$3c0fefb0$a601280a (AT) phx (DOT) gbl... I need to find a way to get around a DTS error produced when I try using a certain text file as a data source. For a screenshot of the error, pls view the following link: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError. JPG Links to both good and bad data samples follow this explanation of the problem. The BAD data file contains a record that comes to me as one of 50,000 records. The problem with the data is that the name GERT is enclosed in two sets of quotation marks, leading to a DTS error when I try to use the file as a data source. STEPS TO REPRODUCE THE PROBLEM 1. Save the BAD data file to a locally-available drive. Link to bad data file below. 2. In Enterprise Manager, navigate to a node labeled "Data Transformation Services". 3. Right-click on Data Transformation Services. 4. Select New Package 5. Inside DTS Designer (which is what opens), in the Connection tool bar, click on the icon for Text File (source). 6. In the dialog box, next to the File name field, click on the box with 3 dots. 7. Navigate to the BAD data file. Select that file as the data source. Click Open. Click OK. Click Next. 8. Select Comma as the delimiter. Note error (screen shot provied by link below). 9. Repeat steps 1-8 for GOOD data. Note that no error occurs. The problem with this error is that it is disabling access to the text file by the Microsoft Data Transformation Services Flat File Rowset Provider, which is used by higher-level DTS utilities. So, my problem boils down to this: how do I clean a comma- delimited text file when there are embedded quotation marks in the data as given in the BAD data example below.... LINKS: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt Screen shot of error: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError.JPG . |
#4
| |||
| |||
|
|
Allan, Your 100% right....so my challenge is to find situations like that and fix them.... To see the data, you can download from the links below: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt -Dan -----Original Message----- Looks like you may have a text delimiter embedded within your data and it is not followed by a column delimiter. What does the data look like? -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Dan Allen" <danallen (AT) danallen (DOT) com> wrote in message news:091901c34b9a$3c0fefb0$a601280a (AT) phx (DOT) gbl... I need to find a way to get around a DTS error produced when I try using a certain text file as a data source. For a screenshot of the error, pls view the following link: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError. JPG Links to both good and bad data samples follow this explanation of the problem. The BAD data file contains a record that comes to me as one of 50,000 records. The problem with the data is that the name GERT is enclosed in two sets of quotation marks, leading to a DTS error when I try to use the file as a data source. STEPS TO REPRODUCE THE PROBLEM 1. Save the BAD data file to a locally-available drive. Link to bad data file below. 2. In Enterprise Manager, navigate to a node labeled "Data Transformation Services". 3. Right-click on Data Transformation Services. 4. Select New Package 5. Inside DTS Designer (which is what opens), in the Connection tool bar, click on the icon for Text File (source). 6. In the dialog box, next to the File name field, click on the box with 3 dots. 7. Navigate to the BAD data file. Select that file as the data source. Click Open. Click OK. Click Next. 8. Select Comma as the delimiter. Note error (screen shot provied by link below). 9. Repeat steps 1-8 for GOOD data. Note that no error occurs. The problem with this error is that it is disabling access to the text file by the Microsoft Data Transformation Services Flat File Rowset Provider, which is used by higher-level DTS utilities. So, my problem boils down to this: how do I clean a comma- delimited text file when there are embedded quotation marks in the data as given in the BAD data example below.... LINKS: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt Screen shot of error: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError.JPG . |
#5
| |||
| |||
|
|
-----Original Message----- I would write a scrubbing routine that wrote out the good file and use that as the data source instead. -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Dan Allen" <danallen (AT) danallen (DOT) com> wrote in message news:0b7101c34c65$cf391b60$a101280a (AT) phx (DOT) gbl... Allan, Your 100% right....so my challenge is to find situations like that and fix them.... To see the data, you can download from the links below: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt -Dan -----Original Message----- Looks like you may have a text delimiter embedded within your data and it is not followed by a column delimiter. What does the data look like? -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Dan Allen" <danallen (AT) danallen (DOT) com> wrote in message news:091901c34b9a$3c0fefb0$a601280a (AT) phx (DOT) gbl... I need to find a way to get around a DTS error produced when I try using a certain text file as a data source. For a screenshot of the error, pls view the following link: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError. JPG Links to both good and bad data samples follow this explanation of the problem. The BAD data file contains a record that comes to me as one of 50,000 records. The problem with the data is that the name GERT is enclosed in two sets of quotation marks, leading to a DTS error when I try to use the file as a data source. STEPS TO REPRODUCE THE PROBLEM 1. Save the BAD data file to a locally-available drive. Link to bad data file below. 2. In Enterprise Manager, navigate to a node labeled "Data Transformation Services". 3. Right-click on Data Transformation Services. 4. Select New Package 5. Inside DTS Designer (which is what opens), in the Connection tool bar, click on the icon for Text File (source). 6. In the dialog box, next to the File name field, click on the box with 3 dots. 7. Navigate to the BAD data file. Select that file as the data source. Click Open. Click OK. Click Next. 8. Select Comma as the delimiter. Note error (screen shot provied by link below). 9. Repeat steps 1-8 for GOOD data. Note that no error occurs. The problem with this error is that it is disabling access to the text file by the Microsoft Data Transformation Services Flat File Rowset Provider, which is used by higher-level DTS utilities. So, my problem boils down to this: how do I clean a comma- delimited text file when there are embedded quotation marks in the data as given in the BAD data example below.... LINKS: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt Screen shot of error: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError.JPG . . |
#6
| |||
| |||
|
|
Allan, I understand what you are saying. I am working on the scrubbing design now. I want to avoid dealing with software distribution issues (components, dlls, etc.) to the multiple machines that will have to run this scrubber. What technology would you use to build the scrubber? I am thinking it is worth it to invest in a package like Data Junction. I am very interested in your thoughts on this. Regards, Dan Allen Proprietor DanAllen.Com www.danallen.com -----Original Message----- I would write a scrubbing routine that wrote out the good file and use that as the data source instead. -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Dan Allen" <danallen (AT) danallen (DOT) com> wrote in message news:0b7101c34c65$cf391b60$a101280a (AT) phx (DOT) gbl... Allan, Your 100% right....so my challenge is to find situations like that and fix them.... To see the data, you can download from the links below: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt -Dan -----Original Message----- Looks like you may have a text delimiter embedded within your data and it is not followed by a column delimiter. What does the data look like? -- ---------------------------- Allan Mitchell (Microsoft SQL Server MVP) MCSE,MCDBA www.SQLDTS.com I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Dan Allen" <danallen (AT) danallen (DOT) com> wrote in message news:091901c34b9a$3c0fefb0$a601280a (AT) phx (DOT) gbl... I need to find a way to get around a DTS error produced when I try using a certain text file as a data source. For a screenshot of the error, pls view the following link: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError. JPG Links to both good and bad data samples follow this explanation of the problem. The BAD data file contains a record that comes to me as one of 50,000 records. The problem with the data is that the name GERT is enclosed in two sets of quotation marks, leading to a DTS error when I try to use the file as a data source. STEPS TO REPRODUCE THE PROBLEM 1. Save the BAD data file to a locally-available drive. Link to bad data file below. 2. In Enterprise Manager, navigate to a node labeled "Data Transformation Services". 3. Right-click on Data Transformation Services. 4. Select New Package 5. Inside DTS Designer (which is what opens), in the Connection tool bar, click on the icon for Text File (source). 6. In the dialog box, next to the File name field, click on the box with 3 dots. 7. Navigate to the BAD data file. Select that file as the data source. Click Open. Click OK. Click Next. 8. Select Comma as the delimiter. Note error (screen shot provied by link below). 9. Repeat steps 1-8 for GOOD data. Note that no error occurs. The problem with this error is that it is disabling access to the text file by the Microsoft Data Transformation Services Flat File Rowset Provider, which is used by higher-level DTS utilities. So, my problem boils down to this: how do I clean a comma- delimited text file when there are embedded quotation marks in the data as given in the BAD data example below.... LINKS: BAD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/BadData.txt GOOD DATA (comma delimited text file): http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/GoodData.txt Screen shot of error: http://www.danallen.com/299- ReleaseArea/2003_07_15_TextfileProblem/DTSError.JPG . . |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.