Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Lookup Transform--Need to limit columns going to destination Expand / Collapse
Author
Message
Posted Thursday, August 26, 2010 1:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 122, Visits: 311
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.
Post #975884
Posted Thursday, August 26, 2010 1:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:41 PM
Points: 3,165, Visits: 8,077
Mappings is where you do it.



Alvin Ramard
Memphis PASS Chapter

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.
Post #975896
Posted Friday, August 27, 2010 9:06 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, September 8, 2014 7:33 AM
Points: 70, Visits: 615
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.
Post #976873
Posted Saturday, August 28, 2010 7:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 122, Visits: 311
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.


  Post Attachments 
destination_ignore.bmp (25 views, 1.17 MB)
Post #976933
Posted Sunday, August 29, 2010 6:33 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 565, Visits: 8,710
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, MCITP
Post #977130
Posted Tuesday, August 31, 2010 10:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 122, Visits: 311
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!
Post #978187
Posted Tuesday, August 31, 2010 10:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:14 PM
Points: 565, Visits: 8,710
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, MCITP
Post #978202
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse