sql server dts: I am using the following VB.NET code to pull queries programmatically from an
SMS Server and dump the output the an XML file. This data is later used in
multiple ways, one of which is dumping to a SQL database. This runs just
fine if run by itself, but I would like to be able to run all of these in an
SSIS package. I have 11 different scripts like for different SMS queries.
When run alone there is no problem, but when run via SSIS the first one runs
fine and all others bomb out with an error that says, "The Script Task threw
an exception: unspecified error." If I break all of these out to separate
packages and run the packages at the same time via SQL Server Agent all of
them complete without a problem. I can't seem to figure out what the problem
is, and I can't get any more detailed error info. Could it be the use of COM
(Microsoft WMI Scripting v1.2 Library)?


' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports System.Xml
Imports Microsoft.SqlServer.Dts.Runtime
Imports WbemScripting


Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables,
events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to
indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
Dim Services As SWbemServices
Dim Locator As New SWbemLocator
Dim SMSProviderServer As String
Dim SMSProviderSitecode As String
Dim writerSettings As New XmlWriterSettings
writerSettings.Indent = True
writerSettings.IndentChars = " "

' Get a new Services interface
' using the local SMS namespace path. User and password are only
required if you
' are connecting remotely as a different user.

SMSProviderServer = "XxXxXSMS02"
SMSProviderSitecode = "XxX"

Services = Locator.ConnectServer(SMSProviderServer, "root\sms\site_"
& SMSProviderSitecode)

Dim Query As SWbemObject

Dim QueryID As String = "XxX00013" ' SMS Query ID
Query = Services.Get("SMS_Query.QueryID='" + QueryID + " '")

Dim Result As SWbemObject
Using writer As XmlWriter = XmlWriter.Create("C:\SMS_Reports\ENTNEED
XP Upgrade.xml", writerSettings)
writer.WriteStartDocument()
writer.WriteStartElement("ENTNeedXPUpgrade")


For Each Result In Services.ExecQuery(Query.Expression)
' SystemOUName is an array/collection.
writer.WriteStartElement("Computer")
writer.WriteElementString("ComputerName", Result.Name)
If Result.Client Is DBNull.Value Then
writer.WriteElementString("Client", String.Empty)
Else
writer.WriteElementString("Client", Result.Client)
End If
If Result.Active Is DBNull.Value Then
writer.WriteElementString("Active", String.Empty)
Else
writer.WriteElementString("Active", Result.Active)
End If
If Result.LastLogonUserName Is DBNull.Value Then
writer.WriteElementString("LastLogonUserName",
String.Empty)
Else
writer.WriteElementString("LastLogonUserName",
Result.LastLogonUserName)
End If
If Result.OperatingSystemNameAndVersion Is DBNull.Value Then

writer.WriteElementString("OperatingSystemNameAndVersion", String.Empty)
Else

writer.WriteElementString("OperatingSystemNameAndVersion",
Result.OperatingSystemNameAndVersion)
End If
Try
If Result.SystemOUName(0) Is DBNull.Value Then
writer.WriteElementString("Division", String.Empty)
Else
writer.WriteElementString("Division",
Result.SystemOUName(0))
End If
Catch ex As Exception
writer.WriteElementString("Division", String.Empty)
End Try
Try
If Result.SystemOUName(1) Is DBNull.Value Then
writer.WriteElementString("Region", String.Empty)
Else
writer.WriteElementString("Region",
Result.SystemOUName(1))
End If
Catch ex As Exception
writer.WriteElementString("Region", String.Empty)
End Try
Try
If Result.SystemOUName(2) Is DBNull.Value Then
writer.WriteElementString("OU", String.Empty)
Else
writer.WriteElementString("OU",
Result.SystemOUName(2))
End If
Catch ex As Exception
writer.WriteElementString("OU", String.Empty)
End Try
writer.WriteEndElement()
writer.Flush()

Next
writer.WriteEndElement()
writer.WriteEndDocument()

End Using
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

--

Hello Adam,

I would like to know how you configure to run the scripts in one package?

Did you execure all scripts in a sequence?

One workaround may be that you could run several Packages in the SSIS.

Sincerely,

Wei Lu

Microsoft Online Community Support

==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

I have tried it multiple different ways including:
1. All script tasks in single package running at the same time.
2. All script tasks in single package running one after the other with data
flow tasks in between dumping info to tables.
3. Scripts in different packages with a "master" package calling Execute
Package Tasks.

The result is the same across all scenarios.

Thanks

--
Adam S


[quoted text, click to view]