VB code to access SSIS package

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

VB code to access SSIS package

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-05-05 : 14:03:40

One of my clients has moved a dts package to dtsx. (2000->2005). But they are facing the problem calling it from VB.

Following is the code. I don’t know much about executing the DTS from VB. Can someone please give me the equivalent of following code for SSIS?

Dim oPkG As DTS.Package
    Set oPkG = New DTS.Package

    oPkG.LoadFromSQLServer gServer, gUserID, gPwd, _
        DTSSQLStgFlag_Default, , , , sPackageName

    oPkG.Execute
    oPkG.UnInitialize
    Set oPkG = Nothing

------------------------
I think, therefore I am - Rene Descartes

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-05 : 14:43:50

"Moved" meaning imported the file (DTS package) to SQL 2005 server or using Import Wizard which trying to convert DTS to SSIS? If the code is now SSIS the there's a way to run the package without using VB code (better solution).

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-05-05 : 14:47:46

I think the code is now SSIS. What is the better solution.
Thank you.

------------------------
I think, therefore I am - Rene Descartes

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-05 : 16:08:19

Execute Package Task is the simplest solution to execute other packages.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-05-05 : 16:31:48

How to do that from VB?

------------------------
I think, therefore I am - Rene Descartes

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-05 : 17:35:44

You don't need to use VB script to run it. On Control Flow, use Execute Package Task (just configure it) to run other package(s). Do you really need to run other packages from VB script?

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-05-05 : 17:39:42

quote:
Originally posted by rgombina
Do you really need to run other packages from VB script?

Yes. Infact it is VB and not VBScript. The app decides when to execute this package.

------------------------
I think, therefore I am - Rene Descartes

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-05-05 : 18:14:56

You can use the dtexec command to execute the package from a VB script. Check this link. You can also create a CMD file and execute the package using dtexec from that.

http://code.msdn.microsoft.com/dtexecRemote

EDIT : Check the comments section in the link. Thats the method we use here.

quote:

Yes. Infact it is VB and not VBScript. The app decides when to execute this package.

------------------------
I think, therefore I am - Rene Descartes

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-05 : 21:33:46

VB script (not VBScript) I mean is VB.NET script which is coding used in Script Task. Anyhow, depending on design you can execute "Execute Package Task" using contraints (green line). If you could give us some idea of what you're looking for there are options of solutions. Most of the time using tasks provided by IDE are most useful and better solution than scripting it.

I'm just trying to make things easier or give a possible solution.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2009-05-05 : 23:56:53

quote:
Originally posted by vijayisonly
EDIT : Check the comments section in the link. Thats the method we use here.
Yes. Infact it is VB and not VBScript. The app decides when to execute this package.

Thank you. This is an interesting option.

------------------------
I think, therefore I am - Rene Descartes

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-05-06 : 08:51:33

Here's an equivalent code from DTS to SSIS:


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
    Public Sub Main()
        Dim oApp As Microsoft.SqlServer.Dts.Runtime.Application = New _
                                    Microsoft.SqlServer.Dts.Runtime.Application()
        Dim oPkg As Microsoft.SqlServer.Dts.Runtime.Package

        oPkg = oApp.LoadPackage("E:\temp\Package27.dtsx", Nothing) '//You can read from SQL server too

        Dim vars As Variables

        oPkg.Variables.Add("execPackage27", False, "", "Package27 executed...")

        oPkg.Execute() '//Execute Package

        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources