Deleting lines from a text file

I have a flat file that look like this

############################################################
# Market Issue Lookup
# Applies to: Muni,Pfd,Govt,Corp
###########################################################
LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|

############################################################
# Maturity Type Lookup

and i want it to look like this

LU_MARKET_ISSUE| |AUSTRALIAN|Corp|
LU_MARKET_ISSUE| |BULLDOG|Corp|
LU_MARKET_ISSUE| |CANADIAN|Corp|
LU_MARKET_ISSUE| |WARRANTS|Muni|
LU_MARKET_ISSUE| |YANKEE|Corp|

Basically remove any line that start with a "#" or any blank lines..

I am assuming you can do this only using a script component and not directly through ssis..but i am not too familiar with scripting...so some code would be helpful

Thanks for any help in advance.

smathew

You don't need a script component for that. Use a derived colum to trim leading spaces and then a conditional split to discard all rows that start with '#'|||

but when it reads the file.. I am assuming you have to use a Flat File Source...

in that case all the lines are read into a single column...

true that you will be able to delete empty lines and lines that start with a #, but you end up having the rest in a single column instead of having 4 coulmns.

|||

You could add a second dataflow where you read the file after removing the '#' rows. This time, the flat file source has 4 columns instead a single one. This is just in case you don't want to use the script component. A drawback is that you need to read the file twice.

|||Given your example, I'd follow Rafael's recommendation. If the file is large, and you don't want to process it twice, take a look at http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx. This has some example script for taking in the flat file and parsing it. You could alter the script slightly to look for and discard the "#" rows.

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000Hi
"donsql22222" wrote:
> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I then
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more than
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000
Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I then
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more than
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John

DELETE not deleting all records..mystery.

I've got a job that has 3 lines and it reports success after it runs.
DELETE FROM TABLE1 WHERE DATE >= '20070115'
DELETE FROM TABLE2 WHERE DATE >= '20070115'
DELETE FROM TABLE3 WHERE DATE >= '20070115'
I then have a separate job for each line below which imports from another
server using DTS.
SELECT * FROM TABLE1 WHERE DATE >= '20070115'
SELECT * FROM TABLE2 WHERE DATE >= '20070115'
SELECT * FROM TABLE3 WHERE DATE >= '20070115'
The 1st job fails, but the other 2 are ok. I then use QA and execute the
single statement:
DELETE FROM TABLE1 WHERE DATE >= '20070115'
and several thousand records are deleted. huh?
During this process, there is no application or person inserting records
into the table.
How can there still be records in the table after the delete job ran? I then
rerun the job to insert from another server and it succeeds.
This has happened on many servers over the last few months. Any opinions
greatly appreciated.
Thanks,
Don
SQL 2000Hi
"donsql22222" wrote:

> I've got a job that has 3 lines and it reports success after it runs.
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> DELETE FROM TABLE2 WHERE DATE >= '20070115'
> DELETE FROM TABLE3 WHERE DATE >= '20070115'
> I then have a separate job for each line below which imports from another
> server using DTS.
> SELECT * FROM TABLE1 WHERE DATE >= '20070115'
> SELECT * FROM TABLE2 WHERE DATE >= '20070115'
> SELECT * FROM TABLE3 WHERE DATE >= '20070115'
> The 1st job fails, but the other 2 are ok. I then use QA and execute the
> single statement:
> DELETE FROM TABLE1 WHERE DATE >= '20070115'
> and several thousand records are deleted. huh?
> During this process, there is no application or person inserting records
> into the table.
> How can there still be records in the table after the delete job ran? I th
en
> rerun the job to insert from another server and it succeeds.
> This has happened on many servers over the last few months. Any opinions
> greatly appreciated.
> Thanks,
> Don
> SQL 2000
It would appear that when you say the first job to import data fails it is
not failing before data has been committed. If the package contains more tha
n
a single Execute SQL task then it could be one of the subsequent steps that
fails (assuming they are not in a single transaction) or if the job has more
than one step it could be a subsequent step that has failed or the step has
already committed the data before it fails.
John