Structured Storage File save and open

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Structured Storage File save and open

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-14 : 03:42:00

Hi guys, (and possible girls)

I want to be able to quickly and easily save all the latest versions of all my DTS files to one structured Storage file. And then I want to be able to take that structured storage file to another server (not same network) and load them all at the click (or just a few clicks) of a button. I dont want the tedious process of saving/opening them one at a time.
Anyone done something like this before?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-14 : 06:50:27

You can save them and load them via
http://www.mindsdoor.net/DTS/SaveAllDTSPackagesToFiles.html

That will do the save.
For the load use
http://www.mindsdoor.net/SQLTsql/s_ProcessAllFilesInDir.html
with
http://www.mindsdoor.net/DTS/s_LoadPackageToServer.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-14 : 12:36:00

nr,
that looks brilliant!
I'll give it a try
Thanks!!!!

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-17 : 12:26:00

I replaced this:
--exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName

With this:
exec @rc = sp_OAMethod @objPackage, 'Connect' , null, '(local)','sa','Mypassword',@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName

But your RaiseError kicks in with:

Server: Msg 50000, Level 16, State 1, Procedure s_SavePackages, Line 57
failed to load package rc = -2147352570, package = AddAdvertSourceTypes & AddCompanyContactTypes

Any idea how to fix this?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-18 : 04:47:33

I only tried that because when I try this:

exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null,
@ServerName = '(local)', @ServerUserName = 'sa', @ServerPassword = 'Mypassword', @Flags = 256, @PackageName = @PackageName

I get "Access Denied". But my username and password are correct!

Any ideas? Anyone?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-18 : 04:57:15

Ok, I'm getting there. I had to provide it a path that does not exsist. Should have read the code comment...

So now it creates the directory but that's it.
I get a NULL result?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-03-21 : 06:03:55

stuff it!

this is what really works! http://www.sqldts.com/default.aspx?272

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources