从vb.net运行SSIS包的问题 [英] Issue with running SSIS package from vb.net
问题描述
Visual Studio 2005(VB.Net)& SQLServer 2005
我已经在SQL Server上创建了一个dtsx包，该包可以正常运行并将XML文件导入相关的atabase表，但是，从我的vb.net应用程序运行时，该表中写入了0行.我不知道为什么.我什至尝试提供从应用程序到SQL Server的连接，完整的管理权限，但仍然没有任何乐趣.我有一个从SSIS包写入的日志，该日志是在每次执行该包时创建的，没有错误显示.我还在vb应用程序上创建了一个文本日志来显示该函数的执行情况，但这没有显示任何错误.如果我继续使用SQL Server并使用SQL Server Business Intelligence Development Studio，然后运行该程序包，它将写入记录.谁能提出建议?
I have created a dtsx package on the SQL server which runs fine and imports an XML file into the relevant atabase tables, however, when running it from my vb.net application 0 rows are written to the table. I am at a loss as to why. I have even tried giving the connection from the application to the SQL server, full administrative rights , yet still no joy. I have a log written from the SSIS package which is created each time the package is executed, and no errors are showing. I have also created a text log fron the vb application to show the execution of the function, and this is showing no errors. If i go ont the SQL server and use the SQL Server Business Intelligence Development Studio, and run the package it writes the records,. Can anyone make any suggestions?
vb.net代码
导入 Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime
模块 XMLImporter
Module XMLImporter
公共 功能 ImportXMLFile( ByVal XmlFilename 为 字符串 ) 为 布尔值
Public Function ImportXMLFile(ByVal XmlFilename As String) As Boolean
writetolog( "导入xml开始时间 & 日期 .Now.ToString)
writetolog("Inport xml start time " & Date.Now.ToString)
暗淡 pkgLocation As 字符串 = "
Dim pkgLocation As String = ""
暗淡 应用 为 新建 应用程序
Dim App As New Application
暗淡 pkg As 新 包
Dim pkg As New Package
'加载packageName
' Load the packageName
pkgLocation = DTSXIMPORTERFULLPATH
pkgLocation = DTSXIMPORTERFULLPATH
writetolog( " file =" & XmlFilename)
writetolog(" file = " & XmlFilename)
'将默认重设为false
' set retun as false as default
ImportXMLFile = 否
ImportXMLFile = False
尝试
Try
pkg = App.LoadPackage(pkgLocation， 没什么 )
pkg = App.LoadPackage(pkgLocation, Nothing)
暗淡 Vars As 变量= 没什么
Dim Vars As Variables = Nothing
暗淡 myVariableDispenser 为 VariableDispenser = pkg.VariableDispenser
Dim myVariableDispenser As VariableDispenser = pkg.VariableDispenser
myVariableDispenser.LockForWrite( " FileName" )
myVariableDispenser.LockForWrite("FileName")
myVariableDispenser.LockForWrite( " OriginatingAccNo" )
myVariableDispenser.LockForWrite("OriginatingAccNo")
myVariableDispenser.LockForWrite( "PayDate" )
myVariableDispenser.LockForWrite("PayDate")
myVariableDispenser.LockForWrite( " ReportRef' )
myVariableDispenser.LockForWrite("ReportRef")
myVariableDispenser.LockForWrite( " TransCount" )
myVariableDispenser.LockForWrite("TransCount")
myVariableDispenser.LockForWrite( " TransValue" )
myVariableDispenser.LockForWrite("TransValue")
myVariableDispenser.GetVariables(Vars)
myVariableDispenser.GetVariables(Vars)
暗淡 myVar As 变量
Dim myVar As Variable
对于 每个 myVar 在 Vars
For Each myVar In Vars
writetolog(myVar.Name)
writetolog(myVar.Name)
下一个
Next
对于 每个 myVar 在 Vars
For Each myVar In Vars
选择 案例 UCase(myVar.Name.ToString)
Select Case UCase(myVar.Name.ToString)
大小写 " FILENAME"
Case "FILENAME"
myVar.Value = FTPDTSXIMPORTPATH& " \" & XmlFilename
myVar.Value = FTPDTSXIMPORTPATH & "\" & XmlFilename
案例 " ORIGINATINGACCNO"
Case "ORIGINATINGACCNO"
myVar.Value = ORICACCNUMBER.ToString
myVar.Value = ORICACCNUMBER.ToString
大小写 "PAYDATE"
Case "PAYDATE"
myVar.Value = MOVEDATE.ToString
myVar.Value = MOVEDATE.ToString
大小写 " REPORTREF"
Case "REPORTREF"
myVar.Value = REPORTSIDENTIFIER.ToString
myVar.Value = REPORTSIDENTIFIER.ToString
案例 " TRANSCOUNT"
Case "TRANSCOUNT"
myVar.Value = TRANSCOUNT.ToString
myVar.Value = TRANSCOUNT.ToString
大小写 " TRANSVALUE"
Case "TRANSVALUE"
myVar.Value = FILEVALUE.ToString
myVar.Value = FILEVALUE.ToString
结束 选择
End Select
下一个
Next
Vars.Unlock()
Vars.Unlock()
pkg.Execute()
pkg.Execute()
暗淡 dts_error As DtsError
Dim dts_error As DtsError
对于 每个 dts_error 在 pkg.错误
For Each dts_error In pkg.Errors
writetolog( "错误代码:" & pkg.Errors.Item(0).ErrorCode)
writetolog(" Errorcode : " & pkg.Errors.Item(0).ErrorCode)
writetolog( "组件:" & pkg.Errors.Item(0).SubComponent)
writetolog(" Componant : " & pkg.Errors.Item(0).SubComponent)
writetolog( "说明:" & pkg.Errors.Item(0).Description)
writetolog(" Discription : " & pkg.Errors.Item(0).Description)
下一个
Next
做
Do
'一直循环直到包完成
' just loop until package completed
writetolog( " pkg.ExecutionStatus =" & pkg.ExecutionStatus.ToString)
writetolog(" pkg.ExecutionStatus = " & pkg.ExecutionStatus.ToString)
循环 直到 pkg.ExecutionStatus<> DTSExecStatus.Executing
Loop Until pkg.ExecutionStatus <> DTSExecStatus.Executing
'检查错误
' check for errors
选择 案例 pkg.Errors.Count
Select Case pkg.Errors.Count
案例 0
Case 0
ImportXMLFile = 真实
ImportXMLFile = True
案例 其他
Case Else
ImportXMLFile = 否
ImportXMLFile = False
结束 选择
End Select
writetolog( "错误计数 & pkg.Errors.Count)
writetolog(" Error Count " & pkg.Errors.Count)
捕获 ex As 例外
Catch ex As Exception
writetolog( " DTSX上的错误 & ex.Message)
writetolog("Error On DTSX " & ex.Message)
ImportXMLFile = 否
ImportXMLFile = False
最后
Finally
pkg.Dispose()
pkg.Dispose()
结束 尝试
End Try
writetolog( "写入xml停止时间 & 日期 .Now.ToString)
writetolog("writing xml Stop time " & Date.Now.ToString)
结束 功能
End Function
结束 模块
End Module
推荐答案
嘿，彼得，有没有得到回应?
您知道SSIS现在严格来说是服务器产品，并且不允许仅使用对象模型进行远程编程执行吗? SSIS对象模型已发布(您正在使用的是什么?)，似乎可以远程连接并晒一个pkg，但是所有SSIS pkg(使用该对象模型的任何东西-包括您的VB解决方案)都必须运行在安装了SSIS的计算机上-充分利用对象模型公开的对象.金达很烂;我不喜欢DTS所做的一项更改(您可以在其中运行一些东西，从而从应用程序远程自动化DTS对象模型).这也解释了为什么它可以在BIDS上运行-不是因为您在BIDS中，而是因为它在已安装SSIS/已注册对象模型组件以供访问的服务器上.您是否在运行VB应用程序的计算机上安装了SS2K5?如果不是这样，当您尝试引用DTS对象时，该应用程序将崩溃(莫名其妙).方法返回一个值(类型为" DTSExecResult ")；我要做的第二件事是将其放在通话之前. 已取消"的I/O已完成"或失败"，至少为您提供了一个起点.
这是一篇有关新SSIS/对象模型下的各种远程/程序提交选项的好文章:
Michael Entin的笔记本-以编程方式运行SSIS程序包
Hey Peter, ever get a response on this?
You do know that SSIS is now strictly a server product and doesn't allow remote programmatic execution using just the object model? The SSIS object model is published (is that what you're using?), making it seem as though you could connect and sun a pkg remotely, but All SSIS pkgs (anything utilizaing that object model - including your VB solution) have to run on a machine that has SSIS installed - for full use of the objects exposed by the object model. Kinda sucks; one of the changes I don't like as a result of the swith to it from from DTS (where you could run stuff remotely automating the DTS object model from an app). This also explain why it works from BIDS - not because you're in BIDS, but because it's on the server where SSIS is installed/object model components are registered for access. Do you have SS2K5 installed on the machine that your VB app is running on? If not, the app will crash (inexplicably) when you try to reference the DTS objects.
The ".Execute" method returns a value (of type "DTSExecResult"); 2nd thing I'd do is stick that in front of the call. A "Cancelled" i/o "Completed" or "Failure", at least gives you a starting point.
Here's a good article on the various remote/programmatic submission options you have under the new SSIS/object model:
Michael Entin's Notebook - Running SSIS package programmatically
这篇关于从vb.net运行SSIS包的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
