How to use user Defined Function in SSIS?

Question

Post reply

How to use user Defined Function in SSIS?

Charmer

SSChampion

Points: 14903
More actions
June 6, 2012 at 3:13 am

#145379

Hi Friends,
I have written a function which i would like to use in ssis...
I don't know how to call the function....?
CREATE FUNCTION [dbo].[GetDateTimeAdd](@Date varchar(8), @sec smallint)
RETURNS datetime
WITH EXECUTE as CALLER
as
BEGIN
DECLARE @FullDttm datetime
If
@Date = '0'
or
ISDATE(substring(@Date, 5,2) + '/' + substring(@Date, 7,2) + '/' + left(@Date,4)) = 0
Set @FullDttm = '1/1/1900'
Else
set @FullDttm =
substring(@Date, 5,2) + '/' + substring(@Date, 7,2) + '/' + left(@Date,4)
SET @FullDttm = dateadd(SECOND, @sec, @FullDttm)
RETURN (@FullDttm)
END;
this is my function and i have to call in 2 methods
select dbo.GetDateAdd(INCALD,0) -- INCALD is a Column
select dbo.GetDateAdd(INCALD,Row_number() over(partion by column order by column) + select max(SequenceNumber) from table)
and is that possible to achieve row_number partition in ssis...? i know we can use Sort transformation but we can't get the values to be used in rows like we do in SQL if i am not wrong...
Ho can i achieve this?
Could you Help me, Friends...?
Any suggestions would be really appreciated...
Thanks,
Charmer

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Smash125 Hall of Fame Points: 3562 More actions · Answer 1

What i can think of

Use OLE DB Source and select either 'SQL Command from variable' and 'SQL Command'

In 'SQL command' you can execute the same syntax that you use in Management Studio in 'SQL Command text'

Ex:SELECT * FROM fnGetInfo(12)

In the case of 'SQL Command from variable' you need to store the query in a variable call it

Create a variable of string type and enter the SQL syntax (SELECT * FROM fnGetInfo(12)) in value and call it

Charmer SSChampion Points: 14903 More actions · Answer 2

Smash125 (6/7/2012)
What i can think of
Use OLE DB Source and select either 'SQL Command from variable' and 'SQL Command'
In 'SQL command' you can execute the same syntax that you use in Management Studio in 'SQL Command text'
Ex:SELECT * FROM fnGetInfo(12)
In the case of 'SQL Command from variable' you need to store the query in a variable call it
Create a variable of string type and enter the SQL syntax (SELECT * FROM fnGetInfo(12)) in value and call it

Thanks for the response, smash....

Could you tell me how can we use partition in ssis? i mean not through T-SQL statements...

Thanks,
Charmer

JMJ Newbie Points: 1 More actions · Answer 3

There are several important benefits to expressing your behavior in SSIS code.

You can easily check the structure of your code - especially if your character has a good reputation, so it's clear what you're doing.
Code translation makes more sense - because you look at the structure of the code and do it without reading all the code lines.
The code is easy to use, so you no longer need to type the same code over and over againChecking the code and correcting the errors is easy - you have a good testing package so you can test all the functions yourself without having to check the whole program.