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

How do you use parameters in SSIS lookup functionality Expand / Collapse
Author
Message
Posted Thursday, March 11, 2010 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 23, 2010 10:25 AM
Points: 10, Visits: 16
Hi everybody...I"m new to SSIS, a tool my company is making us use primarily as a DTS tool. What I'm trying to do is use a flat file as the input, perform transformations and pass it to a Sybase table. Where I am stuck is in addition to performing manipulations such as dates, etc, we need to update and add data from a variety of lookup tables. The problem is I need to pass parameters into the lookup as I have a one to many error if I don't.

I am trying to take the field "ReinsuranceDate" from the flat file (after converted of course) and using fields "Min_Pol_Dt" and "Max_Pol_Dt" in a lookup table called "PlanCode", add in fields from the "PLaCode" tabel to be mapped, but only where the Reinsurance Date is between the Min_Pol_Dt and Max_pol_dt.

I think I am supposed to update the SQL in the advanced tab of the lookup, but I can't figure out how to pass the field I need through. Here is the SQL from the advanced tab as is (I am matching on those three fields)

select * from
(select * from `PlanCode`) as refTable
where [refTable].[ClientCompanyCode] = ? and [refTable].[ClientTreatyNumber] = ? and [refTable].[ClientPlanCode] = ?
Post #880934
Posted Thursday, March 11, 2010 12:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 23, 2010 10:25 AM
Points: 10, Visits: 16
I've figured this out by utilizing the Advanced Tab on the function and modifying the SQL. Thanks!
Post #881275
Posted Wednesday, August 22, 2012 9:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 2:46 PM
Points: 3, Visits: 35
You might consider sharing the answer!
Post #1348479
Posted Wednesday, August 14, 2013 10:39 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 317, Visits: 1,529
So, what is the answer?


Post #1484397
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse