SSIS 2008 using OLE DB Command to call a TSQL Function for each row in dataset

• Rick Hodder

  Old Hand

  Points: 359

  More actions

  August 19, 2010 at 4:32 pm

  #144626

  Hi,

  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(datetime,int) 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!

  ---

  Thanks,

  Rick Hodder

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  August 20, 2010 at 2:21 am

  #1209516

  You can try to write the data to a staging table first.

  Then call the UDF in the database itself and write the results to your final destination table.

  This is all set based, so it should run a little faster.

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP