Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Data Warehousing » Integration Services » Lookup Transform--Need to limit columns going...
Add to briefcase

Lookup Transform--Need to limit columns going to destination Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
lduvall
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: Tuesday, March 19, 2013 12:13 PM
Points: 115, Visits: 288
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
Alvin Ramard
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: Saturday, November 10, 2012 8:24 AM
Points: 3,031, Visits: 7,383
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
JasonYousef
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, May 20, 2013 7:28 AM
Points: 68, Visits: 558
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
lduvall
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: Tuesday, March 19, 2013 12:13 PM
Points: 115, Visits: 288
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 (21 views, 1.17 MB)
Post #976933
Russel Loski
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: Today @ 7:12 AM
Points: 542, Visits: 7,988
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
lduvall
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: Tuesday, March 19, 2013 12:13 PM
Points: 115, Visits: 288
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
Russel Loski
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: Today @ 7:12 AM
Points: 542, Visits: 7,988
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.