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.
ReplyDeleteUsually I never comment on blogs but your article is so convincing that I never stop myself to say something about it. You’re doing a great job Man learn Msbi online training
Thanks for another excellent post. Where else could anybody get that type of info in such an ideal way of writing? In my opinion, my seeking has ended now. The Best Task Management Tool
ReplyDelete
ReplyDeleteSuch an impressive post Thanks for sharing.. We are providing the best services click on below links to visit our website.
Snowflake Training
Snowflake Training in Hyderabad
Snowflake Online Training
Snowflake Online Training Hyderabad
Snowflake Training Online
Snowflake Training in Ameerpet
Snowflake Training Institute in Hyderabad