如何从Visual Basic 6运行SSIS包?

你需要有 xp_cmdshell 已启用,如果未启用,则执行: sp_configure 'xp_cmdshell', '1'

DECLARE @SSISPackage VARCHAR(1000)
DECLARE @cmd VARCHAR(max)
DECLARE @variable_value1 varchar(255)
DECLARE @Result int

SET @SSISPackage = 'C:\path_to_the_package\package_name.dtsx'
SET @cmd = 'dtexec /F "' + @SSISPackage + '"'   
// if you have variables in the package that need to be set up
// add them here
SET @cmd = @cmd + ' /SET \Package.Variables[User::somevariable1].Properties[Value];"' + @variable_value1 + '"'
SET @cmd = @cmd + ' /SET \Package.Variables[User::somevariable2].Properties[Value];"' + @variable_value2 + '"'
SET @cmd = @cmd + ' /SET \Package.Variables[User::somevariable3].Properties[Value];"' + @variable_value3 + '"'

EXECUTE @Result = master..xp_cmdshell @cmd, NO_OUTPUT

然后,通过ADODB.com命令从VB6调用存储过程。

这里有一些例子:

• SSIS http://www.mssqltips.com/tip.asp?tip=1395
• : http://www.codeproject.com/KB/vbscript/simple_sp_vb6.aspx

创建调用包的存储过程:

2. 在执行包窗口中,单击“脚本”按钮。

3. 添加到生成的脚本的顶部:

   CREATE PROCEDURE sp_NewProcedureName
   AS
   BEGIN
   

4. 并添加到生成的脚本的底部:

   END
   GO
   

然后使用代码调用存储过程

6. 这个代码对我有用:

   Set adodbConn = New ADODB.Connection
   adodbConn.Open "Provider=sqloledb; Data Source=yourSQLServer; Initial Catalog=yourDB; Integrated Security=SSPI;"
   Set adodbCMD = New ADODB.Command
   With adodbCMD
       .ActiveConnection = adodbConn
       .CommandText = "dbo.sp_NewProcedureName"
       .CommandType = adCmdStoredProc
       .CommandTimeout = 0
       .Execute
   End With
   Set adodbCMD = Nothing
   adodbConn.Close
   Set adodbConn = Nothing
   

我知道这是一个老问题的答案,但希望我的答案能把所有的东西都集中在一个地方。

也许最简单的方法就是掏钱买东西 run dtexec

或者,你可以 use the using Microsoft.SqlServer.Dts assemblies NET中,并将其包装在COM(或EXE)中,然后从VB6调用它。