asked
how to use UDF in SSIS
Hi can some one help me please. my scenario is i have to migrate the data from ORACLE to SQL SERVER, in this process i have a UDF in SQL SERVER.This UDF Concatenates few columns from source and i should map that to one column in destination. iam unable to understand where i can put this udf and map to my destination column. I will give an example below please give me a suggestion Sourece i have a table called EMPLOYEE with four columns as EmpID,FName,LName,EmailID,Location,Address. I wrote a User Defined Function in SQL SERVER(udf_Concatinateddata), in which i have concatenated FName+LName+EmailID. Destination: there are columns in destination EmpID,EmpDetails,Location,Address. (FName+LName+EmailID) =EmpDetails Select dbo.Concatinateddata (@FName,@LNamae,@EmailID) How do i select this function so that i get the data from source columns for all the records with other columns from source to destination. like for all the columns EmpID,EmpDetails,Location,Address at same time. iam using SSIS, iam not sure where and how should i use the UDF . PLEASE HELP
There are quite a few other better options than using a scalar valued function. It would not scale well. The simplest solution is to get the concatenated values from Oracle source i.e. SELECT EmpID, FName||LName|EmailID "EmpDetails" --Oracle uses || two solid vertical bars for concatenation ,Location,Address FROM EMPLOYEE Moreover, having it as a VIEW on Oracle source could ease the maintenance, depending upon your requirement. Another solution could be to use the Derived Column Transformation of SSIS. You can concatenate the three columns and have them as the output for mapping to the destination. EDIT: As the limitation is to use the scalar function and OLEDB command, this is how it can be done. Make a Stored procedure which will call your scalar function and insert the values in the destination table i.e. something like SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO Create PROCEDURE [dbo].[Proc_InsertValuesInDestination] ( @EmpID INT, @Location VARCHAR(100), @Address VARCHAR(500), @TravelDirection Varchar(300), @Last_Name Varchar(30), @First_Name Varchar(30), @Phone Varchar(30), @Fax Varchar(30), @Email Varchar(30) ) AS Begin INSERT INTO Destination.[table] ( ColumnNames ) SELECT @EmpID, [dbo].[Udf_GetApplicationInstructionsFunction](@TravelDirection, @Last_Name, @First_Name, @Phone, @Fax, @Email), @Location, @Address End You can also encapsulate all the concatenation logic inside the Stored procedure. Then simply put the execution query of the procedure in the SqlCommand property of OLEDB command i.e. something like EXEC [dbo].[Proc_InsertValuesInDestination] ?,?,?,?...... I hope this is all what you need. EDIT:
@arvindreddy33 Although I do not like this approach, but I have edited my answer to show how it can be done.
As @Usman Butt mentioned in his answer, there are better approaches and much better is to modify the package than call a stored proc on Per Row basis which will decrease ETL performance significantly. If the logic is too complex for simple derived column, then you can use a Script Component as transformation in Data Flow and make whatever processing necessary using the power of C# or
VB.Net.
Thanks for replying, actually the reason I have been asked for using a UDF is that they may make changes in the data so they said its easy if i have a udf than writing the code in derived column or inside the package. My confusion is how do i use the dbo.function (source column names) in SSIS package ( like in OLEDB Command or in script component and how) below is my actual function Create Function [dbo].[Udf_GetApplicationInstructionsFunction] ( @TravelDirection Varchar(300), @Last_Name Varchar(30), @First_Name Varchar(30), @Phone Varchar(30), @Fax Varchar(30), @Email Varchar(30) ) Returns Varchar (8000) AS Begin Declare @ApplicationInstructions VARCHAR(8000)= '', @Travel_Direction Varchar(40)='TravelDirection: ', @LastName Varchar(40)='Last Name: ', @FirstName Varchar(40) = 'First Name: ', @Phon_e Varchar(40) = 'Phone: ', @Fa_x Varchar(40) = 'Fax: ', @Emai_l Varchar(40) ='Email: ' IF(@TravelDirection IS Not Null) Begin Set @ApplicationInstructions =@Travel_Direction +@TravelDirection+char(13) End IF(@Last_Name IS NOT NULL) Begin Set @ApplicationInstructions = @ApplicationInstructions+@LastName+@Last_Name+CHAR(13) End If(@FirstName is Not Null) Begin Set @ApplicationInstructions=@ApplicationInstructions+@FirstName+@First_Name+CHAR(13) End If(@Phone is Not Null) Begin Set @ApplicationInstructions=@ApplicationInstructions+@Phon_e+@Phone+CHAR(13) End If( @Fax is Not Null) Begin Set @ApplicationInstructions=@ApplicationInstructions+@Fa_x+@Fax+CHAR(13) End If(@Email is Not Null) Begin Set @ApplicationInstructions=@ApplicationInstructions+@Emai_l+@Email+CHAR(13) End Return(LTRIM(RTRIM(@ApplicationInstructions))) End i was trying with OLEDB command as my destination and inthe sql command in it iam writing soemthing like Insert Into Destination.table ( column names) Select ?(other columns from source) dbo.function(?,?,? (concatenated col's from source)) For example: INSERT INTO DBO.Test(EMPID,EMPDETAILS) Select ?,dbo.SampleDetails(?,?,?) when iam trying the above code , oledb command is throwing me an error ‘’syntax error, permission violation, or other nonspecific error’’.
@arvindreddy33 Sorry, but I did not understand it? If some changes are going to be done at the source, then would you not be changing the package anyway?
7 People are following this question.
