Question problem with an import statement and running a SSIS package

itms

Active member
Joined
Feb 21, 2018
Messages
31
Programming Experience
10+
Hi,

I am trying to run an SSIS package from a VB.Net application. I looked on the net and found a number of examples that were all like the code below.
Assuming that the code works, I am not able to find, in VS 2017, the :Imports Microsoft.SqlServer.Dts.Runtime" statement; it seems to be obsolete. I tried looking in Refferences, but
I did not see anything.

Any ideas of what I can do. or is there another way to do this that would not need it?

Thanks you


VB.NET:
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class Form1
 
    Private Sub btnStart_Click(sender As System.Object, e As System.EventArgs) Handles btnStart.Click
        ' Instantiate SSIS application object
        Dim myApplication As New Microsoft.SqlServer.Dts.Runtime.Application()
 
        ' Load package from file system (use LoadFromSqlServer for SQL Server based packages)
        lblStatus.Text = "Loading package from file system."
        Dim myPackage As Package = myApplication.LoadPackage("D:\SSIS\MyPackage.dtsx", Nothing)
 
        ' Optional set the value from one of the SSIS package variables
        myPackage.Variables("User::myVar").Value = "test123"
 
        ' Execute package
        lblStatus.Text = "Executing package"
        Dim myResult As DTSExecResult = myPackage.Execute()
 
        ' Show the execution result
        lblStatus.Text = "Package result: " & myResult.ToString()
    End Sub
End Class
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
Hi,

I am trying to run an SSIS package from a VB.Net application. I looked on the net and found a number of examples that were all like the code below.
Assuming that the code works, I am not able to find, in VS 2017, the :Imports Microsoft.SqlServer.Dts.Runtime" statement; it seems to be obsolete. I tried looking in Refferences, but
I did not see anything.

Any ideas of what I can do. or is there another way to do this that would not need it?

Thanks you


VB.NET:
Imports Microsoft.SqlServer.Dts.Runtime
 
Public Class Form1
 
    Private Sub btnStart_Click(sender As System.Object, e As System.EventArgs) Handles btnStart.Click
        ' Instantiate SSIS application object
        Dim myApplication As New Microsoft.SqlServer.Dts.Runtime.Application()
 
        ' Load package from file system (use LoadFromSqlServer for SQL Server based packages)
        lblStatus.Text = "Loading package from file system."
        Dim myPackage As Package = myApplication.LoadPackage("D:\SSIS\MyPackage.dtsx", Nothing)
 
        ' Optional set the value from one of the SSIS package variables
        myPackage.Variables("User::myVar").Value = "test123"
 
        ' Execute package
        lblStatus.Text = "Executing package"
        Dim myResult As DTSExecResult = myPackage.Execute()
 
        ' Show the execution result
        lblStatus.Text = "Package result: " & myResult.ToString()
    End Sub
End Class
I have to ask the obvious, do you have Sql Server Data Tools (SSDT) installed on your machine?
 

itms

Active member
Joined
Feb 21, 2018
Messages
31
Programming Experience
10+
I have to ask the obvious, do you have Sql Server Data Tools (SSDT) installed on your machine?

Yes I do, but my question is that I cannot find this Import statement.
I mean I can bring the package into SQL server and run it from a stored Proc, and then call this procedure from Vb app.
I know be cause I have tried it, but that is as a big run around when this would be really direct.
 

JuggaloBrotha

VB.NET Forum Moderator
Staff member
Joined
Jun 3, 2004
Messages
4,524
Location
Lansing, MI; USA
Programming Experience
10+
Yes I do, but my question is that I cannot find this Import statement.
I mean I can bring the package into SQL server and run it from a stored Proc, and then call this procedure from Vb app.
I know be cause I have tried it, but that is as a big run around when this would be really direct.
Ok, that means you have the dll's installed on your system for use and what you need to do is add a reference to the Microsoft.SqlServer.ManagedDTS.dll file in your project, I don't know the path on your machine but here's a screenshot of it on my machine:

Microsoft.SqlServer.ManagedDTS.png

Microsoft.SqlServer.ManagedDTS_Reference.png

Then I can use your code like so:
Imports Microsoft.SqlServer.Dts


Public Class Form1

    Private Sub btnStart_Click(sender As Object, e As EventArgs) Handles btnStart.Click
        ' Instantiate SSIS application object
        Dim myApplication As New Runtime.Application()

        ' Load package from file system (use LoadFromSqlServer for SQL Server based packages)
        lblStatus.Text = "Loading package from file system."
        Dim myPackage As Runtime.Package = myApplication.LoadPackage("D:\SSIS\MyPackage.dtsx", Nothing)

        ' Optional set the value from one of the SSIS package variables
        myPackage.Variables("User::myVar").Value = "test123"

        ' Execute package
        lblStatus.Text = $"Package result: {myPackage.Execute().ToString()}"
    End Sub

End Class
 
Top Bottom