udf from dts 1

[lucyv]

Is it possible to call a user defined function from a DTS package??

Thanks for any help!

 

[SQLDenis]

yes, whatever you can use in Query Analyzer you can use in an execute SQL task



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lucyv]

How would I reference it... This is what I have but I am not having any luck

Function Main()
DTSDestination("isbn") = fn_leos_ISBNconv(DTSSourc"Col001"))

Main = DTSTransformStat_OK
End Function

Where fn_leos_ISBNconv is the function.

Thanks for your help!!

 

[SQLDenis]

go to the source tab
if you have table or view then swith to sql query write out your fields and put dbo.fn_leos_ISBNconv(FieldNameHere) around your field

you are going from a table to a text file right?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lucyv]

I am going from a text file t a table but I an to run the function on one of the columns. I am in the Active X Script Transformation window... I don't see a source tab.

Thanks!!

 

[SQLDenis]

Are you importing with a Transform Data Task?
Double-click on the black line and it will pop up
It doesn't matter really since you can't do it like this if you import from a text file
import into a staging table then apply your function when you insert into the 'real' table

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lucyv]

Yes I am using Transform Data Task. So I guess I cannot access the function through the Active X script?

Thanks for all your help!

 

[SQLDenis]

you can if you are exporting from table to text file not the other way around

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[lucyv]

Thanks again for all your help!!!