Running A SSIS/BIDS Package From VB.Net

[Skittle]

Since BIDS SSIS Packages are created using Visual studio, I assume there is a way of executing a stand alone BIDS package from VB.NET.

Has anybody figured out how to do this?
I want to be able to call a BIDS package that is stored in the file system, completely independant of SQL Server.



Dazed and confused

 

[AlexCuse]

This has examples of how to load a package both from the file system and SQL Server. Its' in C#, but you should be able to figure it out from here.

http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Skittle]

Its a good start, thanks.

I have one follow up question.
Once I have created an executable, will it run regardless of wether or not SQL server is installed on the target run machine?

I notice that the Microsoft.SQLServer.ManagedDTS.dll needs to be in place at code time but is it needed at run time?

Dazed and confused

 

[AlexCuse]

That is a good question, and one that I don't know the answer to.

You might try referencing the .dll as if it were an external reference, and including it in your application?

It that doesn't work, you might need to put deploy the package to SQL Server and execute it through a stored procedure.

Depending on what objects you're using in the package, it might be possible to do it all through ADO.net and SQL as well.

I've found that installing the SQL 2005 backwards compatibility components allowed me to run "old school" DTS packages on machines without SQL Server installed, so I'm wondering if there is a way you can install only the SSIS runtime on the target machines?

Please post back what you find out, this is an issue that's very near and dear to me.

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Skittle]

Sorry for the delay in responding.

Work etc.

I can't get anywhere with this because the namespace Microsoft.SqlServer.Dts.Runtime is not recognised on my machine. Haven't figured out why yet.

Dazed and confused

 

[AlexCuse]

You may need to install the "Backwards Compatibility Components" from here.

This will give you the DTS runtime and a few other things(FWIW, you can also install this on other machines that you will need to run the package from. But where it gets hairy is permissions, because the package runs under the user's permissions).

If you don't want to install that, here is a list of the different files required:

http://www.sqldts.com/225.aspx

(should be enough to change your mind ;-) )

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Skittle]

Good link that discusses many of the isues.

http://blogs.msdn.com/michen/archive/2007/03/22/running-ssis-package-programmatically.aspx

Ultimately I wanted to use packages as a simple, quick alternative to writing client upload/download/transformation programs in .Net.
The problem is that you need the SSIS Runtime on the client computer to do this - and this needs a licence (.

So the only way in my case would be to run the transformation on SQL Server by remotely calling it from the client. You mentioned a stored procedure my be the way and I think you're right.

I had hoped SSIS would be something that you could use without having to code .Net around it or the need for SQL Server but alas, that would be just to perfect a world.




Dazed and confused

 

[JustinEzequiel]

Sorry for hijacking a thread but...

(cross-posted at

http://tinyurl.com/24cgrq

and at

http://tinyurl.com/22v4pf)

How come the C# version succeeds when the VB version fails?

(I adopted the C# version from

http://www.codeproject.com/KB/database/CallSSISFromCSharp.aspx)

' VB
Imports System
Imports Microsoft.SqlServer.Dts.Runtime

Module Module1

    Sub Main()

        Dim app As New Application()
        Dim package2 As Package
        Dim result2 As DTSExecResult

        package2 = app.LoadFromSqlServer( _
            "\OHBilling", "C2130001312", "billing", "billing",
Nothing)

        package2.ImportConfigurationFile("D:\Program Files\Microsoft
SQL Server\90\DTS\Packages\SSISOHBilling\OHBilling.dtsConfig")

        result2 = package2.Execute()

        Console.WriteLine("Package Execution results: {0}", _
            result2.ToString())

    End Sub

End Module

//C#
using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace cs_console_SSIS
{
    class Program
    {
        static void Main(string[] args)
        {
            Application app = new Application();
            Package package2;
            DTSExecResult result2;

            package2 = app.LoadFromSqlServer(
                "\\OHBilling", "C2130001312", "billing", "billing",
null);

            package2.ImportConfigurationFile("D:\\Program Files\
\Microsoft SQL Server\\90\\DTS\\Packages\\SSISOHBilling\
\OHBilling.dtsConfig");

            result2 = package2.Execute();

            Console.WriteLine("Package Execution results: {0}",
                result2.ToString());
        }
    }
}

 

[Sorwen]

How it fails would be helpful. Let me say this that vb.net doesn't have a main like c++/c# so you can't just name a sub main and have it run.

-I hate Microsoft!
-Forever and always forward.

 

[earthandfire]

Sorwen, you can use Sub Main as the start up in VB - you just need to change the Application properties.

Hope this helps.

 

[Sorwen]

Ah, my bad then. I didn't know you could change that anywhere. I knew that by default there was no way that was going to work.

-I hate Microsoft!
-Forever and always forward.

 

[JustinEzequiel]

for the VB version:
Package Execution results: Failure

for the C# version:
Package Execution results: Success