Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jesus4u
Posting Yak Master
204 Posts |
 Posted - 2003-01-24 : 08:41:40
|
How can I have my package first open and then clean up my textfile BEFORE I run the rest of the package?Here is sample data. I need to get rid of ANY lines that start with the # symbol.Thanks for the ideas!#Software: Microsoft Internet Information Services 5.0#Version: 1.0#Date: 2003-01-22 16:21:27#Fields: date c-ip cs-uri-stem cs-uri-query cs(Referer)2003-01-22 10.10.2.254 /hr/ViewOptions.asp - http://65.240.226.68/hr/login.asp2003-01-22 10.10.2.254 /hr/ViewOptions.asp - http://65.240.226.68/hr/ViewOptions.asp2003-01-22 10.10.2.254 /hr/ViewJobTitles.asp FormID=1 http://65.240.226.68/hr/ViewOptions.asp2003-01-22 10.10.2.254 /Default.asp - -2003-01-22 10.10.2.254 /Default.asp - -2003-01-22 10.10.2.254 /Default.asp - -2003-01-22 10.10.2.254 /Default.asp - -2003-01-22 10.10.2.254 /Default.asp - -#Software: Microsoft Internet Information Services 5.0#Version: 1.0#Date: 2003-01-22 16:21:27#Fields: date c-ip cs-uri-stem cs-uri-query cs(Referer)2003-01-22 10.10.2.254 /hr/_Instructions.asp Switch=7&JobTitle=Internet%20Correspondent&UID=32&CJQID=78&FormID=1 http://65.240.226.68/hr/ViewJobTitles.asp?FormID=1 id=code> <edit> to remove big honkin' line of code that ran off the edge of the world </edit>Edited by - robvolk on 01/24/2003 08:45:26 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-24 : 08:49:38
|
| Why not import the text file into a staging table with a single varchar(8000) column? Then you can run:DELETE FROM stagingTable WHERE LineOfText LIKE '#%'You can then parse the remaining rows and INSERT them into the final destination table. You can use a bulk insert task in DTS, or the BULK INSERT command, or bcp, and the import will be super fast.There are ways to clean the text file without using SQL, but they won't be as fast, and you'll either need to modify the original file or make a copy an work on the copy. You could also use ActiveX transformations to remove the offending lines, but that will be even slower. |
 |
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2003-01-24 : 08:57:41
|
quote:
Why not import the text file into a staging table with a single varchar(8000) column? Then you can run:DELETE FROM stagingTable WHERE LineOfText LIKE '#%'You can then parse the remaining rows and INSERT them into the final destination table. You can use a bulk insert task in DTS, or the BULK INSERT command, or bcp, and the import will be super fast.There are ways to clean the text file without using SQL, but they won't be as fast, and you'll either need to modify the original file or make a copy an work on the copy. You could also use ActiveX transformations to remove the offending lines, but that will be even slower.
I REALLY appreciate the idea! Thank you, thank you... |
|
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2003-01-24 : 12:38:20
|
robvolk!I give credit where credit is due! Your idea gave me this solution and it works perfectly. Thank you again...cya around. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-24 : 12:48:28
|
 Glad that worked for you!I'm curious about the need for 2 connections though: is this data being transferred between 2 different SQL Servers, or does it all exist on one? If it's just one server, you can do a straight import into the staging table (without needing to run another package), and then write some code to do all the parsing after the import. It would save you all of the steps after the Cleansing task (they would be added or incorporated into Cleansing) |
|
|
|
jesus4u
Posting Yak Master
204 Posts |
Posted - 2003-01-24 : 12:51:33
|
quote:
Glad that worked for you!I'm curious about the need for 2 connections though: is this data being transferred between 2 different SQL Servers, or does it all exist on one? If it's just one server, you can do a straight import into the staging table (without needing to run another package), and then write some code to do all the parsing after the import. It would save you all of the steps after the Cleansing task (they would be added or incorporated into Cleansing)
yeah well it isn't pretty but it was generated automatically when I saved my dts. Don't really know how to fix it yet. |
|
|
|
|
|
|
|
|
Parsing Textfile in a Package