1

I have a scalar value function that I created to calculate aging and I want to make it apart of my SSIS package that I run monthly. How do I go about making this work?

Craig
  • 11
  • 1
  • 2
  • You need to provide more details. Are you looking to create an SSIS component that will populate a field, or are you looking to use a 'derived column' component to use your function? – Marc Bernier Sep 12 '11 at 18:46

3 Answers3

2

If you mean you have a t-sql function that you would like to call in a data flow somehow, then that is not something you can actually do. Your have the following options:

  1. Write the function as an SSIS expression and make the calculation in a derived column. This can be less than ideal if the function was complex.

  2. Write a function in a script task and use a script transformation. This works well if the function is relatively simple, but isn't easily reusable.

  3. Create an assembly that you then reference in a script transformation. You could also use this underlying code to create a CLR function on the server. This makes it easier to manage the code, but it requires more overhead to implement.

  4. Load the data into a staging table in a database and then use the function when you try to merge the data into your final destination table. The benefit of this approach is that it is the easiest to implement. The downside of this approach is that you have to write the data to disk twice, so you are bound to get worse performance than if you used any of the other solutions.

Registered User
  • 8,357
  • 8
  • 49
  • 65
0

I'm guessing it's a scalar function in a MS sql database. Use it in the SQL Command in a Data Flow Source in a select statement.

Select dbo.myFunction(myParam)
From myTable
craday
  • 21
  • 3
0

Use the OLEDB Command Transformation, as per here:

SSIS return value of Stored Procedure within an OLE DB Command

Modify as required.

Community
  • 1
  • 1
Meff
  • 5,889
  • 27
  • 36
  • This can work, but it does not perform well. This basically calls the database for every individual row which definately won't scale. Am I missing something? – Registered User Sep 15 '11 at 23:09