How to run an SSIS package from Visual Basic 6?

Question

Can anyone provide some information on how to run an integration services package, on an SQL server 2005 instance, from Visual Basic 6?

Any help at all is much appreciated.

Answer 1

Probably easiest is to shell out and run dtexec

Alternatively, you could use the using Microsoft.SqlServer.Dts assemblies in .NET and wrap it in COM (or an EXE) and then call it from VB6.

That second link has some other options which might apply to VB6 in some way, but would probably all result in wrapping the .NET code in a COM object or an EXE.

Answer 2

can you please tell me an example because i can't apply what you are saying?

Answer 3

You need to have xp_cmdshell enabled, if it's not, execute: sp_configure 'xp_cmdshell', '1' in SSMS on your server.

Create a store procedure, and put the following code in it:

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

Then, call the stored procedure from VB6, via an ADODB.Command.

You have examples here:

• SSIS: http://www.mssqltips.com/tip.asp?tip=1395
• ADODB.Command: http://www.codeproject.com/KB/vbscript/simple%5Fsp%5Fvb6.aspx

I hope it helps.