Can I do a Search and Replace in a pool of DTS packages?

Can I do a Search and Replace in a pool of DTS packages?

Post by Dave » Wed, 30 Apr 2003 21:54:59



Hello,

I have about 800 DTS packages (saved in msdb).  Each package pulls
data from an excell file.  In each package's connection object the
path to the file includes a reference to a mapped drive.  For some
reason this occasionally fails.  I modified a few of the packages so
that they used the UNC path to the file instead of the mapped drive.
These modified packages work every time, even when the old packages
fail.

So, I want to change the path reference in all 800 packages to the UNC
name.  Does anyone know how I can do a search and replace agains all
800 packages in msdb?

TIA,

David Simard

 
 
 

Can I do a Search and Replace in a pool of DTS packages?

Post by Allan Mitchel » Wed, 30 Apr 2003 22:21:38


Hello, DaveS!

You can open the packages up programmatically ,change them then resave them.

Dim pkg As DTS.Package

Set pkg = New DTS.Package

pkg.LoadFromSQLServer "MY SERVER", , , DTSSQLStgFlag_UseTrustedConnection, ,
, , "DoesNothing"

'Changes here

pkg.SaveToSQLServer "MY SERVER", , , DTSSQLStgFlag_UseTrustedConnection

Set pkg = Nothing

So long as you know the connection name you want to change you should be
cooking

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

: I have about 800 DTS packages (saved in msdb).  Each package pulls
: data from an excell file.  In each package's connection object the
: path to the file includes a reference to a mapped drive.  For some
: reason this occasionally fails.  I modified a few of the packages so
: that they used the UNC path to the file instead of the mapped drive.
: These modified packages work every time, even when the old packages
: fail.

: So, I want to change the path reference in all 800 packages to the UNC
: name.  Does anyone know how I can do a search and replace agains all
: 800 packages in msdb?

: TIA,

      ---

 
 
 

Can I do a Search and Replace in a pool of DTS packages?

Post by Dave » Thu, 01 May 2003 22:41:20


Thanks Allan, that helps.  I have a quick follow up though:

How would I go about enumerating through all my packages?  I can't
seem to find a package collection or anything like that in the object
model.

thanks again,

David


> Hello, DaveS!

> You can open the packages up programmatically ,change them then resave them.

> Dim pkg As DTS.Package

> Set pkg = New DTS.Package

> pkg.LoadFromSQLServer "MY SERVER", , , DTSSQLStgFlag_UseTrustedConnection, ,
> , , "DoesNothing"

> 'Changes here

> pkg.SaveToSQLServer "MY SERVER", , , DTSSQLStgFlag_UseTrustedConnection

> Set pkg = Nothing

> So long as you know the connection name you want to change you should be
> cooking

> --

> Allan Mitchell (Microsoft SQL Server MVP)
> MCSE,MCDBA
> www.SQLDTS.com
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org

> : I have about 800 DTS packages (saved in msdb).  Each package pulls
> : data from an excell file.  In each package's connection object the
> : path to the file includes a reference to a mapped drive.  For some
> : reason this occasionally fails.  I modified a few of the packages so
> : that they used the UNC path to the file instead of the mapped drive.
> : These modified packages work every time, even when the old packages
> : fail.

> : So, I want to change the path reference in all 800 packages to the UNC
> : name.  Does anyone know how I can do a search and replace agains all
> : 800 packages in msdb?

> : TIA,

>       ---

 
 
 

Can I do a Search and Replace in a pool of DTS packages?

Post by Allan Mitchel » Fri, 02 May 2003 16:25:06


You can enumerate packages by choosing from sysdtspackages

Enumerating DTS Packages using VB.Net
http://www.sqldts.com/default.aspx?6,105,250,0,1

You can then use the object model to open each package.

--

Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


> Thanks Allan, that helps.  I have a quick follow up though:

> How would I go about enumerating through all my packages?  I can't
> seem to find a package collection or anything like that in the object
> model.

> thanks again,

> David




Quote:> > Hello, DaveS!

> > You can open the packages up programmatically ,change them then resave
them.

> > Dim pkg As DTS.Package

> > Set pkg = New DTS.Package

> > pkg.LoadFromSQLServer "MY SERVER", , ,

DTSSQLStgFlag_UseTrustedConnection, ,
Quote:> > , , "DoesNothing"

> > 'Changes here

> > pkg.SaveToSQLServer "MY SERVER", , , DTSSQLStgFlag_UseTrustedConnection

> > Set pkg = Nothing

> > So long as you know the connection name you want to change you should be
> > cooking

> > --

> > Allan Mitchell (Microsoft SQL Server MVP)
> > MCSE,MCDBA
> > www.SQLDTS.com
> > I support PASS - the definitive, global community
> > for SQL Server professionals - http://www.sqlpass.org

> > : I have about 800 DTS packages (saved in msdb).  Each package pulls
> > : data from an excell file.  In each package's connection object the
> > : path to the file includes a reference to a mapped drive.  For some
> > : reason this occasionally fails.  I modified a few of the packages so
> > : that they used the UNC path to the file instead of the mapped drive.
> > : These modified packages work every time, even when the old packages
> > : fail.

> > : So, I want to change the path reference in all 800 packages to the UNC
> > : name.  Does anyone know how I can do a search and replace agains all
> > : 800 packages in msdb?

> > : TIA,

> >       ---

 
 
 

Can I do a Search and Replace in a pool of DTS packages?

Post by Darren Gree » Sun, 04 May 2003 04:19:19




Quote:>Thanks Allan, that helps.  I have a quick follow up though:

>How would I go about enumerating through all my packages?  I can't
>seem to find a package collection or anything like that in the object
>model.

>thanks again,

>David

Have a look at the "Transfer Packages Sample Application" from the link
below. You should be able to change this  easily enough to save to the
same location as you loaded from.

Tools & Tasks
http://www.sqldts.com/default.aspx?220,272,272,1,1

Where it does the "For Each oConn In oPkg.Connections" I am looking for
SQL Server connections where the server is "SourceServer". You could
change this to look for text file connections where it uses the old
mapped drive, and then change if found. Should work a treat, and a lot
faster than a manual process. Make sure you take a copy of
sysdtspackages first in case it all goes pear shaped (DTSBackup 2000 on
the site will also do this quite quickly).

* Warning *
Using this object model to load and save the package means you will
loose any specific layout, it will revert to the default, and also loose
any annotations in your packages. This is an unfortunate limitation of
the object model and there is no way around this.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

 
 
 

1. Switching from inhouse to canned package.

Our company wants to switch from inhouse software (written on a VAX) to an
ERP package called Encore. We are a semiconductor processor and distributor
and have about 100 users. At first we were going to gradually going switch
over from the VAX to VB but now management want to use this canned package
instead. At first I thought there is no way a package will work because out
requirements are so specialized, but now I'm starting to like the idea
because it will force the users to adapt to a simpler system. I think we
have been using the inhouse system for so long that the users are getting
out of hand with their requirements. You know, stuff like "Could you make
it so that when John enters an order on a Thursday it automatically prints
out to Bldg 2 instead of Bldg 3?" And then a year later I invariably get
this question, "How come when John enters an order sometimes it prints to
bldg2 and sometime it prints to bldg3?" The point is that a canned package
will force them to stay within certain guidlines and the overall system
will be easier to manage. The question is will the canned package even come
close to meeting our basic requirements?

Any thoughts?

Chuck.

2. Delphi/Apollo/Clipper Programs - Yea yea

3. FTP command executes before DTS Package is done

4. Determine Sequential Scan

5. DTS package doing double inserts

6. Register problem

7. Timeouts running a DTS package doing sql deletes and inserts

8. How cancel a query from vb?

9. Executing DTS packages inside another DTS package

10. DTS package not executing an inside DTS package

11. Cannot edit DTS package/DTS package opens, but window is blank

12. Error executing a DTS package from another DTS package

13. Run a DTS package from another DTS package