Deleting Specific Rows and Columns in SSIS

Question

Post reply

Deleting Specific Rows and Columns in SSIS

NoviceJatt

SSCommitted

Points: 1671
More actions
July 13, 2010 at 2:44 pm

#226732

Hi,
I'm new to database and I posted this question before, but didnot get any response. But I'm reposting it with a little different question this time. How can I skip Certain Rows and Columns from a table in SSIS. I mean which tranfornations I need to apply to the table to get the following result.
Flat File Data:
Code,Time,Volume,Data,Edge
100,6/20/2010,200,150,300
100,6/21/2010,210,160,320
100,6/23/2010,220,170,340
100,Total,630,580,960
102,6/24/2010,230,180,350
102,6/25/2010,210,160,320
102,6/26/2010,220,170,340
102,Total,630,580,960
103,6/27/2010,230,180,350
103,6/28/2010,210,160,320
103,6/29/2010,220,170,340
103,Total,630,580,960
After Importing it into Table, I want to delete the rows which have "total" and the column "Data".
Table after deletion:
Code,Time,Volume,Edge
100,6/20/2010,200,300
100,6/21/2010,210,320
100,6/23/2010,220,340
102,6/24/2010,230,350
102,6/25/2010,210,320
102,6/26/2010,220,340
103,6/27/2010,230,350
103,6/28/2010,210,320
103,6/29/2010,220,340
Can you guys provide me steps to do it?
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Script Component will do it.

Check here for details... http://msdn.microsoft.com/en-us/library/ms137640.aspx

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

Novicejatt (7/13/2010)
After Importing it into Table, I want to delete the rows which have "total" and the column "Data".
Table after deletion:
Code,Time,Volume,Edge
100,6/20/2010,200,300
100,6/21/2010,210,320
100,6/23/2010,220,340
102,6/24/2010,230,350
102,6/25/2010,210,320
102,6/26/2010,220,340
103,6/27/2010,230,350
103,6/28/2010,210,320
103,6/29/2010,220,340
Can you guys provide me steps to do it?
Thanks

If you really mean 'after importing it into a table' that you want to drop a couple of columns, why not just ALTER TABLE <tablename> DROP COLUMN <column name>?

NoviceJatt SSCommitted Points: 1671 More actions · Answer 3

Yes I can do that, but I was thinking not to import in the first place. I guess I will Drop them after importing.

Thanks Guys.

KoldCoffee SSCoach Points: 18971 More actions · Answer 4

oh i see. I remember reading a post last week with similar issue. One respondent said to drop on the remote end before importing. Another,:( that it wasn't possible in SSIS (to drop column within SSIS).

NoviceJatt SSCommitted Points: 1671 More actions · Answer 5

I will just write a query to delete the specific rows and columns.

Thanks.

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 6

Novicejatt (7/13/2010)
Yes I can do that, but I was thinking not to import in the first place. I guess I will Drop them after importing.
Thanks Guys.

If you just want to ignore Rows/columns.. try using a select statement like

Select Code,Time, Volume , Edge FROM tableName

WHERE Convert(Varchar,Time) != 'Total'

hope i am not missing anything.. Am I? Let me know

NoviceJatt SSCommitted Points: 1671 More actions · Answer 7

I dont want specific rows and columns in my tables. It is better if I dont import them in the first place. But I guess I will just delete them from the table by DELETE query.

Thanks

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 8

Novicejatt (7/14/2010)
I dont want specific rows and columns in my tables. It is better if I dont import them in the first place. But I guess I will just delete them from the table by DELETE query.
Thanks

If you don't want to import them at all then you can use the query from my last post.. In SSIS, You can also use Conditional split to eliminate unnecessary rows similar to what I have done using where clause