Solved

What is the SSIS equivilent of DTS.Package?

Posted on 2007-03-23
15
1,667 Views
Last Modified: 2010-06-30
What is the SSIS equivilent of object DTS.Package?  My goal is to execute (from within a vbscript) an SSIS package.  In the "old" days, the code  
    Set Package = CreateObject("DTS.Package")
     Package.LoadFromSQLServer "(local)\adapt",,,256,,,,"MyPackage"
     Package.Execute()
would do just that.  How do I do this in the new SSIS world?

I have tried running dtexec in wscript.shell.  I tested my command in cmd.exe, it works when executed nativly.  But it will not work in wscript.shell.  Any tips here?

My command is:

     dtexec /sql MyPackage

My package has been stored in SQL Server.
0
Comment
Question by:jflanner
  • 9
  • 3
  • 3
15 Comments
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18780190
Hi jf,

You cany execute the package remotely, you have to do it on the same box as sql server due to licence issues, you can execute the package locally from the command prompt using: "dtexec package", but i dont think thats what your after, if you want to execute if from vb code you can call a stored proceedure from your app, that in turn executes the package. What you need to do is call this stored proceedure from your vb code :


CREATE PROCEDURE ProcedureName
AS
BEGIN
xp_cmdshell dtexec package
END

make sure that 'dtexec' is in you path or else use its full path also make sure you use the full path for your 'package'

some more info here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=534156&SiteID=1
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18780529
This appears to be the new object for SSIS:

set app = createObject("MSDTS.Application")

I found  this here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=312844&SiteId=1
0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18780573
Hi rboyd56,

These objects are only callable from vb.Net managed code within SSIS packages, the SQL Server 2005 licence does not allow them to be called externaly

Steve
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 16

Expert Comment

by:rboyd56
ID: 18780617
The link that I sent dealt with calling SSIS packages from VB Script and that is the object that this user used. He had other problems so may be they were related to what you indicated.
0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18780717
jf,

If you are still sure you want to run the package from code you can use the following vb.Net managed code, you will have to add this reference to your project "Microsoft.SqlServer.ManagedDTS.DLL" default location"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies" :

Dim app As New Application

Dim pkg As New Package

Dim pkgResults As DTSExecResult

pkgLocation = "C:\\Program Files\\Microsoft SQL Server\90\Samples\\Integration Services\\Package Samples\\CalculatedColumns Sample\\CalculatedColumns\\CalculatedColumns.dtsx"

pkg = app.LoadPackage(pkgLocation, Nothing, True)

pkgResults = pkg.Execute()

Rgds

Steve


0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18780732
Be warned about any licencing implications thou !!, with the first method you will not need an sql server licence on the client machine
0
 
LVL 16

Expert Comment

by:rboyd56
ID: 18780956
Thanks for the advice, but I am not doing this. I was just referencing a post I found.
0
 

Author Comment

by:jflanner
ID: 18782594
O.K. folks.  Thanks for the input.  I'm almost there.

I am not concerned with licencing because the script runs on the SQL box.  It is coordinating a number of activities (some database related, some not).

I can create an MSDTS.Application where I used to create a DTS package.  The next line (which I have not altered from DTS days:

Package.LoadFromSQLServer "(local)\adapt",,,256,,,,"MyPackage"

fails due to wring number of parameters.  It seems the template of method LoadFromSQLServer has chenged, which is reasonable.  I went looking for doc on MSDTS.Application and I am having little luck.

Half the points go to the person who can walk me from creation of the Package handle (loaded from SQL server, not a saved .dtsx file) through the execution of that package.  I need thew SSIS equivilant of all three SQL 2000 DTS lines in the original post.


0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18786990
Hi Jf,
This is the prototype for the ssis LoadFromSQLServer method:

 LoadFromSqlServer ( _
      packagePath As String, _
      serverName As String, _
      serverUserName As String, _
      serverPassword As String, _
      events As IDTSEvents _
)

Here is an example of how you can use it:

Dim pkgResults As DTSExecResult
Dim app As New Application
 Dim pkg As Package = New Package()

 pkg = app.LoadFromSqlServer("\\PackageName", "yourserver", Nothing, Nothing, Nothing)

pkgResults = pkg.Execute()

Hope this helps,

Steve
0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18817038
Hi JF,

Just wondering how you got on with this, did it work for you ?

Steve
0
 

Author Comment

by:jflanner
ID: 18819272
Hey Steve:

I got distracted and had to table this for a few days.  I'm getting a bit closer with your help and article   http://msdn2.microsoft.com/en-us/library/ms136090.aspx.  The complicating factor is I am maintaining VBScript, not VB .net.  (This is a large script.  I would hate to have to reengineer the entire thing.)

Any way - where I am at I have:

     Set Application = CreateObject("MSDTS.Application")
     Set Package = Application.LoadFromSqlServer("\\package","(local)\instance", Nothing,  
                                       Nothing,  Nothing)
     Package.Execute()

This dies with error:

Microsoft VBScript runtime error: Type mismatch: 'Application.LoadFromSqlServer'

Any other ideas????
0
 
LVL 2

Accepted Solution

by:
SLaneDell earned 500 total points
ID: 18819812
Hi Jf,

I think you might be in trouble with the VBScript, what is your development envoirnment ? are you using windows script host or asp pages ?, I don think that the SSIS objecs are callable from vbscrit as they are .Net onbects and im pretty sure they are not backward compatile. As an alternative you can use "dtexec package.dtsx" in a batch file, then call the batch file from your script. This will get you out of trouble for the mean time. You could also create a Vb.Net executable (like above) for your new SSIS packages and similarly call from your vb script. This will  allow you to gradually re-engineer your process which i believe you would benifet from doing.

Steve
0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18819888
Hey Jf,

Just got a reference to show you that vbscript is incompatible with SSIS .Net objects :
http://msdn2.microsoft.com/en-us/library/ms403344.aspx, but you can still use either of the methods that i have suggested above,

Steve
0
 
LVL 2

Expert Comment

by:SLaneDell
ID: 18830002
Hi JF,

Does this help, i think we have exausted most possibilities here,

Steve
0
 

Author Comment

by:jflanner
ID: 18839495
Hey Steve:

I agree with you.  Form the Books Online you reference in the last post -

     Note:  
     The Integration Services application programming interfaces are incompatible with COM-based
     scripting languages such as VBScript.

is pretty damming.  I have developed a bat file - and call the bat file from a script as a tacticle measure.  That works - but I will not put it in production.  I will be converting the script to C# before it goes live.

Thanks for the help.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query needed to find duplicate entries and remove entry 16 42
Add different cell to otherwise similiar row 4 37
Webservices in T-SQL 3 30
sql server concatenate fields 10 31
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now