Hi i am working on SSIS i have typical question i have T-SQL Query which uses user defined function to fetch the value from the other table the following Query will give u the idea INSERT INTO Mig_Banksoft.dbo.Mig_SBCAMstr( DateOfActOpening, OperatingInstruction) SELECT DateOfActOpening=OPENDATE, OperatingInstruction=[Mig_Banksoft].dbo.Mig_Fun_For_Fetching_OperatingInstruction_code(OPRNINST), FROM Banahatti_SB.dbo.SBUPDT1 SB JOIN Mig_Banksoft.dbo.Mig_CustomerMstr customer on SB.ACNO=customer.Old_Accont_Number and SB.SB_TYPE=customer.LoandepositMainCodeTypes where customer.product='SB' the function [Mig_Banksoft].dbo.Mig_Fun_For_Fetching_OperatingInstruction_code(OPRNINST) as query as below Create FUNCTION [dbo].[Mig_Fun_For_Fetching_OperatingInstruction_code] ( @operatingInst VARCHAR(50) ) RETURNS varchar(50) AS BEGIN DECLARE @operatingInstCode VARCHAR(50) SELECT @operatingInstCode= GdCode FROM Mig_BankSoft.dbo.Mig_GeneralDefinition WHERE GDName=@operatingInst and gl='O' if @operatingInstCode is null set @operatingInstCode='OIS00001' RETURN @operatingInstCode END the above query both insert Statement and function works fine but now task is to perform this task using SSIS Package please help how i can achieve this.Please help your tips will be much appreciated thanks in advance Regards Sunil Yoganna

You can create a stored procedure and call it from package using Execute Sql Task. OR Using Data FLow task you can select your data and implement the functionality of UDF using SSIS Transformations then Insert using Ole DB Destination. hope this helps.. Cheers, Shailesh

If you write that statement into a SQL Execuettask it will executed on SQL Server. Ther is nothing else to do. The Function has to exists on the Server. But in SSIS I would do the same request with a Lookup Task. I think, it is more efficent as an UDF.

ya i am also thinking the same which transform is best suited for for this task in the function we having one constant value to compare how i can do this please help Regards Sunil yoganna

ya christ, look up transform suits best but their is one constant value how i can compare the constant value with other collumn please help thanks in advance Regards Sunil Yoganna

how i can compare the constant value with other collumn you can compare more than one column in lookup task If another Table has the column for constant value then you can map same else write a query in lookup task with Filter of Constant value like SELECT Col_names from table WHERE gl='O' and use resultant output for lookup with other table ...Cheers, Shailesh

hi Sailesh, thanks for your tips its working fine but what about if the @operatingInstCode is null i need to set the that value to OIS00001 how i can achieve this please help it is almost done only i should avoid null values how i can because it doesnot allo null values thanks in advance. Regards Sunil Yoganna

Since you have to handle NULL records also So i'll advise you to use Merge Join instead of lookup (it performs inner join) using merge join perform Left Outer Join then afterwards replace those NULL values with Constant values OIS00001 in Derived column using expression in same.Cheers, Shailesh

hi Sailesh, Ii used merge join the number of rows out put from merge join is grater then the the actual row can please give me the hint what would be the wrong my mind is getting blunt please help. Regards Sunil Yoganna

I guess you are doing FULL JOIN if you are not aware how Merge join works please go through some examples (easily you get in Google ) and basic of it in MSDN http://msdn.microsoft.com/en-us/library/ms141775.aspx Cheers, Shailesh