pl. Help... DTS Package

[ksraju6]

Thanks in advance.

I have a strange doubt.

when I run the DTS package thru vb , Do i need to have sqlserver client ???

If not , how the dll communicate with SQL server.

DO i need to use ODBC connectivity ??

Pl.. help

Raj

 

[softwarescience]

Hello

In essence Yes. If you are using the DTS object model then you will need to distribute it and the simplest way is the client.

If you are running a remote DTS proccess then you will need ODBC connectivity to the server. This just needs the SQL ODBC drivers (again on the client install).

Hope this helps

 

[ksraju6]

Hi softwarescience ,

Thanks for your reply..


my problem is here is my code and it doesnot have any ODBC connectivity.I need to install this application on user system who doesnot have sqlserver client.

Public Function ExecutePackage() As Boolean
On Error GoTo errdts
Dim oPKG As DTS.Package, oStep As DTS.Step
Set oPKG = New DTS.Package

Dim Sserver As String, sdatabase As String, sUsername As String, sPassword As String
Dim sPackageName As String, sMessage As String
Dim lErr As Long, sSource As String, sDesc As String

' Set Parameter Values
Sserver = FrmSelectDatabase.Cmbdatabase.Text
sdatabase = "DCRVW000"
sUsername = ""
sPassword = ""
sPackageName = "Membership_load"

' Load Package
oPKG.LoadFromSQLServer Sserver, "", "", DTSSQLStgFlag_UseTrustedConnection, "", "", "", sPackageName, ""



' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

' Display Results
MsgBox sMessage
Exit Function
errdts:
MsgBox "You are not valid User for " & Sserver
End Function

How does the user system connects to SQLserver ??
can you advice me in this aspect

Raj

 

[softwarescience]

Hello

The problem is that you need the 'dtspkg.dll' file which is the main DTS dll. This file can only be found in the client install of SQL.

You could try Microsofts web site to see if they distribute a cut down version of their client but I think you'll find that you have no other option but to install SQL Server client on each machine that needs to run the DTS package.

PS You're right in that you don't need any ODBC connectivity, it's just that one file 'dtspkg.dll'(and its dependencies)