November 7, 2005 at 5:04 am
I have written a User Defined Function (UDF) which accepts a string and converts it to ASCII.
In my DTS where my source is a text file and my destination is a table in SQL, I need to call this function which should convert one of my source columns to its ASCII equivalent and then copy it to my destination column. I am using an ActiveX script to try and achieve this task. But it fails! The function 'StrToAsc' works perfectly when called from a stored procedure or Query Analyzer.
Function Main()
DTSDestination("pid") = dbo.StrToAsc(DTSSource("Col001"))
Main = DTSTransformStat_OK
End Function
Please help!!
November 7, 2005 at 12:44 pm
You need to create a Lookup.
The SQL in the lookup will be:
SELECT dbo.StrToAsc(?)
'?' is a parameter placeholder, that will take the value passed in from your VBScript code. You invoke the lookup using the name of the Lookup and the .Execute method. Assuming you call your DTS lookup "LkpASCII", you'd call it like this in your VBScript code:
DTSDestination("pid") = DTSLookups("LkpASCII").Execute(DTSSource("Col001"))
November 7, 2005 at 11:30 pm
Hi, Thanks for your reply.
I did exactly as per your instructions, but I get this error:
ADO error: Syntax error or access violation
when I specify the SQL for the lookup: SELECT dbo.StrToAsc(?) What could be the cause? It expects me to give a string instead of ?.
Please, please HELP
November 8, 2005 at 3:31 am
Your problem is that the UDF is only available through a SQL Server connection. I think you have two options.
1. Re-write the UDF as a vbscript function and then paste this function into all of your vbscript transformations where this is required.
2. Import your text file 'as is' into a working table within SQL and then create a second data pump task from your working table to your destination table. In this data pump task you can use a SQL statement using ure UDL as you do in Query Analyzer.
Daniel
November 8, 2005 at 3:52 am
Daniel, I'll try the second option. Many Thanks to you and PW. Any further ideas/suggestions are still welcome.
August 18, 2006 at 9:39 am
Hello.
I had the exact same problem.
Solution: created a stored procedure which calls the user-defined function. Then, call the stored procedure from the Lookup Query (exec SPNAME ?)
(not very elegant, but worked at the first try... up to you)
Cheers,
Tiago
ps. if someone has a better suggestion, please don't hesitate =)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply