March 18, 2009 at 8:22 pm
Hi all,
I'm trying to test upgrading our existing administration packages. The one I'm having a problem with right now is one that I have programmatically connecting out to our systems to gather WMI data.
The package fails when it hits that script task.
Here is a portion of the code:
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
.
.
.
Public Sub Main()
'Error handling is captured within each sub routine listed below
'Refresh package error handling variable
Dts.Variables("User::errorsFromScript").Value = ""
'Execute Subs to Gather Information
GetRAM_And_SystemModel()
GetNumberOfProcessors()
GetProcessorModelInformation()
GetSAN_YesNo()
GetOSVersionInformation()
GetIPAddresses()
'TEST()
' Catch any errors that were not previously reported.
If m_errorMessage <> "" Or Err.Number <> 0 Then
Dts.Variables("User::errorsFromScript").Value = m_errorMessage
'MsgBox(Dts.Variables("User::errorsFromScript").Value.ToString())
End If
Dts.TaskResult = ScriptResults.Success
End Sub
Private Sub GetRAM_And_SystemModel()
Try
'Get computer name from package variable
Dim strComputer As String
strComputer = m_serverName
'connect to computer object
Dim objWMIService As Object = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")
'Gather system data
Dim colSettings As Object = objWMIService.ExecQuery _
("Select * from Win32_ComputerSystem")
For Each objComputer As Object In colSettings
'MsgBox(("System Name: " & objComputer.Name))
'MsgBox(("Total Physical Memory: " & _
' objComputer.TotalPhysicalMemory))
Dts.Variables("User::RAM").Value = CType(Round((CType(objComputer.TotalPhysicalMemory, Decimal) / 1024 / 1024 / 1024), 0), String) & "GB"
Dts.Variables("User::systemModel").Value = objComputer.Manufacturer & " " & objComputer.Model
Next
Catch ex As Exception
m_errorCnt += 1
m_errorMessage = m_errorMessage & "Error #" & CStr(m_errorCnt) & ": " & vbCrLf & "GetRAM_And_SystemModel failed at " & m_serverName & ". Error details: *** " + ex.Message & vbCrLf & vbCrLf
End Try
End Sub
... the other subs are here, but are formatted similar to that one.
I'm not sure if it doesn't like creating the objects on the fly, but it doesn't even seem to try.. it just fails.
I also know there is the WMI object in the toolbox that I could use instead of a script task, but I'm not trying to re-write my package.
Any thoughts or other experiences with upgrading script tasks would be welcomed!
Regards,
Steve :w00t:
March 19, 2009 at 9:46 am
One important part of the code that I didn't post was causing the issue:
...Imports...
#Region " Global Variables "
Dim m_errorMessage As String = ""
Dim m_errorCnt As Integer = 0
Dim m_serverName As String = Dts.Variables("User::serverName").Value.ToString()
#End Region
...Main Sub...
Apparently, in VS 2008 you cannot assign variable values outside of a function, but the error message just simply says "Cannot load script for execution."
Here is the "fix":
#Region " Global Variables "
Dim m_errorMessage As String
Dim m_errorCnt As Integer
Dim m_serverName As String
Private Sub SetGlobalVariables()
m_errorMessage = ""
m_errorCnt = 0
m_serverName = Dts.Variables("User::serverName").Value.ToString()
End Sub
#End Region
Public Sub Main()
'Error handling is captured within each sub routine listed below
SetGlobalVariables()
.
. 'rest of script contents here
.
End Sub
Hopefully this might help someone else- took a few hours and a lot of msgbox's to determine where the script was failing.
September 9, 2010 at 6:59 pm
Thank you very much for posting this fix. I also ran into the same issue with an SSIS script task 2005 to 2008R2 upgrade.
September 10, 2010 at 10:57 am
From what I understand, the script task in SS 2005 is VbScript and in 2008 it is full fledged VB. I'm not sure of the ramifications of that but it might point you in the right direction.
Steve
EDIT:
Sorry, I posted this before I saw that a solution was available.
January 3, 2012 at 12:39 am
Hi S. Kusen,
Thanks buddy. It helped to have quick fix.
How did you interpret the error message, when it said "script file can not load for execution"?
What are the various possibilities to get this message.
I am the beginner, but worked on 2005 and now I am migrating to 2008 those which developed in 2005.
Veer
January 3, 2012 at 2:34 pm
veeresasai (1/3/2012)
Hi S. Kusen,Thanks buddy. It helped to have quick fix.
How did you interpret the error message, when it said "script file can not load for execution"?
What are the various possibilities to get this message.
I am the beginner, but worked on 2005 and now I am migrating to 2008 those which developed in 2005.
Veer
I basically tore down the script I had in there and just went block by block to see where the error suppressed. When I pulled out everything, and it ran "successfully", I then added back in the variable assignments, and then it broke again. It was really a trial and error to get it functioning. It's not a great answer, but unfortunately that's what it takes sometimes in this industry! 😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply