In this post, we will learn how we will call a user-defined
function in execute sql task. I am creating a simple user defined function
where I am passing the product ID and this function will return product
details.
Read more about User defined function: User define function(UDF) in sql server
USE [AdventureWorksDW2012]
GO
CREATE FUNCTION [dbo].[ufnGetProductName]
(@ProductID [int])
RETURNS varchar(200)
AS
BEGIN
DECLARE @ProductDetails varchar(200);
SELECT @ProductDetails ='ProductKey : '+ cast (ProductKey as varchar)+' and ProductAlternateKey
: '
+ProductAlternateKey +' and
EnglishProductName : '+EnglishProductName
FROM DimProduct
where ProductKey = @ProductID
IF (@ProductDetails IS NULL)
SET @ProductDetails = 'Product is not listed'
RETURN @ProductDetails
END;
|
Function created successfully.
Now In SSDT I am taking execute sql task and connecting to
the AdventureWorksDW2012 database.
Now we need to create two variable one for storing the
product details and another to pass the product ID.
Now we need to do the parameter mapping.
My function return the product
details so I am setting direction as Return Value.
Read ReturnValue: ReturnValuedirection in execute sql task in SSIS
Click ok.
To see the value in product details I am using the script
task and writing the below C# code.
MessageBox.Show("Product ID : " + Dts.Variables["User::productID"].Value.ToString()+
Environment.NewLine+"Product details : " + Dts.Variables["User::productName"].Value.ToString());
|
Now I am running this package.
Now I am passing the production number which is not in the
table.
