SSIS OLE using DBCommand to call tsql function

Question

I'm importing Oracle data into SQL Server. After my OLE DB Source that runs a query to grab the data to process, I'm trying to call a TSQL function on a field value somehow...

I have an SSIS data flow where I have a datetime column called DepartureDateGMT, and an integer column called DepartureTimeZoneKey.

I have a TSQL Function called dbo.udf_ConvertFromGMT(,) that returns a datetime

I am trying to create an OLE DB Command that fills a column called DepartureDate that holds the value of the call:

dbo.udf_ConvertFromGMT(DepartureDateGMT,DepartureTimezoneKey)

for each row in the SSIS dataflow.

I'm not updating/inserting into an existing table, I'm just trying to store the result into the DepartureDate column of each row of the data flow's dataset

I have tried alot of things, like

Exec ? dbo.udf_ConvertFromGMT(?,?)

SELECT dbo.udf_ConvertFromGMT(DepartureDateGMT,DepartureTimezoneKey)

dbo.udf_ConvertFromGMT(?,?)

DECLARE @Result DATETIME SET @Result = dbo.udf_ConvertFromGMT(?,?)

But I'm getting :Syntax Error, permission violation or other nonspecific error

Am I barking up the wrong tree? I have tried using DerivedColumn, but it doesnt allow calling a TSQL function.

Thanks!

Answer 1

The OLE DB Command in a Data Flow task is designed to run a SQL statement once for each row in the data flow. Whether it works for you or not, you may not be happy with the performance if there are many rows to process. Have you looked into the SSIS expression language to see if you can move the functionality, instead of the function call, into a Derived Column component?

If you must use the OLE DB Command, you might try changing your SQL function to a SQL stored procedure. In the OLE DB Command you can call a procedure like this

EXEC dbo.usp_ConvertFromGMT ?, ?, ? OUTPUT

I think you can then map your result as the output parameter (3rd ? in the command) to a data flow column.