Executing Database Functions in DTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Executing Database Functions in DTS

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-05-09 : 08:24:33

Ryan writes "Here is the scenario...

In DTS, I retrieve multiple flat files from an FTP and save them on the network. I then need to load those files into a table using the "Transform Data Task". I want to transform two columns using a database function that exists in the destination database.

Is that possible?"

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2005-05-09 : 12:46:02

Hey,

I just tried with your example. Hope you cant directly use the user defined function while transform the data from a Text file to Database table.

May be you can do in 3 ways ...

1. Take source file dump into table and do one more additional step to update the data. Like Update Table set Col1 = dbo.TestFunction(Col1) ....

2. Write the same functionality into VBScript and save it as VBS file and use it into your package

3. See the code, but in terms of performance it is too costly :-)

Check it out:

-- Step1: Create a Text file with Eno, Ename, Dob, Salary, Status columns and atleast 25 records.
-- Step2: Create a Dummy Table EmpDump with same columns
-- Step3: Create a User defined function

CREATE FUNCTION [dbo].[TestFunction] (@strName varchar(25))
RETURNS varchar(25) AS
BEGIN
Declare @strTemp varchar(25)
set @strTemp = upper(@strName)
Return(@strTemp)
END

'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()

Dim adConn
Dim sSql
Dim rs
Dim strValue

DTSDestination("Eno") = DTSSource("Col001")

Set adConn = CreateObject("ADODB.Connection")
adConn.Provider = "SQLOLEDB"
adConn.Properties("Data Source").Value = "LT-FLOATER2\LOCAL"
adConn.Properties("Initial Catalog").Value = "PUBS"
adConn.Properties("Integrated Security").Value = "SSPI"
adConn.Open

Set rs=CreateObject("ADODB.Recordset")
rs.ActiveConnection = adConn

sSql="select dbo.TestFunction('" + DTSSource("Col002") + "')"
Set rs = adConn.Execute(sSql)

rs.MoveFirst
strValue=rs.Fields(0).Value

DTSDestination("Ename") = strValue
DTSDestination("dob") = DTSSource("Col003")
DTSDestination("salary") = DTSSource("Col004")
DTSDestination("status") = DTSSource("Col005")

Main = DTSTransformStat_OK

End Function

With Regards
Sreenivas Reddy B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources