SQL DTS Question

[Wallegator]

I want to launch a DTS in a VB 6 application. I have several questions.

1. Where do I put the VB code that SQL saved for me in my application? Should it reside in a module, class module, or behind the button that I will use to launch the DTS. Where do I call the code from?

2. Do I need to add any components to my VB application in order allow the SQL generated code to run. Currently I've added it to a module and am launching it from Sub Main. It is giving me a Type error.

Thank you in advance for any light you can shed on this area.

 

[cmcgann]

Hi,

Ive done this before with some problems,

I didnt use the SQL generated VB code.
I called the DTS package on the server directly from VB.
I addeed a reference to the DTS object Library dtspkg.dll.

I then executed it by calling the function below.
I added a List View on the Form to display the results ( You can remove the code that does this)

Private Sub Form_Load()
lvDTS.View = lvwReport
lvDTS.ColumnHeaders.Add 1, "col1", "Step"
lvDTS.ColumnHeaders.Item(1).Width = 1000
lvDTS.ColumnHeaders.Add 2, "col2", "Step Name"
lvDTS.ColumnHeaders.Item(2).Width = 5620
lvDTS.ColumnHeaders.Add 3, "col3", "Step Result"
lvDTS.ColumnHeaders.Item(3).Width = 1200
End Sub





'Function Call
ExecutePackage sServerName, sServerPassword, "", sDTSPackageName


'Function

Public Sub ExecutePackage(sServer As String, sUsername As String, sPassword As String, sPackageName As String)
'<EhHeader>
Dim lErrNum As Long
Dim sErrDesc As String

On Error GoTo ErrorBlock:
'</EhHeader>

Dim oPKG As DTS.Package, oStep As DTS.Step
1000 Set oPKG = New DTS.Package

Dim sMessage As String
Dim lErr As Long, sSource As String, sDesc As String
Dim iListCount As Integer
Dim lsitem As Object
Dim sStepName As String
Dim sStepResult As String

' Load Package
1001 oPKG.LoadFromSQLServer sServer, sUsername, sPassword, _
iConnectionType, , , , sPackageName
1002 frmRunning.Refresh



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

Next

' Execute
1005 oPKG.Execute

' Get Status and Error Message
1006 For Each oStep In oPKG.Steps
1007 iListCount = iListCount + 1
1008 If oStep.ExecutionResult = DTSStepExecResult_Failure Then
1009 frmRunning.Label1.Caption = &quot;Sending E-Mail&quot;
1010 oStep.GetExecutionErrorInfo lErr, sSource, sDesc
1011 sMessage = sMessage & &quot;Step &quot;&quot;&quot; & oStep.Name & _
&quot;&quot;&quot; Failed&quot; & vbCrLf & _
vbTab & &quot;Error: &quot; & lErr & vbCrLf & _
vbTab & &quot;Source: &quot; & sSource & vbCrLf & _
vbTab & &quot;Description: &quot; & sDesc & vbCrLf & vbCrLf
1012 sStepResult = &quot;Failed&quot;
1013 sStepName = oStep.Name
1014 MsgBox sMessage, vbCritical, App.Title
1015 gLogEmail.Log sMessage, True
1016 sMessage = &quot;&quot;
Else


1018 sStepResult = &quot;Succeeded&quot;
1019 sStepName = oStep.Name

End If
1020 sMessage = RemoveUnwantedChars(sMessage)
1021 Set lsitem = frmMain.lvDTS.ListItems.Add(, &quot;a&quot; & iListCount, iListCount)
1022 lsitem.SubItems(1) = sStepName
1023 lsitem.SubItems(2) = sStepResult
Next

1024 oPKG.UnInitialize

1025 Set oStep = Nothing
1026 Set oPKG = Nothing



'<EhFooter>
Exit Sub

ErrorBlock:
lErrNum = Err.Number
sErrDesc = Err.Description

Dim sErrDetail As String
Dim lErrLine As Long
lErrLine = Erl
sErrDetail = &quot;Error Description: [&quot; & sErrDesc & &quot;] - Error Number: &quot; & lErrNum & &quot; - &quot; & &quot; (RedHatWebOrders.mDTS.ExecutePackage) &quot; & &quot; Line &quot; & CStr(lErrLine)
MsgBox sErrDetail, vbCritical, App.Title
'</EhFooter>
End Sub


This should work fine for you. You can customise it yourself.

One thing to note though, you have to have SQL Client Tools installed on the PC that runs this VB app. I found this after a lot of hassle, on Microsfts Tech Site.

Hope this helps.

Off home now.