Lookup Transform--Need to limit columns going to destination

Question

Post reply

Lookup Transform--Need to limit columns going to destination

lduvall

SSCarpal Tunnel

Points: 4869
More actions
August 26, 2010 at 1:03 pm

#142518

I've got a flat file source that I want to pull into an OLE DB connection. Before I dump the data into my table, though, I want to catch some errors. I'm not having any trouble catching the errors and writing it to a destination flat file. It seems to work great, but right now it is pulling all the source columns (187 of them!). I just need a small handful. I know that I can add or remove columns from the reference (lookup) table, but how do I eliminate some columns from the source? I tried changing the "Input Column" to <Ignore> on the Mappings area in the Flat File Destination Editor, but that didn't appear to help. I know I'm missing something obvious. Any help is appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

Mappings is where you do it.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

JasonYousef SSCommitted Points: 1769 More actions · Answer 2

I'm not quite sure I understand your question, but I think you can add a sort before the lookup and pass through only the desired columns.

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 3

I click on the flat file destination source and go to the Mappings area and I'm selecting <ignore> on the input column. However, the file that is getting produced still has the columns I want to ignore.

I'm attaching a screenshot.

Russel Loski SSCertifiable Points: 7059 More actions · Answer 4

Does the connection manager for the flat file have the column that you want to leave out? My guess is that by selecting ignore on the mapping in the flat file destination you will have a comma for the column, just no contents.

You need to remove the column from the flat file connection manager.

http://msdn.microsoft.com/en-us/library/ms183343.aspx

Russel Loski, MCSE Business Intelligence, Data Platform

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 5

Russel Loski (8/29/2010)
Does the connection manager for the flat file have the column that you want to leave out? My guess is that by selecting ignore on the mapping in the flat file destination you will have a comma for the column, just no contents.
You need to remove the column from the flat file connection manager.
http://msdn.microsoft.com/en-us/library/ms183343.aspx

Thanks, Russel! That fixed it. I knew I was missing something.

Can I ask another question? When I did the lookup transform, I used "Configure Error Output" to create a new column so that any rows that were re-directed also got tagged with a particular Error Description. When I try to include that new Error Description column in the file destination, it's coming out as an integer rather than the string that I'm telling it. When I look at my destination connection file manager, it's showing the field is a string (DT_STR), but in the metadata between the lookup and the file destination, it's showing it as DT_I4. How do I change this?

I should mention that the "table" I'm using as the Reference Table in the Lookup Transform isn't a real table. It's a fixed query:

I clicked the "Use results of an SQL query" button and have this syntax:

select '500' as stage, 'Invalid Stage' as 'ErrorCode'

It may be that I can't do this. I don't have an actual table in the db to compare to. I really just want to know if I have any records that have a stage other than '500' and if there are, I want to direct them to an error file with that code.

Am I on the right track? Thanks for your help, everyone!

Russel Loski SSCertifiable Points: 7059 More actions · Answer 6

I would use a conditional split.

http://sqlblog.com/blogs/andy_leonard/archive/2009/02/20/ssis-expression-language-and-the-conditional-split-transformation.aspx

http://msdn.microsoft.com/en-us/library/ms137886.aspx

Basically if a column has the value you expect then send it to the main output. Otherwise send to your error.

Russel Loski, MCSE Business Intelligence, Data Platform