实在没办法了,大家来帮帮忙,一个关于SSIS的问题,大家来看一下.......
在SSIS中写了一个vb script,功能是把一个特定的值赋给EXCEL的一个特定的格子,测试OK,单独运行SSISok.
但当我把这个task放到SQL Server Job Agent中去跑的时候,得到如下BUG:
System.Exception: Cannot create ActiveX component. at Microsoft.VisualBasic.Interaction.GetObject(String PathName, String Class)
我查过了,不是什么用户权限的问题,其他的SSIS job都可以正常地在SQL Server Job Agent下运行.以往也遇到过单独运行ok,一放到sqlserver agent就报错的情况,但这个问题已经修复,我还试过通过在agent中调用sqlserver store的方式调用ssis,依旧报错.
下面是我的vb代码
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic 2008.
' The ScriptMain is the entry point class of the script.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Excel = Microsoft.office.interop.excel
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
Public Sub Main()
Dim mybook As Excel.Workbook
Dim mysheet As Excel.Worksheet
'Dim Excel As Microsoft.Office.Interop.Excel.Application()
Dim batch_no As Integer = Val(Dts.Variables("batch_no").Value)
Dim new_batch_no As String = "00000" + Str(batch_no + 1)
mybook = GetObject("\\sydnas1\schenkeredi\Transport\SapphireAU\ExternalCarrier\RossFreight\EDIOUT\test.xls")
'Excel = GetObject(, "Microsoft.Office.Interop.Excel.Application")
mysheet = mybook.Sheets(1)
mysheet.Cells(1, 3).Value = Right(Replace(new_batch_no, " ", ""), 5)
mybook.Save()
mybook.Windows(1).Visible = True
mybook.Close(True)
mysheet = Nothing
mybook = Nothing
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
