How to launch programatically a SSIS package?

Dear all,

I wrote this post others times but the answer did not satisfied so that I'd like to know if really anyone has ever used or experienced with this possibility. No by .Net language rather than Vb 6.0 or ASP or even instanciacing DMO library.

According this link http://support.microsoft.com/?kbid=817248 it seems possible but I haven't idea if possible keeping in mind that it has been made by yourself no for others... So that, I mean, these assemblies comes along with Sql Server 2005 installation.

Thanks a lot for your support,

It is much simpler than the interop path described in that KB article. Just reference the typelibrary in DTS.DLL and use Application & Package classes from there.|||

Hi Michael,

What do you mean? From VB 6.0? Can you explain better?

Thanks a lot

|||

Enric,

I wrote an answer to this question on another thread that you started.

Please see http://forums.microsoft.com/MSDN/showpost.aspx?postid=408720&siteid=1

Best Regards,

PJ

|||

Yes, I know and I beg you pardon for your time dedicated for me in this post.

Such anwser half-satisfied so that I'm looking further explanations.

|||

enric12879 wrote:

Hi Michael,

What do you mean? From VB 6.0? Can you explain better?

Thanks a lot

The KB article describes how to build the typelibrary from managed code, but SSIS already has a native typelibrary and native COM objects that you can use directly from VB 6.

I don't have VB 6.0 around, so I can't send the detailed instructions, but VB can call any COM object. You add the typelibrary to the Project/Reference dialog, and then you can use any COM object from this typelibrary. The typelibrary lives in Program Files\Microsoft SQL Server\9.0\DTS\Binn\Dts.Dll.

|||

I've taken that file but when I try to register by regsvr32 fails.

Error in loadlibrary("c:\windows\system32\dts.dll")

Sql25k is installed on another server and VB in another one. Maybe the problem is there.

|||

enric12879 wrote:

Sql25k is installed on another server and VB in another one. Maybe the problem is there.

Yes, it is definetely a problem. To run the package, you need much more than DTS.DLL - the SSIS needs to be installed on the machine where the package is executed (both for technical and licensing issues).

If you want to run the package on client, install full SSIS on this machine. You don't need to register DTS.DLL - it should already be registered.

If you want to execute the package on server from a program running on another machine, the simplest way to do this is via Agent. Create a SQL Agent Job that will run the package (via UI on server machine, but you can also do it programmatically). Then invoke this job from your client code.

I'm not Agent expert, so I'm not sure what is the best way to communicate with Agent from VB - obviously you can connect to SQL Server using ADO and call Agent stored procedure; but DMO may also work.

|||

Hi Michael,

Thanks for your quick response. We current have got a vb application which is always running and checking up a specified filesystem. If there to find files it fires a DTS on the contraty it follows listening... Sql Agent functionality doesn't serve for our purposes but you are saying that I install SSIS in my workstation but how? Is it possible to install only SSIS features?

I think understand the SSIS data model now. DTS.DLL is then a COM object which can be called from Vb 6.0 and give you access to any SSIS whereas the rest ones are simply assemblies.

repldts.dll

\Program Files\Microsoft SQL Server\80\COM

C0M ?

DTS.dll

\Program Files\Microsoft SQL Server\90\DTS\Binn

C0M ?

Microsoft.SQLServer.ManagedDTS.dll

\Program Files\Microsoft SQL Server\90\SDK\Assemblies

.NET

Microsoft.SQLServer.DTSRuntimeWrap.dll

\Program Files\Microsoft SQL Server\90\SDK\Assemblies

.NET

Microsoft.SqlServer.DTSPipelineWrap.dll

\Program Files\Microsoft SQL Server\90\SDK\Assemblies

.NET

Our idea is to avoid .Net languages for to build a new solution. I know that it's stupid and silly but I can't decide regarding budget and that stuff...

Thanks again for your comments,

|||Where do you want the package to actually run - on the machine where your code runs (client), or on another machine (server)?
If on client, install SSIS - run SQL 2005 setup, check SSIS checkbox, you don't have to install SQL Server and other components. You do need a license for this machine however.
If on server - use the Agent. You don't need to create a schedule, so you are not restricted by Agent's scheduling capabilities. You will just use it as a remote service for running SSIS packages on demand. You will start the packages from the client.
BTW, the choices and recommendations above don't depend on whether you are using native or managed languages.|||

Hi again,

SSIS packages are stored in the server (msdb...)

I don't understand how after to define a job you can do that it is awared whether exists a file or not in a path

I meant, we've got a fixed path:

E:\..\..\..\SERVER\DTS\

And from there a folder for each ASP application or whatever. Each old DTS is fired when VB service find a .nul file or .TXT sended from a JCL from HOST, via FTP, email, etc..

Up to the moment I've done a SSIS which run another SSIS using a script task with .net.

Maybe a SSIS always running and checking for that structure and firing SSIS from himself? It's sound odd.

|||I don't understand where each code runs. It does not matter where the package is stored, like .EXE file it can be stored in one place, but runs where you execute it.

You write that you run child SSIS package from Script Task - this obviously happens on server, where SSIS is already installed. Why don't you run your VB code on the same machine?