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

SSIS Package with(Derived Column & Lookup) Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 4:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 9:15 PM
Points: 27, Visits: 190
Hi All,

I have a SSIS package that has data in a table. I have a OLE DB Source to connect to the table but i need to do a look up to populate a field within that table. But the field i'm looking up on the destination is nvarchar and the source field is an int and the SSIS componet for look up dosen't allow lookup's with different data types. Can i change this with a derved column ?

I could change the the data types on the tables but their both populated form external sources.

Thank you in advance.
Post #1365130
Posted Thursday, September 27, 2012 4:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 11:49 AM
Points: 48, Visits: 269
just go to the advanced tab-> output column and change the datatype.
or
use the conversion task.
Post #1365137
Posted Thursday, September 27, 2012 4:54 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
Have you seent DataConversion task in SSIS pipes, as this seems to be what you are looking for. simply place it after the extractor, and do the conversion on the column, then run that into the Lookup and join on your new converted column.



_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1365139
Posted Friday, September 28, 2012 12:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 30, 2012 11:27 PM
Points: 1, Visits: 4
Before doing the lookup task convert the field to nvarchar using data conversion task

Business Intelligence
Post #1365624
Posted Friday, September 28, 2012 2:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 9:15 PM
Points: 27, Visits: 190
Cheers guys the conversion task worked a dream.

In relation to SCC Rookie's comment "just go to the advanced tab-> output column and change the datatype" where should i be looking for this ?

The reason i ask is so that i know for the future and can cover all permutations.
Post #1365679
Posted Friday, September 28, 2012 9:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 9:15 PM
Points: 27, Visits: 190
I have previously posted a question yesterday and thank you all for your quick response.

I’ve nearly completed the SSIS package but unfortunately it stalls(doesn't error and have left it for 15 - 20 minutes) at the last step.

The step that it stalls on is the updating of the lookup values from the lookup. I’ve tried many ways on performing the lookup cache and a query

I currently have a SSIS with the following

OLE DB Source

Data Conversion - (To change my lookup value from int to nvarchar to match it in the lookup)

Derived Column - (ltrim(rtrim(column)

Lookup

OLE DB Destination


Any ideas ?
Post #1365934
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse