Modifying an existing DTS package with VB?

Modifying an existing DTS package with VB?

Post by Michael Edward » Tue, 22 Jun 1999 04:00:00



Has anyone been using the DTS object model to automate DTS?  I need to get a
reference to an existing Package to make some modifications to it via Visual
Basic.  I don't see any methods of the Package object which would allow me
to load one or to set a reference to one in my code.  The SQL Magazines have
been having a series on automating DTS with VB but they only discuss setting
up the entire DTS with VB not modifying an existing one.

Any help would be greatly appreciated.  If I figure it out I will be sure to
post it.

Thank you,
Michael Edwards, MCSD

 
 
 

Modifying an existing DTS package with VB?

Post by Euan Garde » Tue, 22 Jun 1999 04:00:00


Create a new package instance as you would if you were going to
create one using code. And then call "LoadFrom......" either
Local, Repos or File.

Works fine for me.

-Euan

Public moPackage As DTS.Package

Set moPackage = New DTS.Package
moPackage.LoadFromStorageFile "c:\temp\testing.dts", " "

Set moPackage = New DTS.Package
moPackage

Quote:> Has anyone been using the DTS object model to automate DTS?  I need to get
a
> reference to an existing Package to make some modifications to it via
Visual
> Basic.  I don't see any methods of the Package object which would allow me
> to load one or to set a reference to one in my code.  The SQL Magazines
have
> been having a series on automating DTS with VB but they only discuss
setting
> up the entire DTS with VB not modifying an existing one.

> Any help would be greatly appreciated.  If I figure it out I will be sure
to
> post it.

> Thank you,
> Michael Edwards, MCSD


 
 
 

Modifying an existing DTS package with VB?

Post by Darren Gree » Tue, 22 Jun 1999 04:00:00




Quote:>Has anyone been using the DTS object model to automate DTS?  I need to get a
>reference to an existing Package to make some modifications to it via Visual
>Basic.  I don't see any methods of the Package object which would allow me
>to load one or to set a reference to one in my code.  The SQL Magazines have
>been having a series on automating DTS with VB but they only discuss setting
>up the entire DTS with VB not modifying an existing one.

>Any help would be greatly appreciated.  If I figure it out I will be sure to
>post it.

>Thank you,
>Michael Edwards, MCSD

You need to load the package, and then edit properties accordingly
There are some samples in the MSSQL7\DevTools\Samples\DTS\ or on the CD.

Some sample code
Dim oPKG As DTS.Package
Set oPKG = New DTS.Package

oPKG.LoadFromSQLServer "SERVER", "USERNAME", "PASSWORD", _
DTSSQLStgFlag_Default, , , , "Packagename"

OR

oPKG.LoadFromSQLServer "SERVER", , , _
DTSSQLStgFlag_UseTrustedConnection, , , , "Packagename"

Debug.Print oPKG.GlobalVariables.Count

This loads a package from SQLServer and prints the number of Global
variables it has. Not very exciting I grant you, but it demonstrates the
use of the package object.

Repository packages are a bit more dificult because you can't use the
name, you must use the GUID instead. See LoadFromRepository method in
BOL.

If anyone knows how the get a Package GUID from a package name them
please let me know.

All of the objects etc are documented in BOL, but I assume you already
know that.

(You have got the reference to the DTSPackage Object Library in your
project)
--
Darren Green