DTS from mapped drive problem

Hey guys

I am having a problem with a DTS package that pulls from a flat file off a mapped drive. When the package is ran alone, it runs perfectly but the stored proc that I took from an example from the net will not execute the DTS properly and I am unsure as to why it will not do so.

CREATE PROC spExecuteDTS
@.Server varchar(255),
@.PkgName varchar(255), -- Package Name (Defaults to most recent version)
@.ServerPWD varchar(255) = Null, -- Server Password if using SQL Security to load Package (UID is SUSER_NAME())
@.IntSecurity bit = 0, -- 0 = SQL Server Security, 1 = Integrated Security
@.PkgPWD varchar(255) = '' -- Package Password
AS

SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int, @.Cmd varchar(1000)

-- Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
PRINT '*** Create Package object failed'
EXEC sp_displayoaerrorinfo @.oPKG, @.hr
RETURN 1
END

-- Evaluate Security and Build LoadFromSQLServer Statement
IF @.IntSecurity = 0
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "' + SUSER_SNAME() + '", "' + @.ServerPWD + '", 0, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'
ELSE
SET @.Cmd = 'LoadFromSQLServer("' + @.Server +'", "", "", 256, "' + @.PkgPWD + '", , , "' + @.PkgName + '")'

EXEC @.hr = sp_OAMethod @.oPKG, @.Cmd, NULL

IF @.hr <> 0
BEGIN
PRINT '*** LoadFromSQLServer failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
PRINT '*** Execute failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Check Pkg Errors
EXEC @.ret=spDisplayPkgErrors @.oPKG

-- Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
PRINT '*** UnInitialize failed'
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

-- Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC sp_displayoaerrorinfo @.oPKG , @.hr
RETURN 1
END

RETURN @.ret
GO

that is the stored proc that i am using along with a couple error trapping ones but this being the one that does the actual execution. Is there anything i can change about this in order for it to run the DTS properly from the mapped drive?

thank youAre you getting an error message?|||Are you getting an error message?

*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.
*** LoadFromSQLServer failed
OLE Automation Error Information
sp_OAGetErrorInfo failed.|||Use the UNC path|||Does the login you are executing the OA_ stored procs as have permission to execute them?|||Wow

What to say

Usually people use DTS to avoid sprocs...but you're combing the 2

Why?

What does the sproc do?

Just load a flat file?

Why not just use bcp and xp_cmdshell?

DTS - Looping in a Data DrivenQuery

Hi Guys

I am reading a table one record at a time. Within this record a field can contain multiple values.
The delimiter is a ^. The data comes from a Pick legacy system
The data looks like this :-
chargeable_item_cd quantity text_1
I77C1^I77C2 1^1 PLATES /SCREWS^1.3MM SET

I want to extract the multiple values from this field and insert a record for each set of values.
I can unravel the data easy enough. The problem I have is how to loop within a DTS Activex
Script to store each of the values extracted from the field before moving onto the next record.
Is it possible or am I better of using a SQL task an taking a fraction of the time. (I am resisting
this as my boss doesnt like SQL code)
I am doing this within a data driven query.

Thanks in advance.

Cheers
Chris

The code I have so far looks like this(but doesnt work). It gives a "No query specification returnedby transform status".
'************************************************* **********
' Visual Basic Transformation Script
'************************************************* **********

' Copy each source column to the destination column
Function Main()
DTSDestination("DHB_Key") = DTSLookups("DHB Lookup").Execute(DTSGlobalVariables("DHB_Code").Value)
DTSDestination("Health_Encounter_Theatre_Key") = DTSSource("rule_violtd_cd")

DTSDestination("Patient_Key") = DTSLookups("Patient Lookup").Execute(left(DTSSource("admit_id"), 7))

DTSDestination("Patient_Care_Episode_Key") = _
DTSLookups("Patient Care Lookup").Execute( DTSDestination("Patient_Key"), _
"*T" + DTSSource("hosp_cd") + DTSSource("scout_sheet_nbr"), _
DTSDestination("DHB_Key"))

DTSDestination("Health_Encounter_Key") = DTSLookups("Hlth Encntr Lookup").Execute( DTSDestination("DHB_Key"), _
DTSDestination("Patient_Key"), _
DTSDestination("Patient_Care_Episode_Key"), _
"TH")
' This piece of code does it for multi field values for charagble items to individual values that can be stored in the database

' Copy each source column to the destination column
DIM string1, string2, string3, sitem, sqty, sdescript, quantity, descript
string1 = DTSSource("chargeable_item_cd")
string2 = DTSSource("quantity")

Do While InStr( string1 , "^") > 0
sitem = left(string1, InStr( string1 , "^") -1 )
sqty = left(string2, InStr( string2 , "^") -1 )
sdescript = left(string3, InStr( string3 , "^") -1 )

DTSDestination("Chargeable_Items_Key") = DTSLookups("Chargeable Items Lookup").Execute(sitem)
DTSDestination("Quantity") = sqty
DTSDestination("Description_Of_Item") = sdescript

If IsNull(sqty) Then
quantity = 0
Else
quantity = sqty
End If

If IsNull(sdescript) Then
descript = "X"
Else
descript = sdescript
End If

If NOT IsNull(DTSDestination("Chargeable_Items_Key")) Then
If DTSLookups("Item Exists Lookup").Execute(DTSDestination("DHB_Key"),_
DTSDestination("Health_Encounter_Theatre_Key"),_
DTSDestination("Patient_Key"),_
DTSDestination("Patient_Care_Episode_Key"),_
DTSDestination("Health_Encounter_Key"),_
DTSDestination("Chargeable_Items_Key"),_
quantity,_
descript) = 0 Then
Main = DTSTransformstat_InsertQuery
Else
Main = DTSTransformStat_SkipRow
End If
Main = DTSTransformStat_OK
End If

string1 = Mid( string1 , InStr( string1 , "^") + 1, Len( string1 ) )
string2 = Mid( string2 , InStr( string2 , "^") + 1, Len( string2 ) )
string3 = Mid( string3 , InStr( string3 , "^") + 1, Len( string3 ) )
loop

Main = DTSTransformStat_OK
End Functionokie, this is what I would do...
write 2 sub routines......

Sub InsertItems(strItems)
Dim arrItems, iItem
arrItems = split(strItems,"^")
for iItem = 0 to uBound(arrItems)
ExecuteSQL("insert into itemTable (column) values (" & arrItems(iItem) & ")"
next
End Sub

Sub ExecuteSQL(strSQL)
On Error Resume Next
Dim oConn, oRs, strConn
Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

strConn = "Your connection string goes here"
oConn.ConnectionString = strConn
oConn.ConnectionTimeout = 30
oConn.Open
oConn.execute(stqSQL
set oConn = nothing
On Error Goto 0
End Sub

in your main function call InsertItems and pass it the value of chargeable_item_cd (assuming I got the name right)

Make sense??|||Hi Rokslide

I take it that this will work inside the Activex Script Transformation Properties portion of a data driven query.

It is nearly knock off time Friday afternoon in NZ so I will try it on Monday morning
.
Cheers
Chris|||Hi Chris,

Your Activex Script can have as many extra functions or sub routines as you like with no problems providing there is only 1 main function.

Nearly knowing off time?? It's only 2pm! ;) It is Friday though and the lead in to a long weekend so... ;)

Where are you working in Christchurch? I used to live and work there myself. :)|||Hi

I am based at Princess Margret Hospital by under the Port Hills but
I live way out in the country about 10 kms the other side of Rangiora
on a 52 acre farmlet.

Cheers|||Cool. :) I used to work for the Council there and then started a company with a friend. Before living in Christchurch I was living out in Cheviot.

Mmmmm country air... ;)

Let me know if you need any more help with that DTS package.